| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Tuning of a stored oracle procedure
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*/
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;
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*/
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;
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 02 1998 - 10:20:48 CDT
 |
 |