?
Solved

Optimize pl/sql procedure with loop

Posted on 2007-10-05
18
Medium Priority
?
1,299 Views
Last Modified: 2013-12-07
Guys here is what simple pl/sql Proc which uses two for loops...

It seems to be taking abt 12 hrs just to finish it completely.....

Sql looks very simple but still dont know why its taking long time!!

CREATE OR REPLACE PROCEDURE MIN.test_Cla_Sta_Gen IS
     l_count NUMBER := 0;
     BEGIN
        l_count := 0;
        FOR c IN (SELECT * FROM S.DAILY_CLASS_JOINS where rownum < 2)
        LOOP
           FOR d IN (
                       SELECT watcher.usernum AS recipient_usernum,
                               watcher.EMAIL AS emailAddress,
                               watcher.firstname AS firstName,
                               watcher.lastname AS lastName,
                               watcher_aw.school_id AS school_id,
                               watcher_aw.primary_school AS primarySchool,
                               nh.NAME AS school_name,
                               hev.validation_status AS recipient_validation_status,
                               watcher_aw.startyear AS recipient_startyear,
                               watcher_aw.endyear AS recipient_endyear,
                               c.startyear AS new_cm_startyear,
                               c.endyear AS new_cm_endyear,
                               joiner.usernum AS classmate_usernum,
                               joiner.firstname AS classmateFirstName,
                               NVL(joiner.maidenname,joiner.lastname) AS classmateLastName,
                               hev.new_classmates_alert as NEW_CLASSMATES_ALERT
                        FROM min.A joiner,
                              min.A watcher,
                              min.AALUMNIWATCH watcher_aw,
                              min.A_EMAIL_PROFILE hev,
                              min.HIGHSCHOOLS nh
                         WHERE watcher.deleted=0
                         AND   joiner.deleted=0
                         --
                         --AND   hev.validation_status IN (1,2,3)
                         --AND   hev.new_classmates_alert IS NULL OR hev.new_classmates_alert=1
                         --
                         AND   nh.pin=c.school_id
                         AND   hev.usernum=watcher.usernum
                         AND   joiner.gradyear BETWEEN watcher_aw.startyear AND watcher_aw.endyear
                         AND   watcher.usernum=watcher_aw.usernum
                         AND   c.school_id=watcher_aw.school_id
                         AND   joiner.usernum=c.usernum)
            LOOP
               l_count := l_count + 1;
             INSERT INTO test.new_clas (
                       RECIPIENT_USERNUM,
                         EMAILADDRESS,
                         FIRSTNAME,
                         LASTNAME,
                         SCHOOL_ID,
                         PRIMARYSCHOOL,
                         SCHOOL_NAME,
                         RECIPIENT_VALIDATION_STATUS,
                         RECIPIENT_STARTYEAR,
                         RECIPIENT_ENDYEAR,
                         NEW_CM_STARTYEAR,
                         NEW_CM_ENDYEAR,
                         CLASSMATE_USERNUM,
                         CLASSMATEFIRSTNAME,
                         CLASSMATELASTNAME,
                         NEW_CLASSMATES_ALERT)
               VALUES
                  (              d.RECIPIENT_USERNUM,
                               d.EMAILADDRESS,
                               d.FIRSTNAME,
                               d.LASTNAME,
                               d.SCHOOL_ID,
                               d.PRIMARYSCHOOL,
                               d.SCHOOL_NAME,
                               d.RECIPIENT_VALIDATION_STATUS,
                               d.RECIPIENT_STARTYEAR,
                               d.RECIPIENT_ENDYEAR,
                               d.NEW_CM_STARTYEAR,
                               d.NEW_CM_ENDYEAR,
                               d.CLASSMATE_USERNUM,
                               d.CLASSMATEFIRSTNAME,
                               d.CLASSMATELASTNAME,
                               d.NEW_CLASSMATES_ALERT
                         );
              IF (l_count = 10) THEN
                  COMMIT;
                  l_count := 0;
               END IF;
            END LOOP;
         END LOOP;
      END;
/
0
Comment
Question by:anandmahajan
  • 7
  • 6
  • 2
  • +3
18 Comments
 
LVL 9

Assisted Solution

by:konektor
konektor earned 800 total points
ID: 20026901
i noticed that you commit afrer 10 rows inserted ... how many rows does your select return? even if there are hundreds of thousands of refords, i thing you would better perform insert-select all at once.
0
 
LVL 9

Expert Comment

by:konektor
ID: 20026902
i haven't put down, that doing insert-select is faster due to insert-select query is pure SQL, doing cursor-loop-insert is mixing SQL and PL/SQL - operation "context switch" occurs while performing it
0
 
LVL 12

Expert Comment

by:jwahl
ID: 20026927
what type are the columns joiner.gradyear, watcher_aw.startyear and watcher_aw.endyear?
do you have indexes/primary keys on the join columns?

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 17

Expert Comment

by:ram_0218
ID: 20028038
12 hours? something should be terribly  wrong. enable profiler on this process and find out where it gets stuck. mostly it should be a missing index or a missing join condition.

and also i think the outer loop is not at all required. because looks like you always select only one record, why cant you just put it as select * into
0
 

Expert Comment

by:GuyFromCalgary
ID: 20028121
All of the above plus.

Do you have the HighSchools table in the where clause join to another table? I don't see it. So you are returning every row from the HighSchool tables with every row from the rest of the query.
0
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 1200 total points
ID: 20028131
As konektor suggested, you could very well do away with that extra FOR LOOP unless u need those 10-row COMMITs very badly. You could replace them with COMMITs within the outer FOR LOOP.

CREATE OR REPLACE PROCEDURE min.test_cla_sta_gen IS
BEGIN
FOR c IN (SELECT * FROM s.daily_class_joins where ROWNUM < 2) LOOP
          INSERT INTO test.new_clas
        (recipient_usernum,
         emailaddress,
         firstname,
         lastname,
         school_id,
         primaryschool,
         school_name,
         recipient_validation_status,
         recipient_startyear,
         recipient_endyear,
         new_cm_startyear,
         new_cm_endyear,
         classmate_usernum,
         classmatefirstname,
         classmatelastname,
         new_classmates_alert)
          SELECT
         watcher.usernum,
                         watcher.email,
         watcher.firstname,
         watcher.lastname,
         watcher_aw.school_id,
         watcher_aw.primary_school,
         nh.name,
         hev.validation_status,
         watcher_aw.startyear,
         watcher_aw.endyear,
         c.startyear,
         c.endyear,
         joiner.usernum,
         joiner.firstname,
         NVL(joiner.maidenname,joiner.lastname),
         hev.new_classmates_alert
          FROM
           min.a joiner,
           min.a watcher,
           min.aalumniwatch watcher_aw,
           min.a_email_profile hev,
           min.highschools nh
          WHERE watcher.deleted = 0
                          AND joiner.deleted = 0
          AND nh.pin = c.school_id
          AND hev.usernum = watcher.usernum
          AND joiner.gradyear BETWEEN watcher_aw.startyear AND watcher_aw.endyear
          AND watcher.usernum = watcher_aw.usernum
          AND c.school_id = watcher_aw.school_id
          AND joiner.usernum = c.usernum;

        COMMIT;
END LOOP;

END test_cla_sta_gen;
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20028136
>> Do you have the HighSchools table in the where clause join to another table? I don't see it.
I see it - AND   nh.pin=c.school_id
0
 
LVL 1

Author Comment

by:anandmahajan
ID: 20035328
let me try solution suggested by konektor: and jinesh and will get back to you guys.
0
 
LVL 1

Author Comment

by:anandmahajan
ID: 20036748
I have modified Pl/sql to use only one loop and use  Insert select as suggested.and using commit for each for loop

here is wht explain plan looks like :

Plan
INSERT STATEMENT  FIRST_ROWSCost: 1,756                                      
      13 NESTED LOOPS  Cost: 1,756  Bytes: 715  Cardinality: 5                                
            10 NESTED LOOPS  Cost: 1,746  Bytes: 655  Cardinality: 5                          
                  7 NESTED LOOPS  Cost: 1,736  Bytes: 420  Cardinality: 5                    
                        4 NESTED LOOPS  Cost: 4  Bytes: 60  Cardinality: 1              
                              1 INDEX RANGE SCAN INDEX HSADMIN.NEW_HIGHSCOOLS_COVER_IDX Cost: 2  Bytes: 29  Cardinality: 1        
                              3 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE HSADMIN.HSA Cost: 2  Bytes: 31  Cardinality: 1  Partition #: 6  Partitions accessed #ROW L      
                                    2 INDEX UNIQUE SCAN INDEX (UNIQUE) HSADMIN.HSA_USERNUM_PK_IDX Cost: 1  Cardinality: 1  
                        6 TABLE ACCESS BY INDEX ROWID TABLE HSADMIN.HSAALUMNIWATCH Cost: 1,732  Bytes: 120  Cardinality: 5              
                              5 INDEX RANGE SCAN INDEX HSADMIN.HSAALUMNIWATCH_US_UIDX2 Cost: 4  Cardinality: 2,033        
                  9 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE HSADMIN.HSA Cost: 2  Bytes: 47  Cardinality: 1  Partition #: 10  Partitions accessed #ROW L                  
                        8 INDEX UNIQUE SCAN INDEX (UNIQUE) HSADMIN.HSA_USERNUM_PK_IDX Cost: 1  Cardinality: 1              
            12 TABLE ACCESS BY INDEX ROWID TABLE HSADMIN.HSA_EMAIL_PROFILE Cost: 2  Bytes: 12  Cardinality: 1                          
                  11 INDEX UNIQUE SCAN INDEX (UNIQUE) HSADMIN.HSA_EMAIL_PROFILE_UNUM_IDX Cost: 1  Cardinality: 1  
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20041938
Has the performance improved now?
0
 
LVL 1

Author Comment

by:anandmahajan
ID: 20058939
Yes performance has improved abt 120% now Query is finishing in about 5 hrs... what other way to optimize this Query....

It uses tables which are having lots of indexes.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20063807
Remove the COMMIT inside the loop and put it just before the EXCEPTION / END. If thats still expensive, do away with the whole loop and execute the INSERT directly and see the performance.
0
 
LVL 1

Author Comment

by:anandmahajan
ID: 20068591
This Query is still very expensive and taking lots of time as it is using table  a   twice which is largest table in our database with 22 index keys and 22 triggers. It has about 55 mill  rows with abt 27 partitions.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20076655
Try including this join in ur query and see if it makes any diff. I think it should.

AND joiner.usernum = watcher.usernum
0
 
LVL 1

Author Comment

by:anandmahajan
ID: 20081830
no we cannot do that as thta will change the whole purpose of Query.... looks at select columns in statement
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20084331
Oops ... sorry i missed that ... U said there are 22 triggers on that table. Is there no way u can cut down that no. by clubbing them together? I think those many triggers would most certainly pose a big penalty on the performance.
0
 
LVL 1

Author Comment

by:anandmahajan
ID: 20085291
yeah ur right i am working on it....in the meantime i could somehow reduce joiner,watcher tables to just one which was getting used twice and that reduced lots of overhead....
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20085501
Thats great :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 how to recover a database from a user managed backup
Suggested Courses

839 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