Question

Need help with a slow pl/sql script

PL, SQL, script, Oracle 10, PL/SQL

Hello. I am running this script and it is taking over 3 hours on Oracle 10. Can anyone help.
----------------------------------
spool noaddressusagerelfix.LOG
set timing on;
SET SERVEROUT ON;

DECLARE
vpartyCustomerNo TableB.I_OCPRTY_USAGES%TYPE;
vI_OCPOSTAD_USAGES TableB.I_OCPOSTAD_USAGES%TYPE;
vC_OCPOSTAD_USAGES TableB.C_OCPOSTAD_USAGES%TYPE;
vpartyAddressUsageIValue TableB.I%TYPE;
vpartyAddressUsageCValue TableB.C%TYPE;
vCounter NUMBER(10):=0;

CURSOR cursor1 IS
SELECT C, I, I_OCPRTY_USAGES FROM TableB
WHERE I_OCPOSTAD_USAGES IS NULL
AND C_OCPOSTAD_USAGES IS NULL;


CURSOR cursor2 IS
SELECT I_OCPOSTAD_USAGES, C_OCPOSTAD_USAGES FROM TableA
WHERE I_OCPRTY_USAGES = vpartyCustomerNo
AND I_OCPOSTAD_USAGES IS NOT NULL
AND C_OCPOSTAD_USAGES IS NOT NULL
AND USAGE IN(1088000,1088001)
AND ROWNUM = 1;

BEGIN
DBMS_OUTPUT.PUT_LINE('---- Start ----');

OPEN cursor1;
LOOP
FETCH cursor1 INTO vpartyAddressUsageCValue, vpartyAddressUsageIValue,vpartyCustomerNo;
EXIT WHEN cursor1%NOTFOUND;

OPEN cursor2;
LOOP
FETCH cursor2 INTO vI_OCPOSTAD_USAGES,vC_OCPOSTAD_USAGES;
EXIT WHEN cursor2%NOTFOUND;
/* Update TableB set the usages */
UPDATE TableB SET I_OCPOSTAD_USAGES = vI_OCPOSTAD_USAGES,
C_OCPOSTAD_USAGES = vC_OCPOSTAD_USAGES
WHERE I = vpartyAddressUsageIValue
AND C = vpartyAddressUsageCValue
AND I_OCPRTY_USAGES = vpartyCustomerNo;

vCounter := vCounter + 1;
END LOOP;
CLOSE cursor2;
END LOOP;
CLOSE cursor1;

DBMS_OUTPUT.PUT_LINE('---- NO. OF RECORDS CHANGED ----');
DBMS_OUTPUT.PUT_LINE(vCounter);
DBMS_OUTPUT.PUT_LINE('--------------------------------');

COMMIT;

DBMS_OUTPUT.PUT_LINE('---- END ----');
END;
/
set timing off
spool off
-------------------------------------

Thanks in advance

Hello Darrankavanagh

I would suggest the following:

- Since cursor2 will allways return only 1 row, there is no need to use acursor. It would be better to use a 'select' .
- It would be better to use a cursor for loop for cursor1. If you define it as 'for update' you can take advantage of the 'where current of" condition to update the rows.

For example:

DECLARE
 vI_OCPOSTAD_USAGES TableB.I_OCPOSTAD_USAGES%TYPE;
 vC_OCPOSTAD_USAGES TableB.C_OCPOSTAD_USAGES%TYPE;
 vCounter NUMBER(10):=0;
 
 CURSOR cursor1 IS
 SELECT C, I, I_OCPRTY_USAGES FROM TableB 
 WHERE I_OCPOSTAD_USAGES IS NULL
 AND C_OCPOSTAD_USAGES IS NULL
 FOR UPDATE OF I_OCPOSTAD_USAGES;
 
 BEGIN 
 DBMS_OUTPUT.PUT_LINE('---- Start ----'); 
 for i in cursor1 LOOP
    SELECT I_OCPOSTAD_USAGES, C_OCPOSTAD_USAGES into vI_OCPOSTAD_USAGES,vC_OCPOSTAD_USAGES
 	FROM TableA 
 	WHERE I_OCPRTY_USAGES = I.I_OCPRTY_USAGES
 		AND I_OCPOSTAD_USAGES IS NOT NULL
 		AND C_OCPOSTAD_USAGES IS NOT NULL
 		AND USAGE IN(1088000,1088001)
 		AND ROWNUM = 1;
 
    /* Update TableB set the usages */
 
    UPDATE TableB SET I_OCPOSTAD_USAGES = vI_OCPOSTAD_USAGES, C_OCPOSTAD_USAGES = vC_OCPOSTAD_USAGES 
 	WHERE current of cursor1;
    vCounter := vCounter + 1;
 END LOOP;
 
 DBMS_OUTPUT.PUT_LINE('---- NO. OF RECORDS CHANGED ----');
 DBMS_OUTPUT.PUT_LINE(vCounter);
 DBMS_OUTPUT.PUT_LINE('--------------------------------');
 
 COMMIT;
 
 DBMS_OUTPUT.PUT_LINE('---- END ----');
 END;

Regards,

-cadlg