Improve performance for my plsql block

Question

First of all let me make kind of a disclaimer here:

1) i'm a developer, not an administrator;

2) and i dont have admin rights on my database.

3) table specs :

-- oulp_path_attendance_base_t - 5.437.370 rows

-- ota_gsi_delegate_bookings_t@DISEULPROD (over dblink) - 3.135.400 rows

--inted_parted_events_t@DISEULPROD (over dblink) 683.912 rows

That being said , to the code we go!

CREATE OR REPLACE TYPE  "OULP_AVIDS" as table of varchar2(50)
/


CREATE OR REPLACE PROCEDURE "OULP_UPDATE_ATTENDACE_INCLASS"
AS
   TYPE users_completed_avids IS RECORD(
     avid oulp_path_attendance_base_t.avid%TYPE,
     close_date oulp_path_attendance_base_t.completed_date%TYPE
    );
  TYPE avid_results IS TABLE OF users_completed_avids;

  resulted_avids avid_results;


  v_users OULP_AVIDS;
  v_user_avids OULP_AVIDS;

  v_c SYS_REFCURSOR;
  v_get_avids SYS_REFCURSOR;
  v_get_results SYS_REFCURSOR;

BEGIN
 PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS START');
  OPEN v_c FOR
    SELECT distinct person_id
      FROM oulp_path_attendance_base_t
     WHERE avid is not null;

   LOOP
    FETCH v_c BULK COLLECT INTO v_users LIMIT 100;

    FOR i IN 1..v_users.COUNT LOOP


      OPEN v_get_avids FOR
      SELECT avid
        FROM oulp_path_attendance_base_t
       WHERE avid is not null
         AND person_id = v_users(i);

      FETCH v_get_avids BULK COLLECT INTO v_user_avids;
      CLOSE v_get_avids;      


     OPEN v_get_results FOR
      SELECT ev.activity_version_id avid,
             ev.course_end_date close_date
        FROM ota_gsi_delegate_bookings_t@DISEULPROD gsi,
             inted_parted_events_t@DISEULPROD ev
       WHERE gsi.event_id = ev.event_id
         AND gsi.delegate_id_emp = v_users(i)
         AND ota_status IN ('ATTENDED','Attended')
         AND ev.activity_version_id IN (SELECT column_value 
                                         FROM TABLE(v_user_avids));                              
        FETCH v_get_results BULK COLLECT INTO resulted_avids;   
        CLOSE v_get_results;        

        FORALL j IN 1..resulted_avids.COUNT
        UPDATE oulp_path_attendance_base_t 
           SET completed_date = resulted_avids(j).close_date
         WHERE avid = resulted_avids(j).avid
           AND person_id = v_users(i);



    END LOOP;   
      EXIT WHEN v_c%NOTFOUND;
   END LOOP;

  CLOSE v_c;
  PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS END');

  EXCEPTION
    WHEN others THEN 
  PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS FAILED');
END;

And the comments :

-- the purpose of this procedure is to updates my local table (oulp_path_attendance_base_t) with the close_dates for some activity_ids (named avids).

-- PROCESS_ERROR() procedure is just a simple logging procedure.

-- im running this via a scheduled job with dbms_scheduler.

Is there any way i can improve the performance here?

The job runs for about 10 hours and needs to run daily.

Cheers, Alex

Answer 1

If your problem is timing out and you have no way of solving that issue, maybe you might want to process your records in chunks, but also with a single update. Here is a little demo, just to give you the idea, you can use another chunking method other than mod if you don't have an incremental key. And I have to add, for the good practise follow Philip's advice if you can.

create table BIG_UPDATE as
(select 
    rownum COL1, 
    'data' COL2 from ALL_OBJECTS);

--612 msec
update(
    select COL1, COL2 from BIG_UPDATE
    where 
        mod(COL1, 8) = 0
)
set col2 = '1234';

--3secs
update(
    select COL1, COL2 from BIG_UPDATE
)
set col2 = '1234';

rollback;