oracle-databaseperformanceoptimizationplsqlbulk-operations

PLSQL script optimisation / tuning


I have this plsql script. I was able to test this on a test table with around 300 rows and it is working perfectly fine. However when I tried to run this using the actual table which is around 1M rows, it doesn't complete. I would like seek for your suggestion on how can I optimise my script, I am new to plsql so any ideas/suggestions are a great help. :)

DECLARE
    c_BGROUP    PP_TRANCHE_RBS.BGROUP%TYPE := 'RBS';
    l_start     NUMBER;

    /* Check for all entries where pt03d = pt04d+1. */
    CURSOR c_pp_tranche IS
        SELECT 
            refno,
            pt04d,
            seqno
        FROM PP_TRANCHE_RBS a
        WHERE a.BGROUP = c_BGROUP
        AND a.pt03d = (SELECT (pt04d + 1) 
                       FROM PP_TRANCHE_RBS 
                       WHERE bgroup = a.bgroup 
                       AND refno = a.refno 
                       and seqno = a.seqno)
    ;

    TYPE c_refno IS TABLE OF PP_TRANCHE_RBS.REFNO%TYPE;
    TYPE c_pt04d IS TABLE OF PP_TRANCHE_RBS.PT04D%TYPE;
    TYPE c_seqno IS TABLE OF PP_TRANCHE_RBS.SEQNO%TYPE;

    t_refno c_refno;
    t_pt04d c_pt04d;
    t_seqno c_seqno;

BEGIN

    DBMS_OUTPUT.put_line('Updating rows... ');

    l_start := DBMS_UTILITY.get_time;

    OPEN c_pp_tranche;
    LOOP

        FETCH c_pp_tranche BULK COLLECT INTO t_refno, t_pt04d, t_seqno LIMIT 10000; -- break the data into chucks of 10000 rows
        EXIT WHEN t_refno.COUNT() = 0; -- cursor attribute to exit when 0.

        FORALL i IN t_refno.FIRST .. t_refno.LAST

            /* Update pt03d = pt04d */
            UPDATE PP_TRANCHE_RBS
            SET pt03d = t_pt04d(i)
            WHERE
                bgroup = c_BGROUP
                AND refno = t_refno(i)
                AND seqno = t_seqno(i)
            ;   

        -- Process contents of collection here.
        DBMS_OUTPUT.put_line(t_refno.count || ' rows was updated');     

    END LOOP;

        DBMS_OUTPUT.put_line('Bulk Updates Time: ' || (DBMS_UTILITY.get_time - l_start));

    CLOSE c_pp_tranche;
END;
/

exit;

Solution

  • Equivalent pure SQL statement:

      UPDATE PP_TRANCHE_RBS
      SET pt03d = pt04d
      WHERE bgroup = 'RBS'
      and   pt03d = pt04d + 1;
    

    This will probably run faster than your procedural version. PL/SQL bulk processing is faster than row-by-row but it's usually slower than a single set-based operation. So save it for those times when you have complicated transformation logic which can only be handled procedurally.