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 


 

 

 


 

 

 

 
 

Oracle - Find unused Indexes

Oracle Tips by Burleson Consulting


Also see my notes on
detecting duplicate Oracle index columns and how to detect duplicate table rows.  There are 3 ways to write duplicate row detection queries.

Also, see my important notes on finding infrequently used indexes and detecting duplicate index columns.

Finding unused indexes in Oracle 10g and beyond

In Oracle10g, it is easy to see what indexes are used, when they are used and the context in which they are used. Here is a STATSPACK query to find un-used indexes. 

You can also download an AWR script
to detect unused indexes from the history tables:

<  statspack_unused_indexes.sql

 


ttitle "Unused Indexes by Time Period"

 

col owner heading "Index Owner" format a30

col index_name heading "Index Name" format a30

 

set linesize 95 trimspool on pagesize 80

 

select *
from

   (select
      owner,
      index_name

   from
      dba_indexes di

   where

      di.index_type != 'LOB'

   and

      owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')

minus

select
   index_owner owner,
   index_name

from
   dba_constraints dc

where

   index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')

minus

select

   p.object_owner owner,

   p.object_name  index_name

from

   stats$snapshot       sn,

   stats$sql_plan       p,

   stats$sql_summary    st,

   stats$sql_plan_usage spu

where

   st.sql_id = spu.sql_id
and
   spu.plan_hash_value = p.plan_hash_value

and

   st.hash_value = p.plan_hash_value

and

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

and

   sn.snap_id = spu.snap_id

and
   sn.dbid = spu.snap_id
and
   sn.instance_number = spu.instance_number

and

   sn.snap_id between &begin_snap and &end_snap

and

   p.object_type = 'INDEX'

)

where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')

order by 1, 2

/

 

Note in the script above the clause to ensure that the index is not used to enforce a primary key or foreign key relationship. 

Even though an index may appear to be used by SQL, it may be heavily used to enforce primary key and foreign key constraints.

 

 

Enter value for begin_snap: 48795

 

Enter value for end_snap: 48923

 

old  22:    sn.snap_id between &begin_snap and &end_snap

new  22:    sn.snap_id between 48795 and 48923

 

 

 

When dropping un-used indexes, also watch out for any indexes that may be used internally to enforce primary key foreign key relationships. 

If you have the licenses you can use STATSPACK and AWR to plot index usage.  Here is another script for tracking unused indexes and showing the invocation count of all indexes.  Best of all, this script shows the columns referenced for multi-column indexes:


col c1 heading 'Begin|Interval|time' format a20

col c2 heading 'Search Columns'      format 999

col c3 heading 'Invocation|Count'    format 99,999,999

 

 

break on c1 skip 2

 

accept idxname char prompt 'Enter Index Name: '

 

ttitle 'Invocation Counts for index|&idxname'

 

select

   to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,

   p.search_columns                                 c2,

   count(*)                                         c3

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   

   p.object_name = '&idxname'

group by

   begin_interval_time,search_columns;

 

This will produce an output like this, showing a summary count of the index specified during the snapshot interval. This can be compared to the number of times that a table was invoked from SQL.  Here is a sample of the output from this script:

 

Invocation Counts for cust_index

 

Begin

Interval                             Invocation

time                 Search Columns       Count

-------------------- -------------- -----------

04-10-21 15                       1           3

04-10-10 16                       0           1

04-10-10 19                       1           1

04-10-11 02                       0           2

04-10-11 04                       2           1

04-10-11 06                       3           1

04-10-11 11                       0           1

04-10-11 12                       0           2

04-10-11 13                       2           1

04-10-11 15                       0           3

04-10-11 17                       0          14

04-10-11 18                       4           1

04-10-11 19                       0           1

04-10-11 20                       3           7

04-10-11 21                       0           1

 Here is a simple AWR query to plot index usage:

col c1 heading 'Object|Name'       format a30
col c2 heading 'Operation'         format a15
col c3 heading 'Option'            format a15
col c4 heading 'Index|Usage|Count' format 999,999

break on c1 skip 2
break on c2 skip 2

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

You can still locate unused indexes in Oracle and Oracle 8i, but the mechanism for finding the unused indexes is not as easy.

Finding unused indexes on Oracle

One of the great features of Oracleis the ability to easily locate and remove unused indexes.  When an index is not used by SQL queries with the cost-based optimizer, the unused indexes waste space and cause INSERT statements to run slower.

When you issue the alter index <index_name> monitoring usage command, Oracle places an entry in the v$object_usage view so you can see if the index is used.  This is just a bit-flag that is set to "1" when the index is accessed by any SQL statement.

Here is a simple SQL*Plus script to track all index usage in all Oracle schemas:

set pages 999;
set heading off;
spool run_monitor.sql

select
   'alter index '||owner||'.'||index_name||' monitoring usage;'
from
   dba_indexes
where
   owner not in ('SYS','SYSTEM','PERFSTAT')
;


spool off;

@run_monitor

Next, we wait until a significant amount of SQL has executed on our database, and then query the new v$object_usage view:

select
   index_name
   mon,
   used
from
   v$object_usage;

Here we see that v$object_usage has a single column called used, which will be set to YES or NO.  Sadly, this will not tell you how many times the index has been used, but this tool is useful for investigating unused indexes.

INDEX_NAME              MON USED
---------------         --- ----
CUSTOMER_LAST_NAME_IDX  YES  NO  
 

Schema owners and finding unused indexes

The problem is that the v$object_usage view uses the username logged into database when the alter  index index_name monitoring usage; command  is issued.

The solution is to bypass the v$object_usage view and query the underlying sys.object_usage fixed table. 

select 
   io.name, t.name,
   decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
   decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
   u.start_monitoring,
   ou.end_monitoring 
from   
   sys.obj$ io,
   sys.obj$ t,
   sys.ind$ i,
   sys.object_usage ou 
where
   io.owner# = userenv('SCHEMAID') 
and 
   i.obj# = ou.obj# 
and 
   io.obj# = ou.obj# 
and 
   t.obj# = i.bo#;


This is another possible solution published by Oracle guru Rich Jesse:

select 
   u.name "owner",
   io.name "index_name",
   t.name "table_name",
   decode(bitand(i.flags, 65536), 0, 'no', 'yes') "monitoring",
   decode(bitand(nvl(ou.flags,0), 1), 0, 'no', 'yes') "used",
   ou.start_monitoring "start_monitoring",
   ou.end_monitoring "end_monitoring"
from 
   sys.obj$ io, 
   sys.obj$ t, 
   sys.ind$ i, 
   sys.object_usage ou, sys.user$ = u
where 
   t.obj# =3d i.bo#
and 
   io.owner# =3d u.user#
and 
   io.obj# =3d i.obj#
and 
   u.name not in ('sys','system')
and 
   i.obj# =3d ou.obj#(+);

Finding unused indexes in Oracle8i

This same technique for finding unused indexes can be done in Oracle8i, but it involves running complex scripts to interrogate the Oracle 8i library cache and parse for index names.  Burleson Consulting has developed a complete method to detect and remove un-used indexes in Oracle8 and Oracle8i databases.
 

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", 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 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.