How to optimize the PL/SQL code that uses cursor

Question

I am trying to optimize a query that is running for around 3 hours to fetch/update 2.8 million records. The code uses cursor and we need to update the entire set of 2.8 million records. I tried modifying the query using bulk collect but i dont think it has helped much. Could someone please advise me on what else i can do to optimize the code. To be honest i dont think it is any better at all even with bulk collect... I am using oracle19c

Old code is:

DECLARE
    CURSOR PROVNUMCURSOR
    IS
      SELECT DISTINCT RCRD_NBR, ID, Entity FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM ;
    v_FirstRow SPSMDMRW.GTT_SCW_PROFILE_INTRM.RCRD_NBR%type;
    v_ID SPSMDMRW.GTT_SCW_PROFILE_INTRM.ID%type;
    v_Entity SPSMDMRW.GTT_SCW_PROFILE_INTRM.Entity%type;
  BEGIN
    OPEN PROVNUMCURSOR;
    --v_FirstRow:=0;v_ID:=0;v_Entity:='0';
    FETCH PROVNUMCURSOR
    INTO v_FirstRow,
      v_ID,
      v_Entity;
    WHILE PROVNUMCURSOR%FOUND
    LOOP
      SELECT
        CASE
          WHEN v_ID = v_EndRow
          THEN v_MaxRow
          ELSE
            (SELECT RCRD_NBR-1 FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM WHERE ID = v_ID+1
            )
        END
      INTO v_LastRow
      FROM dual;
      IF v_Entity = '3' THEN
        UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF a
        SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
          ||'I'
        WHERE RCRD_NBR BETWEEN v_FirstRow AND v_LastRow;
      ELSE
        IF v_Entity = '4' THEN
          UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF a
          SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
            || 'II'
          WHERE RCRD_NBR BETWEEN v_FirstRow AND v_LastRow;
        ELSE
          UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF a
          SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
          WHERE RCRD_NBR BETWEEN v_FirstRow AND v_LastRow;
        END IF;
      END IF;
      FETCH PROVNUMCURSOR INTO v_FirstRow, v_ID, v_Entity;
      EXIT
    WHEN PROVNUMCURSOR%NOTFOUND;
    END LOOP;
    --END;
    CLOSE PROVNUMCURSOR;
  END;

The code that i modified is the below:

DECLARE
    CURSOR PROVNUMCURSOR
    IS
      SELECT DISTINCT RCRD_NBR,
        ID,
        Entity
      FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST ;
  type v_ID_type
IS
  TABLE OF SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST.ID%type;
type v_Entity_type
IS
  TABLE OF SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST.Entity%type;
type v_FirstRow_type
IS
  TABLE OF SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST.RCRD_NBR%type;
  v_FirstRow v_FirstRow_type;
  v_ID v_ID_type;
  v_Entity v_Entity_type;
  c_limit PLS_INTEGER := 100;
BEGIN
  OPEN PROVNUMCURSOR;
  --v_FirstRow:=0;v_ID:=0;v_Entity:='0';
  LOOP
    FETCH PROVNUMCURSOR bulk collect INTO v_FirstRow, v_ID,v_Entity LIMIT C_LIMIT;
    EXIT
  WHEN V_FIRSTROW.COUNT = 0;
    --dbms_output.put_line(V_FIRSTROW.COUNT);
    FOR INDX IN 1..V_FIRSTROW.COUNT
    LOOP
      --      dbms_output.put_line(1);
      SELECT
        CASE
          WHEN v_ID(INDX) = v_EndRow
          THEN v_MaxRow
          ELSE
            (SELECT RCRD_NBR-1
            FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST
            WHERE ID = v_ID(INDX)+1
            )
        END
      INTO v_LastRow
      FROM dual;
      IF v_Entity(INDX) = '3' THEN
        UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF_TEST a
        SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
          ||'I'
        WHERE RCRD_NBR BETWEEN v_FirstRow(INDX) AND v_LastRow;
      ELSIF v_Entity(INDX) = '4' THEN
          --DBMS_OUTPUT.PUT_LINE(v_entity || ' First Row '|| v_FirstRow || ' Last row '||v_LastRow);
          UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF_TEST a
          SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
            || 'II'
          WHERE RCRD_NBR BETWEEN v_FirstRow(INDX) AND v_LastRow;
        ELSE
         -- DBMS_OUTPUT.PUT_LINE(v_entity || ' First Row '|| v_FirstRow || ' Last row '||v_LastRow);
          UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF_TEST a
          SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
          WHERE RCRD_NBR BETWEEN v_FirstRow(INDX) AND v_LastRow;
        END IF;
     END LOOP;
  END LOOP;
  CLOSE PROVNUMCURSOR;
END;

Sample data:
RCRD_NBR    FILE_NM       PROVNUM   ID  ENTITY
225184      sample.txt              1681    2
225241      sample.txt              1682    3
225352      sample.txt              1683    4
225436      sample.txt              1684    5
225493      sample.txt               1685   2

Please provide some sample data. I would assume you can do it all in one single UPDATE statement. No cursor, no loop, no bulk fetch. — Wernfried Domscheit
In addition to that, have you done any profiling to see where the time is actually being spent? It is entirely possible, for example, that you're missing an index/ there is a trigger on the table/ there is an unindexed foreign key that is actually causing performance to be slow. — Justin Cave
What is v_MaxRow? It is not declared in your code, i.e. it should not work at all. — Wernfried Domscheit
@JustinCave The table is indexed on rcrd_nbr and there is no trigger on the table. Also, no unindexed foreign keys are there. The issue that i was able to find was with the update statement within the code that is taking a lot of time. I observed that in toad — Aswin Ajai

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2021-01-20T21:16:58

Without having any sample data and without knowing the logic it is difficult to provide a solution. I think it could be done with a single statement which goes in a direction like this one:

UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF a SET ProvNum =
with t as
    (SELECT DISTINCT
       RCRD_NBR as FirstRow,
       LEAD(RCRD_NBR, 1) -1 OVER (ORDER BY ID) AS LastRow,
       CASE Entity
           when '3' then TRIM(SUBSTR(Column0, 1, 13))||'I'
           when '4' then TRIM(SUBSTR(Column0, 1, 13))||'II'
           else TRIM(SUBSTR(Column0, 1, 13))
       end case as ProvNum
    FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM)
SELECT t.ProvNum
from t
where a.RCRD_NBR BETWEEN FirstRow AND LastRow;

How to optimize the PL/SQL code that uses cursor

1 Answers