Managing load KPIs following a merge statement

Hi there,

I have a customer that currently complain on some queries  taking too long during his nightly load jobs. After a quick look, I discover few queries doing “select count (*)” taking more than 80 minutes to complete!

Environment is: Oracle version is 11.2.0.4 on AIX

Curiously during the day, the same query is taking less than 7 minutes to complete. I discover then that during the day a “fast full index scan” was triggered by the optimizer while during the night it was a “full index scan” … The table has more than 500 millions rows and so it’s slow…

I have checked all optimizer parameters during the nightly execution using the “+outline” format and nothing explain the different plan.

The only thing that has changed is “upgrade from AIX 5.1 to AIX 6.1”. All AIX parameters are correctly set and are following Oracle recommended values.

The query is execute before and after a merge. The only reason to do that is to count the number of inserted rows to generate loading KPIs 🙁

Here is a simple way to get that without running such select (*) queries.

Create a simple package like this:

Create or replace package TrackMerge AS
UpdateCount NUMBER;
DeleteCount NUMBER;
FUNCTION Increment_Update  RETURN NUMBER;
FUNCTION Increment_Delete  RETURN NUMBER;
Procedure Init;
END;
/

Create or replace package BODY TrackMerge IS
FUNCTION Increment_Update RETURN NUMBER AS
BEGIN
 UpdateCount:=UpdateCount+1;
 RETURN 0;
 EXCEPTION WHEN OTHERS THEN
  RETURN 0;
END;
FUNCTION Increment_Delete RETURN NUMBER AS
BEGIN
 DeleteCount:=DeleteCount+1;
 RETURN 0;
 EXCEPTION WHEN OTHERS THEN
  RETURN 0;
END;
Procedure Init as
Begin
UpdateCount  := 0;
DeleteCount  := 0;
End;
end;
/ 

 

Do your merge:

SET SERVEROUTPUT ON
BEGIN
  TrackMerge.init;  -- Init your counters
 
  MERGE INTO MyDest d
    USING MySource s
    ON (d.object_id = s.object_id)
    WHEN MATCHED THEN
      UPDATE SET
        owner       = s.owner,
        object_name = s.object_name,
        object_type = s.object_type where TrackMerge.Increment_Update = 0
      Delete where (s.object_type = 'VIEW' and TrackMerge.Increment_Delete = 0)
    WHEN NOT MATCHED THEN
      INSERT (object_id, owner, object_name, object_type)
      VALUES (s.object_id, s.owner, s.object_name, s.object_type);

dbms_output.put_line ('Total row impacted: '|| SQL%ROWCOUNT);
dbms_output.put_line ('Number of records Inserted: '|| to_char (SQL%ROWCOUNT - TrackMerge.UpdateCount - TrackMerge.DeleteCount));
dbms_output.put_line ('Number of records Updated:  '|| TrackMerge.UpdateCount);
dbms_output.put_line ('Number of records Deleted:  '|| TrackMerge.DeleteCount);

END;
/

anonymous block completed
Total row impacted: 93626
Number of records Inserted: 45782
Number of records Updated:  44023
Number of records Deleted:  3821

This will avoid a lots of I/O just to count inserted rows:-)

Jko

 

 

 

 

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

Leave a Reply

Your email address will not be published. Required fields are marked *