Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Find infrequently used indexes

Oracle Database Tips by Donald BurlesonJanuary 10, 2015

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



   to_char(sn.begin_interval_time,'Month') c1,

   p.object_owner c2,

   p.object_name c3,

   sum(executions_delta) c4


   dba_hist_snapshot   sn,

   dba_hist_sql_plan   p,

   dba_hist_sqlstat    st


   st.sql_id = p.sql_id


   sn.snap_id = st.snap_id
   sn.dbid = st.dbid
   sn.instance_number = st.instance_number


   p.object_type = 'INDEX'


   p.object_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP')

group by

   to_char(sn.begin_interval_time, 'Month'),



   sum(executions_delta) < 50

order by
   1, 4 desc, 2, 3



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.

If you like Oracle tuning, you may enjoy my bestselling book Oracle Tuning: The Definitive Reference", with over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.