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
Object_Name | Option | Index_Usage_Count |
---|---|---|
AR_RECEIVABLE_APPLICATIONS_N1 | RANGE SCAN | 282 |
SKIP SCAN | 4 | |
AR_RECEIVABLE_APPLICATIONS_N11 | RANGE SCAN | 150 |
AR_RECEIVABLE_APPLICATIONS_N15 | RANGE SCAN | 156 |
AR_RECEIVABLE_APPLICATIONS_N9 | RANGE SCAN | 147 |
AR_RECEIVABLE_APPLICATIONS_U1 | UNIQUE SCAN | 2,667 |