Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted August 31, 2015

Is My Oracle Index Being Used?

By David Fitzjarrell

Occasionally forum topics recur and one of these that has reared its head again regards finding 'unused' indexes. Of course how one defines 'unused' has an impact on what you look for. So, how should 'unused' be defined? Let's look at that question and see what direction that discussion takes.

The commonly offered idea of 'unused' involves searching execution plans for the existence of the index in question. That, however, covers only one of several conditions that can indicate that an index is 'used'. Certainly if an execution plan contains the index in question that index is used. Additionally a continual search of execution plans over a period of time can produce a count of how many times that index appears. 'Used' indexes aren't always found in execution plans, and that complicates the issue.

Oracle provides another avenue to see if an index is used (again, using the execution plan strategy) by allowing an index to be monitored for usage:


alter index <index_name> monitoring usage;

The V$OBJECT_USAGE view records the 'usage' information in a column named USED. If Oracle finds the index in question was actually mentioned in an execution plan the column data reflects that. It doesn't say how often it's used, if or when it was used, just that Oracle used it. Monitoring can be stopped at any time by issuing the following command:


alter index <index_name> nomonitoring usage;

The monitoring data remains in V$OBJECT_USAGE until the index is again altered for monitoring. The following example, using the ubiquitous EMP table, has been used elsewhere but it's a good demo of how index usage monitoring works. It begins by creating an index on EMP:


SQL>
SQL> --
SQL> -- Create an index on the EMPNO column
SQL> -- of the EMP table
SQL> --
SQL> create index emp_eno_idx
  2  on emp(empno);

Index created.

SQL>
SQL> --
SQL> -- Let's monitor the index to see if
SQL> -- it's being used
SQL> --
SQL> alter index emp_eno_idx monitoring usage;

Index altered.

SQL>
SQL> --
SQL> -- Now, let's run some queries
SQL> --
SQL> -- First, let's get everything from the
SQL> -- EMP table
SQL> --
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL> --
SQL> -- Obviously the index hasn't yet been
SQL> -- used
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            NO

1 row selected.

SQL>
SQL> --
SQL> -- So let's run a qualified query and
SQL> -- see if things change
SQL> --
SQL> -- Since the DEPTNO column isn't indexed
SQL> -- the monitored index still shouldn't be
SQL> -- used
SQL> --
SQL> select * from emp where deptno = 30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.

SQL>
SQL> --
SQL> -- And we see it isn't
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            NO

1 row selected.

SQL>
SQL> --
SQL> -- Yet another qualified query, this time
SQL> -- using the indexed column
SQL> --
SQL> select * from emp where empno = 7369;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL>
SQL> --
SQL> -- We see the index is now being used, or at
SQL> -- least it was for that last query
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL>
SQL> --
SQL> -- We'll try another query using that column
SQL> --
SQL> -- Let's set autotrace on to see if the index
SQL> -- is being used in this example
SQL> --
SQL> set autotrace on
SQL> select * From emp where empno is null;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3712041407

---------------------------------------------------------------------------
 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time 
---------------------------------------------------------------------------
   0  SELECT STATEMENT              1     87      0   (0)      
*  1   FILTER                                                  
   2    TABLE ACCESS FULL EMP      14   1218      3   (0) 00:00:01
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        353  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> set autotrace off
SQL>
SQL> --
SQL> -- Since the index has been marked as used
SQL> -- it remains in the USED state for this
SQL> -- monitoring window even though the last
SQL> -- query didn't use the index at all
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL>
SQL> --
SQL> -- Turn off the usage monitoring
SQL> --
SQL> alter index emp_eno_idx nomonitoring usage;

Index altered.

SQL>
SQL> --
SQL> -- And the last generated data remains
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL> 

Unfortunately Oracle can use an index even though it will not be found in any execution plan. Finding such 'usage' requires using a 10053 trace to dump the optimizer decision 'tree'. Why would Oracle do such a thing, use an index but not put it in a plan? There can be times when the optimizer calculates selectivities and the statistics for an index containing the columns in question can be used to calculate those values. Once calculated the index may not be used in the execution plan as a more favorable index is selected, but it's important to know that even though it's not found in any execution plan the index in question HAS been used, if indirectly. Index usage monitoring won't reveal this type of usage as it relies on the 'is it in a plan?' strategy. In such cases dropping the index can drastically alter execution plans for the worse since the optimizer can no longer eliminate execution paths because the relevant statistics are no longer available.

It has been argued that setting an index to UNUSABLE would provide the same conditions as dropping it, however disabling an index doesn't remove the statistics generated on that index. If the optimizer is using those statistics but it is not accessing the index the same conditions don't exist possibly leading the DBA into a false sense of security. Eventually the statistics will be marked as STALE and will no longer be used but that takes time and creates a situation where, down the road, the plan actually changes and the end users will complain about performance. At that point there isn't much that can be done except to recreate the missing index.

Oracle 11.2 and later releases allow setting the index in question to INVISIBLE, which prevents the optimizer from using the index and the associated statistics without dropping it. An example illustrating this (again, that has been used elsewhere) follows:


SQL> create index emp_empno_idx
  2  on emp(empno)
  3  invisible;

Index created.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3956160932                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
          5  recursive calls                                                                                                        
          0  db block gets                                                                                                          
         16  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
       1021  bytes sent via SQL*Net to client                                                                                       
        520  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          1  rows processed                                                                                                         

SQL> 
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,executions,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          1             2              1                                                                             
              1          1             1              1                                                                             

SQL> set autotrace on
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 576302360                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------                                       
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                       
---------------------------------------------------------------------------------------------                                       
|   0 | SELECT STATEMENT            |               |     1 |    87 |     2   (0)| 00:00:01 |                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    87 |     2   (0)| 00:00:01 |                                       
|*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IDX |     1 |       |     1   (0)| 00:00:01 |                                       
---------------------------------------------------------------------------------------------                                       
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
          9  recursive calls                                                                                                        
          0  db block gets                                                                                                          
         12  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
       1025  bytes sent via SQL*Net to client                                                                                       
        520  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          1  rows processed                                                                                                         

SQL> 
SQL> alter session set optimizer_use_invisible_indexes=false;

Session altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          1             2              1                                                                             
              1          1             1              1                                                                             

SQL> set autotrace on 
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3956160932                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
          8  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
       1021  bytes sent via SQL*Net to client                                                                                       
        520  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          1  rows processed                                                                                                         

SQL> 
SQL> alter index emp_empno_idx visible;

Index altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          2             3              2                                                                             
              1          1             2              1                                                                             


SQL> set autotrace on
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 576302360                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------                                       
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                       
---------------------------------------------------------------------------------------------                                       
|   0 | SELECT STATEMENT            |               |     1 |    87 |     2   (0)| 00:00:01 |                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    87 |     2   (0)| 00:00:01 |                                       
|*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IDX |     1 |       |     1   (0)| 00:00:01 |                                       
---------------------------------------------------------------------------------------------                                       
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
         42  recursive calls                                                                                                        
          0  db block gets                                                                                                          
         44  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
       1025  bytes sent via SQL*Net to client                                                                                       
        520  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          5  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          1  rows processed                                                                                                         

SQL> 

The setting optimizer_use_invisible_indexes can be set at the session level rather than at the system level making the index visible only to the currently connected session rather than to every user who has access to the objects in the affected schema. Setting the index to INVISIBLE provides a way to assess whether that index is truly unused and, as a result, whether the index can be safely dropped.

What if there is an index that isn't being used, but it SHOULD be? This can occur when the optimizer is using another index it thinks is more favorable due to the statistics but it really isn't. Setting the currently used index to INVISIBLE (again, in 11.2 and later releases) could improve performance by making the previously unused index the 'index of choice'. This is where hinting a query MAY help if the used index cannot be made invisible. This also reveals a situation where an index IS being used but another index would be better for performance. If bypassing that index causes the optimizer to use the formerly unused index then all is good, but it may be that after dropping the used index the optimizer STILL isn't using the desired index. Avenues are available to 'correct' that, such as outlines and SQL plan stability so don't be surprised if the optimizer needs a bit of assistance to get to the intended execution path.

Some indexes exist simply to circumvent the 'foreign key locking' issue, where the entire child table is locked during inserts, updates and deletes. Such indexes also won't be considered 'used' by Oracle, but dropping them most likely would introduce less than stellar performance and issues in multi-user systems.

Finding unused indexes isn't an easy task, and some 'unused' indexes are meant to be 'unused'. Exhaustive testing should be performed when evaluating whether or not to drop an index; remember that testing takes less time than recreating an index, especially on large tables commonly found in current databases that can reach terabyte or multi-terabyte sizes.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM