Solved

Oracle Pipeline (table function)

Posted on 2012-04-11
6
621 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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

786 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