It’s not uncommon for infrequently used indexes to consume more resources
than they save when they are used. For highly active tables, each and
every DML statement requires Oracle to keep the index current, and you must
carefully weigh the relative saving of the index on the infrequent SQL
against the continuous overhead of maintaining the index.
Also, see my important notes on
finding
unused indexes. and
detecting duplicate index columns.
Disk space is cheap so the
cost of the disk required to store the index is usually negligible.
Infrequently-used indexes do not impose a heavy overhead for static tables
that are rarely updated, but they can wreak havoc on volatile table, causing
a measurable amount of operational overhead for very little benefit.
Remember, indexes always add overhead to DML operations, and it may often be
a good practice for weekly and monthly jobs to drop the index and consider
one of these alternative methods:
1. Create the index, run the job and then drop the index.
2. Allow the infrequent job to choose another plan that does not involve
the index.
The below query can be used to find infrequently-used indexes.
When using the query below, adjust the count to match your specific workload
(my default is 50 invocations). You can also change the date format
mask to change the aggregation period (currently set to monthly (mon)).
Expert tip!
It is not necessary to purchase the extra-cost packs to run the below AWR
query! You can get this same information directly from the STATSPACK
table
stats$sql_plan at no additional cost! If you don’t want to
write your own query you can use
Ion for Oracle.
ttitle "Infrequently-used indexes by month"
col c1 heading "Month"
format a20
col c2 heading "Index Owner"
format a30
col c3 heading "Index Name"
format a30
col c4 heading "Invocation|Count" format 99
set linesize 95 trimspool on pagesize 80
select
to_char(sn.begin_interval_time,'Month') c1,
p.object_owner c2,
p.object_name c3,
sum(executions_delta) c4
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
SEE CODE DEPOT
FOR WORKING SCRIPT
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
sn.instance_number = st.instance_number
and
p.object_type = 'INDEX'
and
p.object_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS',
'DBSNMP')
group by
to_char(sn.begin_interval_time, 'Month'),
p.object_owner,
p.object_name
having
sum(executions_delta) < 50
order by
1, 4 desc, 2, 3
/
Here is the output of the infrequent index report usage
report:
Sun Jan 10
page 1
Infrequently-used indexes by month
Invocation
Month
Index Owner Index
Name
Count
--------------------
---------------- ------------------------------ ----------
January
ROBOHEAD CIX_ATTREF_LIBFID
44
January
ROBOHEAD CIX_THUBNL_FAID
44
January
ROBOHEAD CIX_AUDIT_TASKID
42
January
ROBOHEAD CIX_FOLDER_GID
28
January
ROBOHEAD PERF2_C_FOLDER_SEC
13
January
ROBOHEAD CIX_AUDIT_TASK_ID
11
January
ROBOHEAD CIX_PF_SI
11
January
ROBOHEAD IX_ACCOUNT_1
8
January
ROBOHEAD CIX_AUDIT_NOTESID
3
January
ROBOHEAD CIX_SAVERPT_UID_TYPE
3
January
ROBOHEAD CIX_FLDRTRCK_FLDR
2
January
PERFSTAT DBA_HIST_IDX_STAT_DATE_IDX
1
January
ROBOHEAD CIX_AL_UI
1
Again, you need to carefully perform a cost-benefit
analysis against any infrequently-used indexes:
- Benefit of the index – It’s easy to see the benefit
because the SQL is infrequently used. Simply run the query in your
QA database once with the index and again without the index.
- Cost of the index – As noted the cost of an
infrequently used index is directly proportional to the amount of
updates to the target table. For each DML, the overhead to
maintain the index may not be measurable, but it can add-up fast for
highly active tables that perform thousands of updates per day.
