Ken Gottry - Cambridge NY History

Oracle maintains all Explain Plans in DBA_HIST_SQLPLAN. Here's how to analyze that table to see which indexes are used and how

This SQL script pulls index info from every explain plan that's in AWR history tables. The amount of information is controlled by the retention period set via dbms_workload_repository.modify_snapshot_settings . The output shows how each index is used in the explain plan (RANGE SCAN, SKIP SCAN, etc) so you can tell if Oracle is using the index the way you thought it was going to. At the bottom is sample output. I spool it as HTML then open with Excel to sort, filter, etc. An interesting exercise is to compare the output of this script with a list of indexes stored in DBA_INDEXES. It's a quick way to find unused indexes, assuming your AWR retention is long enough. I ask for 45 days so I'm sure to get month end SQL

Here's the script. below that is sample output

SQL Source Code

set mark html ON
spool KEN_index_usage.html
set linesize 131
set pagesize 999
set trimspool on
col c1 heading 'Object_Name' format a30
col c3 heading 'Option' format a35
col c4 heading 'Index_Usage_Count' format 999,999

break on c1 skip 2

select
   p.object_name c1,
   p.options     c3,
   count(1)      c4
from
   dba_hist_sql_plan p,
   dba_hist_sqlstat s
where
   p.object_owner <> 'SYS'
and
   p.operation like '%INDEX%'
and
   p.sql_id = s.sql_id
group by
   p.object_name,
   p.options
order by
   1,2,3;

spool off
set mark html off
prompt Output written to KEN_index_usage.html

Sample Output