Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
A quick first scan of the code makes me think that this could all be done with a single UPDATE statement.... –  Phil Apr 11 '12 at 9:10
    
@Phil : tried it the first time, but because of the DBlink, i'm getting timed out. Forgot to mention that the DB link is running oracle 9i, and my instance is on oracle 11g R2 and dont tell anybody but its all in production :) So its heavily used while im trying to do this :( –  Alex Peta Apr 11 '12 at 9:51
3  
Get them to sort out the firewall timeouts then. I'm not a big fan of writing nasty workaround SQL (and increasing remote DB roundtrips as a result) because of infrastructure issues. Sorry to be blunt! –  Phil Apr 11 '12 at 9:53
1  
I agree with @phil. Your code has a loop where it is not necessary. I believe the correct thing to do just one update, you would be the correct test in a separate environment, this query to see if with a single query the execution becomes more performatic. If you experience a timeout error or disconnection, you should contact the DBA to check the problem with DBLINK. Not a good idea for you to slay the bench to relieve the network. The ideal is that both work together for better performance. –  Rainier Morilla Apr 11 '12 at 11:56
1  
Can you explain plan the v_get_avids and v_get_results queries & edit your answer with the output. Cheers. –  Phil Apr 11 '12 at 14:37

1 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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.