identifing unused indexes

PURPOSE
     
     
     
     

-------
     
     

The purpose of this document is to explain how to find unused indexes 
     
     

using the new feature in Oracle 9i: "Identifying Unused Indexes" via
     
     

ALTER INDEX MONITORING USAGE, as mentioned in Oracle9i Database 
     
     

Administrator's Guide, Chapter 11.
     
     

The clause MONITORING / NOMONITORING USAGE is useful in determining
     
     

whether an index is being used. 
     
     

SCOPE & APPLICATION
     
     

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

This article is intended for database Administrators who want to
     
     

identify unused indexes in their database.
     
     

IDENTIFYING UNUSED INDEXES
     
     

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

You can find indexes that are not being used by using the ALTER INDEX
     
     

MONITORING USAGE functionality over a period of time that is
     
     

representative of your workload.
     
     

PART 1 will demonstrate the new feature using a simple example.
     
     

PART 2 will give a detailed instruction how to identify all unused
     
     

indexes in the database.
     
     

PART 1  -  Monitoring usage of indexes - a simple example
     
     

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

To demonstrate the new feature, you can use the following example:
     
     

(a) Create and populate a small test table
     
     

(b) Create Primary Key index on that table
     
     

(c) Query v$object_usage: the monitoring has not started yet
     
     

(d) Start monitoring of the index usage
     
     

(e) Query v$object_usage to see the monitoring in progress
     
     

(f) Issue the SELECT statement which uses the index
     
     

(g) Query v$object_usage again to see that the index has been used
     
     

(h) Stop monitoring of the index usage
     
     

(i) Query v$object_usage to see that the monitoring stopped
     
     

Detailed steps:
     
     

(a) Create and populate a small test table
     
     

   create table products
     
     

   (prod_id number(3),
     
     

    prod_name_code varchar2(5));
     
     

   insert into products values(1,'aaaaa');
     
     

   insert into products values(2,'bbbbb');
     
     

   insert into products values(3,'ccccc');
     
     

   insert into products values(4,'ddddd');
     
     

   commit;
     
     

(b) Create Primary Key index on that table
     
     

   alter table products
     
     

   add (constraint products_pk primary key (prod_id));
     
     

(c) Query v$object_usage: the monitoring has not started yet
     
     

   column index_name format a12
     
     

   column monitoring format a10
     
     

   column used format a4
     
     

   column start_monitoring format a19
     
     

   column end_monitoring format a19
     
     

   select index_name,monitoring,used,start_monitoring,end_monitoring
     
     

   from v$object_usage;
     
     

   no rows selected
     
     

(d) Start monitoring of the index usage
     
     

   alter index products_pk monitoring usage;
     
     

   Index altered.
     
     

(e) Query v$object_usage to see the monitoring in progress
     
     

   select index_name,monitoring,used,start_monitoring,end_monitoring
     
     

   from v$object_usage;
     
     

   INDEX_NAME   MONITORING USED START_MONITORING    END_MONITORING
     
     

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

   PRODUCTS_PK  YES        NO   04/25/2001 15:43:13
     
     

Note: Column MONITORING='YES', START_MONITORING gives the timestamp.
     
     

(f) Issue the SELECT statement which uses the index
     
     

First, make sure that index will be used for this statement.
     
     

Create plan_table in your schema, as required by Oracle Autotrace
     
     

utility:
     
     

   @$ORACLE_HOME/rdbms/admin/utlxplan
     
     

   Table created.
     
     

Use Oracle Autotrace utility to obtain the execution plan:
     
     

   set autotrace on explain
     
     

   select * from products where prod_id = 2; 
     
     

    .
     
     

    . 
     
     

    Execution Plan
     
     

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

     0     SELECT STATEMENT Optimizer=CHOOSE
     
     

     1  0  TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS'
     
     

     2  1    INDEX (UNIQUE SCAN) OF 'PRODUCTS_PK' (UNIQUE)
     
     

   set autotrace off
     
     

Now, since you know the index will be used for this query, issue the
     
     

actual SELECT statement:
     
     

   select * from products where prod_id = 2;
     
     

      PROD_ID PROD_
     
     

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

            2 bbbbb
     
     

(g) Query v$object_usage again to see that the index has been used
     
     

   select index_name,monitoring,used,start_monitoring,end_monitoring
     
     

   from v$object_usage;
     
     

   INDEX_NAME   MONITORING USED START_MONITORING    END_MONITORING
     
     

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

   PRODUCTS_PK  YES        YES  04/25/2001 15:43:13
     
     

Note: Column USED='YES'.
     
     

(h) Stop monitoring of the index usage
     
     

   alter index products_pk nomonitoring usage;
     
     

   Index altered.
     
     

(i) Query v$object_usage to see that the monitoring stopped
     
     

   select index_name,monitoring,used,start_monitoring,end_monitoring
     
     

   from v$object_usage;
     
     

   INDEX_NAME   MONITORING USED START_MONITORING    END_MONITORING
     
     

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

   PRODUCTS_PK  NO         YES  04/25/2001 15:43:13 04/25/2001 15:48:44
     
     

Note: Column MONITORING='NO', END_MONITORING gives the timestamp.
     
     

PART 2  -  How to identify all unused indexes in the database
     
     

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

To identify all unused indexes in the database, you can do the
     
     

following:
     
     

(a) Create a SQL script to start monitoring all indexes except those
     
     

    owned by users SYS and SYSTEM
     
     

(b) Create another script to stop monitoring all indexes except those
     
     

    owned by users SYS and SYSTEM
     
     

(c) Connect as a user with ALTER ANY INDEX system privilege and run
     
     

    the start monitoring script
     
     

(d) Perform normal activities in your database
     
     

(e) After a period of time that is representative of your workload,
     
     

    run the stop monitoring script
     
     

(f) Query v$object_usage to see what indexes have not been used
     
     

Detailed steps:
     
     

(a) Create a SQL script to start monitoring all indexes except those
     
     

    owned by users SYS and SYSTEM
     
     

   set heading off
     
     

   set echo off
     
     

   set feedback off
     
     

   set pages 10000
     
     

   spool startmonitor.sql
     
     

   select 'alter index '||owner||'.'||index_name||' monitoring usage;'
     
     

   from dba_indexes
     
     

   where owner not in ('SYS','SYSTEM');
     
     

   spool off
     
     

(b) Create another script to stop monitoring all indexes except those
     
     

    owned by users SYS and SYSTEM
     
     

   set heading off
     
     

   set echo off
     
     

   set feedback off
     
     

   set pages 10000
     
     

   spool stopmonitor.sql
     
     

   select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
     
     

   from dba_indexes
     
     

   where owner not in ('SYS','SYSTEM');
     
     

   spool off
     
     

(c) Connect as a user with ALTER ANY INDEX system privilege and run
     
     

    the newly created script to start monitoring.
     
     

   @startmonitor
     
     

(d) Perform normal activities in your database
     
     

(e) After a period of time that is representative of your workload,
     
     

    connect as a user with ALTER ANY INDEX system privilege and run
     
     

    the script to stop monitoring.
     
     

   @stopmonitor
     
     

(f) Query v$object_usage in join with dba_indexes, to see what indexes
     
     

    have not been used
     
     

   select d.owner, v.index_name
     
     

   from dba_indexes d, v$object_usage v
     
     

   where v.used='NO' and d.index_name=v.index_name;
     
     

RELATED DOCUMENTS
     
     

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

Note 1033478.6 Script Monitoring the Usage of Indexes(prior Oracle9i)
    
    

Note 1015945.102 How to Monitor Most Recently Accessed Indexessing script
    
    

Oracle9i Database Administrator's Guide
    
    

Viewing All Indexes Being Monitored Under Another User's Schema

 

PURPOSE
    
    

   To show how to view all indexes being monitored. 
    
    

SCOPE & APPLICATION
    
    

   Instructional.
    
    

Viewing All Indexes Being Monitored Under Another User's Schema:
    
    

================================================================
    
    

V$OBJECT_USAGE does not display rows for all indexes in the database whose 
    
    

usage is being monitored.
    
    

'ALTER INDEX <index> MONITORING USAGE' places an entry in V$OBJECT_USAGE for 
    
    

that particular index to help determine if the index is being used or not. The 
    
    

V$OBJECT_USAGE view uses the username logged into database when the 'ALTER 
    
    

INDEX <index> MONITORING USAGE' is issued. This will not enable any user other
    
    

than the user who issued the 'ALTER INDEX <index> MONITORING USAGE' to view if 
    
    

index is being monitored or not.
    
    

The view structure may be changed slightly (see below) in order to expand its 
    
    

scope system-wide (see below) so that you may see all indexes being monitored.
    
    

For example:
    
    

Showing User Scott monitoring his Index on EMP table:
    
    

    SQL>  connect scott/tiger 
    
    

    SQL> set LONG 30000
    
    

    SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';
    
    

    TEXT
    
    

    select   io.name, t.name,
    
    

             decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
    
    

             decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
    
    

             ou.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#
    
    

    SQL> select index_name, table_name, uniqueness, status from user_indexes 
    
    

         where table_name = 'EMP';
    
    

    INDEX_NAME   TABLE_NAME     UNIQUENES STATUS
    
    

    PK_EMP              EMP     UNIQUE    VALID
    
    

    SQL> alter index PK_EMP monitoring usage;
    
    

    Index altered.
    
    

    SQL> select * from v$object_usage;
    
    

    INDEX_NAME   TABLE_NAME    MONITORING USED START_MONITORING  END_MONITORING
    
    

    PK_EMP              EMP    YES        NO   10/12/2001 06:42:35
    
    

Then connect as another user to view indexes being monitored:
    
    

    SQL> connect / as sysdba;
    
    

    Connected.
    
    

    SQL> select * from v$object_usage;
    
    

    no rows selected
    
    

To be able to view them do the following:
    
    

    SQL> create or replace view V$ALL_OBJECT_USAGE 
    
    

         (OWNER,
    
    

         INDEX_NAME, 
    
    

         TABLE_NAME, 
    
    

         MONITORING, 
    
    

         USED, 
    
    

         START_MONITORING, 
    
    

         END_MONITORING) 
    
    

         as 
    
    

           select u.name, io.name, t.name, 
    
    

           decode(bitand(i.flags, 65536), 0, 'NO', 'YES'), 
    
    

           decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), 
    
    

           ou.start_monitoring, 
    
    

           ou.end_monitoring 
    
    

         from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou 
    
    

         where i.obj# = ou.obj# 
    
    

         and io.obj# = ou.obj# 
    
    

         and t.obj# = i.bo#
    
    

         and u.user# = io.owner#
    
    

    View created.
    
    

    SQL> select * from v$all_object_usage;
    
    

    OWNER INDEX_NAME      TABLE_NAME     MON  USE   START_MONITORING   END_MONITORING
    
    

    SCOTT    PK_EMP          EMP            YES  NO    10/12/2001 06:42:35
    
    

Related Documents:
    
    

==================
    
    

Oracle9i Database Administrator's Guide Volume 1 (Managing Indexes: Monitoring 
    
    

  Index Usage)
    
    

Note 144070.1  Identifying unused indexes in Oracle9i