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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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

 

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

/

 

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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational