Solved

Oracle Pipeline (table function)

Posted on 2012-04-11
6
629 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 77

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

828 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