When it comes to Oracle performance tuning, one of the invaluable features that DBAs and Performance experts have in their toolbox is SQL Stored Outlines. Much has been written about stored outlines since it earlier days. One of such interesting articles, can be found here.
Though the supplied PL/SQL package provides many useful routines to play with the outlines, one critical item that is being missed in the package set is a routine to indicate whether a stored outline is consistently being used? And more importantly, when was the last time a stored outline was used? Granted, we can “clear” the stored outlines’ usage and see if it resets to ensure that the outline is being used but that seems too much intrusion to the live production system. While working on this issue, I discovered a simpler way to determine the last time an outline had been used for a given SQL.
With Oracle 10g there has been great improvements in the V$ views area providing a wealth of information without the need to access many V$ views. This tradition has been carried over to 10g R2 as well. The V$SQL view in 10g R2 has new columns that cater to stored outlines. One such column is the LAST_ACTIVE_TIME. By querying the SQL_ID, OUTLINE_CATEGORY, and LAST_ACTIVE_TIME columns from V$SQL you can verify whether a stored outline is being actively used and also the last time it was used.
Here is an example.
NOTE: The outline creation steps are assumed.
11:52:27 SQL> alter session set use_stored_outlines=HV;
Session altered.
11:55:43 SQL> select count(*) from ptn_tab where col2=2;
COUNT(*)
----------
300000
11:56:57 SQL> select name,used from dba_outlines where name='HV';
NAME USED
-------------------- ------
HV USED
11:58:00 SQL> exec dbms_outln.clear_used('HV');
PL/SQL procedure successfully completed.
11:58:10 SQL> select name,used from dba_outlines where name='HV';
NAME USED
-------------------- ------
HV UNUSED
11:58:18 SQL> select count(*) from ptn_tab where col2=2;
COUNT(*)
----------
300000
11:58:59 SQL> select name,used from dba_outlines where name='HV';
NAME USED
-------------------- ------
HV USED
11:59:04 SQL>
1 select sql_id,outline_category,exact_matching_signature,
2 force_matching_signature,last_active_time
3* from v$sql where sql_text like 'select count(*) from ptn_tab%'
11:59:06 SQL> /
SQL_ID OU EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- -- ------------------------ ------------------------
LAST_ACTIVE_TIME
--------------------
10d6hmvy8t74n HV 0 0
08-FEB-2008 11:58:59
Though the supplied PL/SQL package provides many useful routines to play with the outlines, one critical item that is being missed in the package set is a routine to indicate whether a stored outline is consistently being used? And more importantly, when was the last time a stored outline was used? Granted, we can “clear” the stored outlines’ usage and see if it resets to ensure that the outline is being used but that seems too much intrusion to the live production system. While working on this issue, I discovered a simpler way to determine the last time an outline had been used for a given SQL.
With Oracle 10g there has been great improvements in the V$ views area providing a wealth of information without the need to access many V$ views. This tradition has been carried over to 10g R2 as well. The V$SQL view in 10g R2 has new columns that cater to stored outlines. One such column is the LAST_ACTIVE_TIME. By querying the SQL_ID, OUTLINE_CATEGORY, and LAST_ACTIVE_TIME columns from V$SQL you can verify whether a stored outline is being actively used and also the last time it was used.
Here is an example.
NOTE: The outline creation steps are assumed.
11:52:27 SQL> alter session set use_stored_outlines=HV;
Session altered.
11:55:43 SQL> select count(*) from ptn_tab where col2=2;
COUNT(*)
----------
300000
11:56:57 SQL> select name,used from dba_outlines where name='HV';
NAME USED
-------------------- ------
HV USED
11:58:00 SQL> exec dbms_outln.clear_used('HV');
PL/SQL procedure successfully completed.
11:58:10 SQL> select name,used from dba_outlines where name='HV';
NAME USED
-------------------- ------
HV UNUSED
11:58:18 SQL> select count(*) from ptn_tab where col2=2;
COUNT(*)
----------
300000
11:58:59 SQL> select name,used from dba_outlines where name='HV';
NAME USED
-------------------- ------
HV USED
11:59:04 SQL>
1 select sql_id,outline_category,exact_matching_signature,
2 force_matching_signature,last_active_time
3* from v$sql where sql_text like 'select count(*) from ptn_tab%'
11:59:06 SQL> /
SQL_ID OU EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- -- ------------------------ ------------------------
LAST_ACTIVE_TIME
--------------------
10d6hmvy8t74n HV 0 0
08-FEB-2008 11:58:59
