How to improve Execution time

[latha9]

Hi,

I have created a procedure which will retrieve the data and insert into another table.but this is taking more than 30min.Can u please help how to reduce the execution time.all the columns i have used in where clause have indexes expect org_staff_id.

CREATE OR REPLACE PROCEDURE Dw_MH_Assess_Enrollment IS


CURSOR mh_data IS
SELECT
DEPT_ASSESSMENT_QUES.ASSMT_ID,
DEPT_ASSESSMENT.INS_ID,
DEPT_ASSESSMENT.PEO_ID,
INSTRUMENT.INS_NAME,
INSTRUMENT.INS_PURPOSE_DESCR,
INSTRUMENT.INS_VERSION_NO,
INSTRUMENT.DATE_INS_INACTIVE,
DEPT_ASSESSMENT.DATE_START,
DEPT_ASSESSMENT.DATE_END,
DEPT_ASSESSMENT.ASSMT_DESCR,
RG_NAME,
ol.LOCATION_NAME,
NVL(pa.FIRST_NAME ||' '||pa.LAST_NAME, 'N/A')
ORG_STAFF,
INS_QUES_GRID.INS_QUES_GRID_NAME,
INS_DIMENSION.INS_DIMENSION_ID,
INS_DIMENSION.INS_DIMENSION_NAME,
INS_QUESTION.INS_QUES_GRID_ROW_ID,
INS_QUESTION_GRID_ROW.INS_QUES_GRID_ROW_LABEL,
INS_ANSWER.INS_QUES_ID,
INS_QUESTION.INS_QUES_TXT,
INS_ANSWER.INS_ANS_ID,
NVL(INS_ANSWER.INS_ANS_VALUE_CODE, 'N/A')
INS_ANS_VALUE_CODE,
NVL(INS_ANSWER.INS_ANS_DISPLAY_LABEL,
NVL(DEPT_ASSESSMENT_QUES.DEPT_ASSMT_ANSWER_TEXT_VALUE,

DEPT_ASSESSMENT_QUES.DEPT_ASSMT_NUMBER_VALUE)) ANSWER,
INS_DIMENSION.INS_DIMENSION_ORDER,
INS_QUES_GRID.INS_QUES_GRID_ORDER,
INS_QUESTION_GRID_ROW.INS_QUES_GRID_ROW_ORDER,
INS_QUESTION.INS_QUES_ORDER
FROM
PEOPLE_ALL pa,
ORGANIZATION o,
ORG_LOCATION ol,
INSTRUMENT,
INS_ANSWER,
DEPT_ASSESSMENT_QUES,
INS_QUESTION,
INS_QUESTION_GRID_ROW,
INS_QUES_GRID,
DEPT_ASSESSMENT,
INS_DIMENSION
WHERE
DEPT_ASSESSMENT.ORG_STAFF_ID(+)=PA.PEO_ID AND
DEPT_ASSESSMENT.ORG_ID=o.org_id AND
DEPT_ASSESSMENT.ORG_LOCATION_ID = ol.ORG_LOCATION_ID
AND
INSTRUMENT.INS_ID=DEPT_ASSESSMENT.INS_ID AND
DEPT_ASSESSMENT.ASSMT_ID =
DEPT_ASSESSMENT_QUES.ASSMT_ID AND
DEPT_ASSESSMENT_QUES.INS_ANS_ID =
INS_ANSWER.INS_ANS_ID AND
DEPT_ASSESSMENT_QUES.INS_QUES_ID =
INS_QUESTION.INS_QUES_ID (+) AND
INS_QUESTION.INS_QUES_GRID_ROW_ID =
INS_QUESTION_GRID_ROW.INS_QUES_GRID_ROW_ID(+) AND
INS_QUES_GRID.INS_QUES_GRID_ID(+) =
INS_QUESTION_GRID_ROW.INS_QUES_GRID_ID AND
INS_DIMENSION.INS_DIMENSION_ID(+) =
INS_QUESTION.INS_DIMENSION_ID AND
DEPT_ASSESSMENT.INS_ID IN (1589,1969) AND
DEPT_ASSESSMENT.PEO_ID IN
(SELECT PEO_ID FROM DW_ASSOCIATION_SPPRIMARY WHERE
ASSOC_NAME = 'ADULT MH')
ORDER BY
DEPT_ASSESSMENT.INS_ID,
DEPT_ASSESSMENT_QUES.ASSMT_ID ASC,
INS_DIMENSION.INS_DIMENSION_ORDER ASC,
INS_QUES_GRID.INS_QUES_GRID_ORDER ASC,
INS_QUESTION_GRID_ROW.INS_QUES_GRID_ROW_ORDER,
INS_QUESTION_GRID_ROW.INS_QUES_GRID_ROW_LABEL ASC,
INS_QUESTION.INS_QUES_ORDER ASC;

BEGIN
DELETE DW_MH_ASSESSM_ANS_ENROLLMENT;
FOR i IN mh_data LOOP
--dbms_output.put_line ('getting ready for loop') ;
INSERT INTO DW_MH_ASSESSM_ANS_ENROLLMENT
( ASSMT_ID,
INS_ID,
PEO_ID,
INS_NAME,
INS_PURPOSE_DESCR,
INS_VERSION_NO,
DATE_INS_INACTIVE,
DATE_START,
DATE_END,
ASSMT_DESCR,
ORG_NAME,
LOCATION_NAME,
ORG_STAFF,
INS_QUES_GRID_NAME,
INS_DIMENSION_ID,
INS_DIMENSION_NAME,
INS_QUES_GRID_ROW_ID,
INS_QUES_GRID_ROW_LABEL,
INS_QUES_ID,
INS_QUES_TXT,
INS_ANS_ID,
INS_ANS_VALUE_CODE,
ANSWER,
INS_DIMENSION_ORDER,
INS_QUES_GRID_ORDER,
INS_QUES_GRID_ROW_ORDER,
INS_QUES_ORDER )
VALUES
( i.ASSMT_ID,
i.INS_ID,
i.PEO_ID,
i.INS_NAME,
i.INS_PURPOSE_DESCR,
i.INS_VERSION_NO,
i.DATE_INS_INACTIVE,
i.DATE_START,
i.DATE_END,
i.ASSMT_DESCR,
i.ORG_NAME,
i.LOCATION_NAME,
i.ORG_STAFF,
i.INS_QUES_GRID_NAME,
i.INS_DIMENSION_ID,
i.INS_DIMENSION_NAME,
i.INS_QUES_GRID_ROW_ID,
i.INS_QUES_GRID_ROW_LABEL,
i.INS_QUES_ID,
i.INS_QUES_TXT,
i.INS_ANS_ID,
i.INS_ANS_VALUE_CODE,
i.ANSWER,
i.INS_DIMENSION_ORDER,
i.INS_QUES_GRID_ORDER,
i.INS_QUES_GRID_ROW_ORDER,
i.INS_QUES_ORDER );
COMMIT;
-- dbms_output.put_line ('inserted record for PEO_ID '|| .peo_id);
END LOOP;
COMMIT ;
END ;
Thanks,
Latha

 

[BJCooperIT]

Hard to do without knowing more about indexes and how large your tables are.

These might help:
DW_ASSOCIATION_SPPRIMARY - index on ASSOC_NAME
DEPT_ASSESSMENT - index on ORG_ID

Consider dropping any indexes on DW_MH_ASSESSM_ANS_ENROLLMENT before running your script and adding them back afterwards.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[taupirho]

Why are you using explicit cursors for this?

Just do a simple insert into ... select ...

Think you'll find execution time improves significantly

 

[BJCooperIT]

Perhaps the cursor is because of the dbms_output which is commented out. Not committing each row might help. Perhaps every 100 or 1000?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus