asked on

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;
/

SOLUTION

konektor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

konektor

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

jwahl

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

ram_0218

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

GuyFromCalgary

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.

ASKER CERTIFIED SOLUTION

Jinesh Kamdar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jinesh Kamdar

>> 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

anandmahajan

ASKER

let me try solution suggested by konektor: and jinesh and will get back to you guys.

anandmahajan

ASKER

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

Jinesh Kamdar

Has the performance improved now?

anandmahajan

ASKER

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.

Jinesh Kamdar

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.

anandmahajan

ASKER

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.

Jinesh Kamdar

Try including this join in ur query and see if it makes any diff. I think it should.

AND joiner.usernum = watcher.usernum

anandmahajan

ASKER

no we cannot do that as thta will change the whole purpose of Query.... looks at select columns in statement

Jinesh Kamdar

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.

anandmahajan

ASKER

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....

Jinesh Kamdar

Thats great :)