Solved

Oracle Pipeline (table function)

Posted on 2012-04-11
6
639 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 53

Expert Comment

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

AND MyTab2.x1 BETWEEN rec.dt_start AND rec.dt_end
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

734 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