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 220.127.116.11 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:-)
Latest posts by Jacques (see all)
- 8 Nodes Cluster Upgrade from 18.104.22.168 to 22.214.171.124 - May 12, 2019
- RAC Database upgrade from 126.96.36.199 to 188.8.131.52 - May 12, 2019
- Managing Standby RedoLogs - May 12, 2019
- Cannot create database on ODA X6-2m after changing default port from 1521 to 13200 - January 8, 2018
- Setting up TAF with Data Guard: news from error ORA-16456 - December 13, 2016