Applies to ONLY Oracle Database 10gR1 through to 12cR1.
In Oracle Database 12cR2, this feature is replaced.  A new blog post of this feature will be posted in due course.

Since Oracle 10g, you can monitor indexes to see if they are being used or not.  Which is very useful as indexes, consume unnecessary CPU and I/O on DML activity if not used.  Therefore, it’s recommended to monitor indexes and any unused indexes can be dropped, not only freeing vital space but CPU and I/O, resulting in better throughput.

It recommended to enable index monitoring for an appropriate period that is representative of your full workload cycle.  For example if you run a report every week, month or even year, just to catch all those activities.

Please Note: It’s important that foreign key constraints have associated indexes to avoid any table level lock on DML.  These indirect uses of indexes will not be detected by index monitoring.  So be mindful when dropping indexes that are associated with foreign key constraint.

Enable Index Monitoring

To Enable Index Monitoring, you alter the index as shown below:

ALTER INDEX . MONITORING USAGE;

Checking Index Usage

To check if the index has been used, will depends on the version of Oracle:

Oracle 10g/11g

SELECT * FROM V$OBJECT_USAGE;

Database Reference 10g – V$OBJECT_USAGE
Database Reference 11g – V$OBJECT_USAGE

Please Note: Only shows the indexes belonging to the login user.

To see all indexes in the database regardless of the logged on user:

select 
do.owner,
t.name table_name, io.name index_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
ou.start_monitoring,
ou.end_monitoring 
from 
sys.obj$ io, 
sys.obj$ t, 
sys.ind$ i, 
sys.object_usage ou,
dba_objects do 
where i.obj# = ou.obj# 
and io.obj# = ou.obj# 
and t.obj# = i.bo#
and i.obj# = do.object_id
order by 1, 2, 3;

DISCLAIMER: As accessing data dictionary objects directly, this query is not guaranteed to work.

Oracle 12cR1

“The V$OBJECT_USAGE view is deprecated in Oracle Database 12c Release 1 (12.1) and maintained for backward compatibility. Support for this view may be removed in a future release. Oracle recommends that you use the USER_OBJECT_USAGE view instead of the V$OBJECT_USAGE view.”

Database Reference 12cR1 – V$OBJECT_USAGE

For the current logon user:

SELECT * FROM USER_OBJECT_USAGE;

Database Reference 12cR1 – USER_OBJECT_USAGE

For all users:

SELECT * FROM DBA_OBJECT_USAGE;

Database Reference 12cR1 – DBA_OBJECT_USAGE

Dis-enable Index Monitoring

To Dis-enable Index Monitoring, you alter the index as shown below:

ALTER INDEX . NOMONITORING USAGE;

Demo of Index Monitoring

The content of the index_monitoring_demo.sql is below:

-------------------------------------------------------------
--
-- Index Monitoring Demo
--
-- File Name: index_monitoring_demo.sql (blog.zeddba.com)
-- Created: Zahid Anwar (ZedDBA)
-- 
-- Date: 16/07/2018
-- Version: 1.0
--
-- History
--
--1.0 16/07/2018 ZA Initial Script
--
-------------------------------------------------------------
set echo on
set pages 999 lines 400

CREATE TABLE "EMP"
( "ID" NUMBER,
"FIRST_NAME" VARCHAR2(50),
"LAST_NAME" VARCHAR2(50)
);

pause Press Enter

INSERT INTO "EMP" VALUES (1, 'Zahid', 'Anwar');
INSERT INTO "EMP" VALUES (2, 'Scott', 'Tiger');
INSERT INTO "EMP" VALUES (3, 'Joe', 'Bloggs');

commit;

pause Press Enter

set autotrace on
col id format 99
col first_name format a50
col last_Name format a50

select * from emp;

pause Press Enter

create index emp_id on emp (id);

pause Press Enter

select * from emp where id = 1;

pause Press Enter

set autotrace off

col INDEX_NAME format a20
col TABLE_NAME format a20
col MONITORING format a15
col USED format a10
select * from USER_OBJECT_USAGE;

pause Press Enter

alter index emp_id monitoring usage;

pause Press Enter

select * from USER_OBJECT_USAGE;

pause Press Enter

set autotrace on

select * from emp;

pause Press Enter

set autotrace off

select * from USER_OBJECT_USAGE;

pause Press Enter

set autotrace on

select * from emp where id = 1;

pause Press Enter

set autotrace off

select * from USER_OBJECT_USAGE;

pause Press Enter

alter index emp_id nomonitoring usage;

pause Press Enter

select * from USER_OBJECT_USAGE;

pause Press Enter

DROP TABLE "EMP" PURGE;

Below is the output of the script index_monitoring_demo.sql:

SQL> @index_monitoring_demo.sql
SQL> set pages 999 lines 400
SQL>
SQL> CREATE TABLE "EMP"
2 ( "ID" NUMBER,
3 "FIRST_NAME" VARCHAR2(50),
4 "LAST_NAME" VARCHAR2(50)
5 );

Table created.

SQL>
SQL> pause Press Enter
Press Enter

Table EMP is created in my schema ZEDDBA, please ensure you have appropriate schema with correct privileges.  Next we insert 3 rows into the table EMP:

SQL> INSERT INTO "EMP" VALUES (1, 'Zahid', 'Anwar');

1 row created.

SQL> INSERT INTO "EMP" VALUES (2, 'Scott', 'Tiger');

1 row created.

SQL> INSERT INTO "EMP" VALUES (3, 'Joe', 'Bloggs');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> pause Press Enter
Press Enter

With the 3 rows inserted and committed, we select from the EMP table with auto trace on, so we can see the execution plan:

SQL> set autotrace on
SQL> col id format 99
SQL> col first_name format a50
SQL> col last_Name format a50
SQL>
SQL> select * from emp;

ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
2 Scott Tiger
3 Joe Bloggs


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| EMP | 3 | 201 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


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

SQL>
SQL> pause Press Enter
Press Enter

Please Note: This database is an Exadata Machine, hence the “TABLE ACCESS STORAGE FULL“, otherwise would be “TABLE ACCESS FULL“.

As we can see the Execution Plan was a Full Table Scan (FTS) as this was the only access method available.  Next, we create an index on the emp_id:

SQL> create index emp_id on emp (id);

Index created.

SQL>
SQL> pause Press Enter
Press Enter

Next, we query the table with a predicate on emp_id:

SQL> select * from emp where id = 1;

ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar


Execution Plan
----------------------------------------------------------
Plan hash value: 458854468

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access("ID"=1)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
552 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> pause Press Enter
Press Enter

Now, we can see the Execution Plan used the newly created index.  Next, we check if the index was used:

SQL> set autotrace off
SQL>
SQL> col INDEX_NAME format a20
SQL> col TABLE_NAME format a20
SQL> col MONITORING format a15
SQL> col USED format a10
SQL> select * from USER_OBJECT_USAGE;

no rows selected

SQL>
SQL> pause Press Enter
Press Enter

We get no rows back as we didn’t turn on index monitoring, which isn’t on by default.  So next, we enable index monitoring:

SQL> alter index emp_id monitoring usage;

Index altered.

SQL>
SQL> pause Press Enter
Press Enter

Next, we check the index usage and we can see it monitoring but not used:

SQL> select * from USER_OBJECT_USAGE;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID     EMP        YES        NO   07/16/2018 17:53:59

SQL>
SQL> pause Press Enter
Press Enter

Next, we query the table with no predicate to do a FTS:

SQL> set autotrace on
SQL>
SQL> select * from emp;

ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
2 Scott Tiger
3 Joe Bloggs


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| EMP | 3 | 201 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
51 recursive calls
0 db block gets
54 consistent gets
0 physical reads
132 redo size
784 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
3 rows processed

SQL>
SQL> pause Press Enter
Press Enter

Next, we check if the index monitoring reflects the usage:

SQL> set autotrace off
SQL>
SQL> select * from USER_OBJECT_USAGE;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID     EMP        YES        NO   07/16/2018 17:53:59

SQL>
SQL> pause Press Enter
Press Enter

Next, we query the table with a predicate to do Index Range Scan:

SQL> set autotrace on
SQL>
SQL> select * from emp where id = 1;

ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar


Execution Plan
----------------------------------------------------------
Plan hash value: 458854468

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access("ID"=1)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
13 recursive calls
3 db block gets
24 consistent gets
0 physical reads
876 redo size
695 bytes sent via SQL*Net to client
552 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> pause Press Enter
Press Enter

Next, we check if the index monitoring reflects the usage:

SQL> set autotrace off
SQL>
SQL> select * from USER_OBJECT_USAGE;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID     EMP        YES        YES  07/16/2018 17:53:59

SQL>
SQL> pause Press Enter
Press Enter

Next, we now turn off index monitoring, as we know it’s been used:

SQL> alter index emp_id nomonitoring usage;

Index altered.

SQL>
SQL> pause Press Enter
Press Enter

Next, we check to see if monitoring is now disabled:

SQL> select * from USER_OBJECT_USAGE;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID     EMP        NO         YES  07/16/2018 17:53:59 07/16/2018 17:54:09

SQL>
SQL> pause Press Enter
Press Enter

Finally, we drop the demo EMP table:

SQL> DROP TABLE "EMP" PURGE;

Table dropped.

SQL>

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)