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.
