Re: Tuning of a stored oracle procedure

Only two suggestions come to mind:

(1) Hire a consultant.
(2) Post a more specific question.

I am not trying to be a smart a%% or anything, its just that I know from experience that the chances are pretty slim that anyone would take the time to analyze this much code and reply to this post. Even if someone tried to look at it a little, unless there was something blatantly wrong with the code it would require a lot more information about the application, desired functionality, data model, table row counts, etc. to be able to tune this procedure.

a_andy_at_my-dejanews.com wrote:

> Hi! Everybody,
>
> This is a procedure which i want to tune. Presently it takes 10 minutes for
> execution. The required indexes are in place.
>
> Any suggestions ???
>
> Thanks in advance.
>
> PROCEDURE OpenCursor IS
> customer_acct TEMP_ACCOUNT.ACCT_ICN%TYPE;
> splinter_acct TEMP_ACCOUNT.ACCT_ICN%TYPE;
> customer_rec AUTHORIZE%ROWTYPE;
>
> CURSOR AUTHORIZE_TEMP_CURSOR (xaact_icn AUTHORIZE.ACCT_ICN%TYPE) IS
> SELECT * FROM AUTHORIZE
> WHERE ACCT_ICN = xaact_icn;
>
> CURSOR AUTHORIZE_OLD (xaact_icn AUTHORIZE.ACCT_ICN%TYPE) IS
> SELECT * FROM AUTHORIZE
> WHERE ACCT_ICN = xaact_icn
> AND AUTH_CODE IN (1,2);
>
> CURSOR TEMP_PROD_CURSOR IS
> SELECT DISTINCT PARENT_ICN FROM ENUMPH
> WHERE HIER_ICN = 3 AND
> PARENT_LVL IN (3,4) AND
> CHILD_LVL = 5 AND
> CHILD_ICN IN (SELECT DISTINCT PROD_ICN FROM TEMP_AUTHORIZE
> WHERE AUTH_CODE IN (1,2));
>
> CURSOR TEMP_ACCOUNT_ENUMAH (xaact_icn AUTHORIZE.ACCT_ICN%TYPE) IS SELECT
> ACCT_ICN, ADD_UPDATE_FLAG, CHILD_LVL FROM ENUMAH E, TEMP_ACCOUNT A WHERE
> A.PROCESS_ID = CommonGlobalPackage.Process_ID AND A.UPPER_LEVEL =
> CommonGlobalPackage.FALSE_FLAG AND A.ACCT_ICN = E.CHILD_ICN AND
> E.PARENT_ICN = xaact_icn AND E.HIER_ICN = 2;
>
> CURSOR TEMP_ACCOUNT_CURSOR IS
> SELECT * FROM TEMP_ACCOUNT
> WHERE PROCESS_ID = CommonGlobalPackage.Process_ID AND
> UPPER_LEVEL = CommonGlobalPackage.FALSE_FLAG;
> /* do not include nohist*/
> CURSOR CUSTOMER_ACCT_C IS
> SELECT ACCT_ICN FROM ACCTHIER A,TEMP_ACCOUNT B
> WHERE B.ACCT_ICN = A.CHILD_ICN AND
> A.HIER_ICN = 2 AND
> A.CHILD_LVL IN (7, 8) AND
> B.PROCESS_ID = CommonGlobalPackage.Process_ID AND
>
> B.UPPER_LEVEL in (CommonGlobalPackage.TRUE_FLAG,
> CommonGlobalPackage.FALSE_FLAG)
> ORDER BY A.CHILD_LVL DESC;
> xspace_guide_icn TEMP_AUTHORIZE.SPACE_GUIDE_ICN%TYPE;
> BEGIN
> insert into adebug values('in here before delete'||to_char
> (sysdate,'mm/dd/yyyy hh24:mi:ss'));
> commit;
> DELETE FROM TEMP_AUTHORIZE
> WHERE PROCESS_ID = CommonGlobalPackage.Process_ID;
> COMMIT;
> SELECT COUNT(*) INTO rec_count FROM TEMP_ACCOUNT
> WHERE ADD_UPDATE_FLAG > 0 AND
> PROCESS_ID = CommonGlobalPackage.Process_ID AND
> UPPER_LEVEL = CommonGlobalPackage.TRUE_FLAG;
> insert into adebug values('in here before for'||to_char
> (sysdate,'mm/dd/yyyy hh24:mi:ss'));
> commit;
>
> /* update from authorize*/
> FOR CA IN CUSTOMER_ACCT_C LOOP /*start customer loop*/
> customer_acct := CA.ACCT_ICN;
> FOR TAA IN TEMP_ACCOUNT_ENUMAH (customer_acct)
> LOOP /* loop2*/
> FOR AC IN AUTHORIZE_TEMP_CURSOR (customer_acct)
> LOOP /* loop3*/
> rec_count := 0;
> IF TAA.CHILD_LVL = 8 THEN
> SELECT COUNT(*) INTO rec_count FROM AUTHORIZE
> WHERE ACCT_ICN = TAA.ACCT_ICN AND
> PROD_ICN = AC.PROD_ICN;
> END IF;
> IF rec_count = 0 THEN
> BEGIN
> INSERT INTO TEMP_AUTHORIZE (
> PROCESS_ID,
>
> ACCT_ICN,
>
> PROD_ICN,
>
> BEGIN_DATE,
>
> END_DATE,
>
> CUST_ORDER_NBR,
>
> PROD_STATUS,
>
> AUTHORIZED,
>
> CBT_SHARE_OBJ,
>
> PLANOGRAM,
>
> DIST_RATING_EXC,
>
> DIST_RATING_GOOD,
>
> UNTIL_DATE,
>
> AUTH_CODE,
>
> STD_ITEM_CODE,
>
> CASE_PROD_KEY,
>
> PACKAGE_CSE_QTY,
>
> SPACE_GUIDE_ICN,
> NEILSON_RANK_ICN,
> PROFIT_MARGIN,
> BRACKET_1,
>
> DELETED_FLAG,
>
> ECHO_FLAG,
>
> UPDATE_DATE_TIME,
>
> UPDATE_EMP_ICN )
>
> VALUES (
> CommonGlobalPackage.Process_ID,
> TAA.ACCT_ICN,
> AC.PROD_ICN,
>
> AC.BEGIN_DATE,
>
> AC.END_DATE,
>
> AC.CUST_ORDER_NBR,
>
> AC.PROD_STATUS,
>
> AC.AUTHORIZED,
>
> AC.CBT_SHARE_OBJ,
>
> AC.PLANOGRAM,
>
> AC.DIST_RATING_EXC,
>
> AC.DIST_RATING_GOOD,
>
> AC.UNTIL_DATE,
>
> AC.AUTH_CODE,
>
> AC.STD_ITEM_CODE,
>
> AC.CASE_PROD_KEY,
>
> AC.PACKAGE_CSE_QTY,
>
> AC.SPACE_GUIDE_ICN,
> AC.NEILSON_RANK_ICN,
> AC.PROFIT_MARGIN,
> AC.BRACKET_1,
> AC.DELETED_FLAG,
> AC.ECHO_FLAG,
> AC.UPDATE_DATE_TIME,
> AC.UPDATE_EMP_ICN);
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> /* this is dummy statement to handle exception*/
> rec_count := 0;
> END;
> END IF;
> END LOOP;/*end loop 3*/
> /* there are newly added accounts
> for which old auth records are needed*/
> IF TAA.ADD_UPDATE_FLAG > 0 THEN
> FOR AO IN AUTHORIZE_OLD (customer_acct)
> LOOP /* loop4*/
> rec_count := 0;
> IF TAA.CHILD_LVL = 8 THEN
> SELECT COUNT(*) INTO rec_count FROM AUTHORIZE
> WHERE ACCT_ICN = TAA.ACCT_ICN AND
> PROD_ICN = AO.PROD_ICN;
> END IF;
> IF rec_count = 0 THEN
> BEGIN
> INSERT INTO TEMP_AUTHORIZE (
> PROCESS_ID,
>
> ACCT_ICN,
>
> PROD_ICN,
>
> BEGIN_DATE,
>
> END_DATE,
>
> CUST_ORDER_NBR,
>
> PROD_STATUS,
>
> AUTHORIZED,
>
> CBT_SHARE_OBJ,
>
> PLANOGRAM,
>
> DIST_RATING_EXC,
>
> DIST_RATING_GOOD,
>
> UNTIL_DATE,
>
> AUTH_CODE,
>
> STD_ITEM_CODE,
>
> CASE_PROD_KEY,
>
> PACKAGE_CSE_QTY,
>
> SPACE_GUIDE_ICN,
> NEILSON_RANK_ICN,
> PROFIT_MARGIN,
> BRACKET_1,
>
> DELETED_FLAG,
>
> ECHO_FLAG,
>
> UPDATE_DATE_TIME,
>
> UPDATE_EMP_ICN )
>
> VALUES (
> CommonGlobalPackage.Process_ID,
> TAA.ACCT_ICN,
> AO.PROD_ICN,
>
> AO.BEGIN_DATE,
>
> AO.END_DATE,
>
> AO.CUST_ORDER_NBR,
>
> AO.PROD_STATUS,
>
> AO.AUTHORIZED,
>
> AO.CBT_SHARE_OBJ,
>
> AO.PLANOGRAM,
>
> AO.DIST_RATING_EXC,
>
> AO.DIST_RATING_GOOD,
>
> AO.UNTIL_DATE,
>
> AO.AUTH_CODE,
>
> AO.STD_ITEM_CODE,
>
> AO.CASE_PROD_KEY,
>
> AO.PACKAGE_CSE_QTY,
>
> AO.SPACE_GUIDE_ICN,
> AO.NEILSON_RANK_ICN,
> AO.PROFIT_MARGIN,
> AO.BRACKET_1,
> AO.DELETED_FLAG,
> AO.ECHO_FLAG,
> AO.UPDATE_DATE_TIME,
> AO.UPDATE_EMP_ICN);
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> /* this is dummy statement to handle exception*/
> rec_count := 0;
> END;
> ELSE
> BEGIN
> INSERT INTO TEMP_AUTHORIZE
> SELECT CommonGlobalPackage.Process_ID,
> AUTHORIZE.* FROM AUTHORIZE
> WHERE ACCT_ICN = TAA.ACCT_ICN AND
> PROD_ICN = AO.PROD_ICN;
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> /* this is dummy statement to handle exception*/
> rec_count := 0;
> END;
> END IF;
> END LOOP;/* end loop4*/
> END IF;
> END LOOP;/* end loop2*/
> END LOOP; /*end customer loop*/
> COMMIT;
> insert into adebug values('in here after 4 for'||to_char
> (sysdate,'mm/dd/yyyy hh24:mi:ss'));
> commit;
> FOR EP IN TEMP_PROD_CURSOR LOOP
> BEGIN
> SELECT EFD_ICN INTO xspace_guide_icn FROM SPACE_GUIDE_TABLE
> WHERE PROD_ICN = EP.PARENT_ICN;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> xspace_guide_icn := NULL;
> END;
> FOR TA IN TEMP_ACCOUNT_CURSOR LOOP
> rec_count := 0;
> /* check if it exists*/
> SELECT COUNT(*) INTO rec_count FROM AUTHORIZE
> WHERE ACCT_ICN = TA.ACCT_ICN AND
> PROD_ICN = EP.PARENT_ICN;
> IF rec_count = 0 THEN
> BEGIN
> INSERT INTO TEMP_AUTHORIZE (
> PROCESS_ID,
> ACCT_ICN,
> PROD_ICN,
> BEGIN_DATE,
> PROD_STATUS,
> END_DATE,
>
> AUTHORIZED,
>
> AUTH_CODE,
> SPACE_GUIDE_ICN,
> DELETED_FLAG,
> ECHO_FLAG,
> UPDATE_DATE_TIME,
> UPDATE_EMP_ICN )
>
> VALUES (
> CommonGlobalPackage.Process_ID,
> TA.ACCT_ICN,
> EP.PARENT_ICN,
> TO_DATE ('01/01/1996','MM/DD/YYYY'),
> '',
> TO_DATE ('12/31/2099','MM/DD/YYYY'),
>
> 'YES',
> 1,
> xspace_guide_icn,
> 0,
> 0,
> SYSDATE,
> .000002);
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> /* this is dummy statement to handle exception*/
> rec_count := 0;
> END;
> END IF;
> END LOOP;
> END LOOP;
> insert into adebug values('out of prod2 loop'||to_char
> (sysdate,'mm/dd/yyyy hh24:mi:ss'));
> commit;
> COMMIT;
>
> OPEN DOWNLOAD_CURSOR;
> insert into adebug values('opened download cursor'||to_char
> (sysdate,'mm/dd/yyyy hh24:mi:ss'));
> commit;
>
> EXCEPTION
> WHEN OTHERS THEN
> CommonGlobalPackage.Err_Msg := SUBSTR(SQLERRM, 1, 80);
> CommonGlobalPackage.Err_Num := SQLCODE;
> Help_DeskPackage.AddBackendRecord ('E',
> 140042,
> CommonGlobalPackage.Err_Msg,
> CommonGlobalPackage.Err_Num,
> 'ACCOUNT'
> );
> RAISE;
> END OpenCursor;
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Oct 02 1998 - 13:10:44 CDT