Re: Tuning of a stored oracle procedure

Hi.

I agree.
At least you have to supply TKPROF output for the executionm of your procedure.

   Michael.

In article <36151724.8D01CDC8_at_sky.net>,   John Durst <jdurst_at_sky.net> wrote:
> 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
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 02 1998 - 16:33:01 CDT