• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1305
  • Last Modified:

Optimize pl/sql procedure with loop

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
anandmahajan
Asked:
anandmahajan
  • 7
  • 6
  • 2
  • +3
2 Solutions
 
konektorCommented:
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
 
konektorCommented:
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
 
jwahlCommented:
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
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.

 
ram_0218Commented:
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
 
GuyFromCalgaryCommented:
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
 
Jinesh KamdarCommented:
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
 
Jinesh KamdarCommented:
>> 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
 
anandmahajanAuthor Commented:
let me try solution suggested by konektor: and jinesh and will get back to you guys.
0
 
anandmahajanAuthor Commented:
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
 
Jinesh KamdarCommented:
Has the performance improved now?
0
 
anandmahajanAuthor Commented:
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
 
Jinesh KamdarCommented:
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
 
anandmahajanAuthor Commented:
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
 
Jinesh KamdarCommented:
Try including this join in ur query and see if it makes any diff. I think it should.

AND joiner.usernum = watcher.usernum
0
 
anandmahajanAuthor Commented:
no we cannot do that as thta will change the whole purpose of Query.... looks at select columns in statement
0
 
Jinesh KamdarCommented:
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
 
anandmahajanAuthor Commented:
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
 
Jinesh KamdarCommented:
Thats great :)
0
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now