Oracle Pipeline (table function)

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.
toookiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
Data partioning or a bulk collect, perhaps?
0
toookiAuthor Commented:
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
HainKurtSr. System AnalystCommented:
what happens if you remove cursor and this line

AND MyTab2.x1 BETWEEN rec.dt_start AND rec.dt_end
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paquicubaCommented:
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
toookiAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.