Updating via cursor is taking a lot of time

Question

I'm performing address association by taking the addr_id and updating it in two tables (same table and another table) for different columns. However it is taking huge time even though I use parallel hint with degree of paralleism as 500. Could you assist here ? Below is the procedure.

PROCEDURE 
ADD_ASOC AS
BEGIN
DECLARE
CURSOR C1 IS
SELECT /*+ PARALLEL(500) */ ADDR_ID FROM TEMP_ADDR WHERE BATCH_RANGE BETWEEN 100 AND 900;

TYPE CURSOR_ATT IS TABLE OF C1%ROWTYPE;

L_CURSOR CURSOR_ATT;

    BEGIN
    OPEN C1;
    LOOP

    FETCH C1 BULK COLLECT INTO          L_CURSOR LIMIT 5000;
EXIT WHEN L_CURSOR.COUNT = 0;

FOR I IN 1 .. L_CURSOR.COUNT LOOP

UPDATE  /*+ PARALLEL(500) */ MAIN_ADDR SET ADR_NAME = L_CURSOR(I).ADDR_ID||'RESIDENCE'
WHERE ADDR_ID = L_CURSOR(I).ADDR_ID;



UPDATE  /*+ PARALLEL(500) */ TEMP_ADDR SET ADR_NAME = L_CURSOR(I).ADDR_ID||'RESIDENCE'

WHERE ADDR_ID = L_CURSOR(I).ADDR_ID
AND BATCH_RANGE BETWEEN 100 AND 900;

COMMIT;
END LOOP;
END LOOP;
COMMIT;
CLOSE C1;
END;
COMMIT;

I executed using degree of paralleism as 500 but in vain.

Answer 1

• A parallel hint on a single-row update statement is, at best, pointless.
• commit is very expensive. A commit on every single iteration of a loop would be catastrophically slow.
• Doing a forall is more efficient than a for loop. Doing the update in a single SQL statement would be the most efficient approach.
• How did you come up with 500 as the degrees of parallelism for your select statement? It seems highly, highly unlikely that is a reasonable number.

Beyond that, what have you done to trace your code or to see what's taking time. We can certainly guess at things that might be slow but we have no way to see if, for example, one of your tables is missing an index on addr_id or if there are a bunch of unindexed foreign keys that need to be managed or if there are triggers that are slowing things down.

The forall approach that would be faster (not as fast as individual update statements but better than row-by-row processing in a loop).

FORALL I IN 1 .. L_CURSOR.COUNT
  UPDATE MAIN_ADDR 
     SET ADR_NAME = L_CURSOR(I).ADDR_ID||'RESIDENCE'
   WHERE ADDR_ID = L_CURSOR(I).ADDR_ID;

FORALL I IN 1 .. L_CURSOR.COUNT
  UPDATE TEMP_ADDR 
     SET ADR_NAME = L_CURSOR(I).ADDR_ID||'RESIDENCE'
   WHERE ADDR_ID = L_CURSOR(I).ADDR_ID
     AND BATCH_RANGE BETWEEN 100 AND 900;

UPDATE /*+ PARALLEL(500) */ TEMP_ADDR SET ADR_NAME = L_CURSOR(I).ADDR_ID||'RESIDENCE'

WHERE ADDR_ID = L_CURSOR(I).ADDR_ID AND BATCH_RANGE BETWEEN 100 AND 900;