PLSQL script optimisation / tuning

Question

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;

Any error messages? What is the value of your DBMS_OUTPUT.put_line() calls? — tale852150, Jun 14 '17 at 14:09
nothing for DBMS_OUTPUT.put_line(t_refno.count || ' rows was updated'); ?? Set dbms_output.enable(NULL); at the start of your code. If you are running this via SQLPlus also run *set serveroutput on* prior to running your code. — tale852150, Jun 14 '17 at 14:21
@tale852150 the server output isn't fetched until after the procedure has completed — Boneist, Jun 14 '17 at 14:53
@Boneist understood and thank you. But I would suspect some output. I am referring to a "good" run. When it does not complete of course there'd be no output. From the OP's question it just "hangs"... — tale852150, Jun 14 '17 at 14:54
@Shin can you try running with a test table of 20000 rows? Or change your limit to 200 and retry using your test table of 300 rows? — tale852150, Jun 14 '17 at 15:04
@tale852150 when I changed the limit to 200 for my test table of 300 rows. It works and got the ff. output 200 and 100 rows was updated, same with the 20000 rows. It is really weird that is working for this rows. — Shin, Jun 14 '17 at 15:14
Did you run an explain plan on the individual SQL statements? — OldProgrammer, Jun 14 '17 at 15:26
When I try to run this select statement in sql developer on the actual tables " 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)." it just keep on running for more than 3hrs until it hangs. — Shin, Jun 14 '17 at 15:32
@Shin - I think that is your first issue. Tune that SQL. The PL/SQL may not really be "hanging", just taking a very long time due to the poor performance of the SQL. As mentioned by OldProgrammer, could you provide an EXPLAIN PLAN on that SQL and add it to the question? — tale852150, Jun 14 '17 at 15:49
Okay. Will do that but I will do some research on how Explain plan works as I am not familiar with it. — Shin, Jun 14 '17 at 16:16
@Shin - for your SELECT statement run EXPLAIN PLAN FOR SELECT .... ; then run SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); — tale852150, Jun 14 '17 at 18:46

score 4 · Accepted Answer · answered Jun 14 '17 at 16:46

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.

PLSQL script optimisation / tuning

1 Answers1