Solved

Oracle Pipeline (table function)

Posted on 2012-04-11
6
617 Views
Last Modified: 2012-06-22
I have a procedure in Oracle (11gR2) that takes several hour to run. If possible, could you let me know how I could improve the performance of the query?

The query of the procedure is:
Procedure MyProc IS
BEGIN

CURSOR cur1
        IS
WITH t AS  (SELECT to_date('01-DEC-2010') AS dt_init, sysdate AS dt_final FROM dual)
            SELECT TO_CHAR(add_months(TRUNC(dt_init, 'mm'), level - 1), 'RRRRMM') AS y_mon,
              to_char(add_months(TRUNC(dt_init, 'mm'), level - 1), 'J') AS dt_start,
              to_char(add_months(TRUNC(dt_init, 'mm'), level) - 1, 'J') AS dt_end
            FROM t
              CONNECT BY level <= months_between(dt_final, dt_init) + 1;

     FOR rec IN cur1

      LOOP
INSERT  /*+ PARALLEL */
  INTO MyTab1
    (f1, f2, f3, f4)
SELECT MyTab2.x1, SUM(MyTab2.x2), SUM(MyTab2.x3), SUM(MyTab2.x4) FROM MyTab2
INNER JOIN MyTab3 ON MyTab2.x1 = MyTab3.y1
AND MyTab2.x1 BETWEEN rec.dt_start AND rec.dt_end
GROUP BY MyTab2.x1;
END LOOP;

CLOSE cur1;    
COMMIT;
END MyProc;

The cursor1 has fixed 17 records. And the loop is run thus 17 times. Table MyTab2 has 8million records. MyTab3 has about 115 records.
In the end the table MyTab1 contains about 27000 records.
MyTab3.y1 has the bitmap index. MyTab2.x1 has the unique index.

The above query takes 3 hours to run and is not usable. Is there any way I could make it fasted by Oracle pipelines or table functions? Thanks a lot.
0
Comment
Question by:toooki
6 Comments
 
LVL 23

Expert Comment

by:David
ID: 37835403
Data partioning or a bulk collect, perhaps?
0
 

Author Comment

by:toooki
ID: 37835586
Thank you.
I tried bulk collect before. Actually that was taking longer (6 hours).  This is what I tried before for the bulk collect.


CURSOR cur1
        IS
Procedure MyProc IS
WITH t AS  (SELECT to_date('01-DEC-2010') AS dt_init, sysdate AS dt_final FROM dual)
            SELECT TO_CHAR(add_months(TRUNC(dt_init, 'mm'), level - 1), 'RRRRMM') AS y_mon,
              to_char(add_months(TRUNC(dt_init, 'mm'), level - 1), 'J') AS dt_start,
              to_char(add_months(TRUNC(dt_init, 'mm'), level) - 1, 'J') AS dt_end
            FROM t
              CONNECT BY level <= months_between(dt_final, dt_init) + 1;

CURSOR cur2 (dt_start number, dt_end number)
IS
SELECT MyTab2.x1, SUM(MyTab2.x2), SUM(MyTab2.x3), SUM(MyTab2.x4) FROM MyTab2
INNER JOIN MyTab3 ON MyTab2.x1 = MyTab3.y1
AND MyTab2.x1 BETWEEN rec.dt_start AND rec.dt_end
GROUP BY MyTab2.x1;

TYPE cur1_array IS TABLE OF cur2%ROWTYPE;
v_cur1_array     cur1_array;

BEGIN

FOR rec IN cur1
<<loop1>>
      LOOP
OPEN cur2 (rec1.dt_start, rec1.dt_start);

<<loop2>>
LOOP
FETCH cur2 BULK COLLECT INTO v_cur1_array LIMIT 5000;
             
FORALL i IN 1 .. v_cur1_array.COUNT
 INSERT  /*+ PARALLEL */
 INTO MyTab1
 VALUES   v_cur1_array(i);
COMMIT;
EXIT loop2 WHEN cur1%NOTFOUND;
 END LOOP loop2;
CLOSE cur2;

COMMIT;
END LOOP loop1;

END;


Not sure if the above is the absolute correct way of doing when choosing bulk collect option. It worked but takes more than what it took without bulk collect in the first post ...
Thank you.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37835652
what happens if you remove cursor and this line

AND MyTab2.x1 BETWEEN rec.dt_start AND rec.dt_end
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37837253
How many columns does MyTab2 have?
How much DML is performed on MyTab2?

I'm thinking an index on MyTab2(x1,x2,x3,x4) might help.


What is the excution plan of:
SELECT MyTab2.x1, SUM(MyTab2.x2), SUM(MyTab2.x3), SUM(MyTab2.x4) FROM MyTab2
INNER JOIN MyTab3 ON MyTab2.x1 = MyTab3.y1
AND MyTab2.x1 BETWEEN sysdate AND sysdate-17
GROUP BY MyTab2.x1;
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 37856542
If the SELECT from Tab2 & Tab3 runs fast, then you may have some triggers on the background doing some pretty nasty updates, 'cause I don't see why a simple INSERT statement is taking that long.
How many indexes do you have in Tab1?
0
 

Author Comment

by:toooki
ID: 37927617
Thank you for the help. I had to give up on the procedure performance for some time. I will test with the index and get with you. Thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now