Saturday, 22 March 2008

How to Monitor Oracle Index Usage

We can use the following command to determine whether a particular index is being used by Oracle.
ALTER INDEX <index_name> MONITORING USAGE;
If the specified index is used before monitoring is disabled, then the USED column of the relevant V$OBJECT_USAGE row, is set to YES.

Let's see this in action.

First, we create a table using the CONNECT BY LEVEL clause to generate a thousand rows, with an index on the ID column.
CREATE TABLE index_usage AS
SELECT rownum id, 'xyz' || rownum name
FROM dual
CONNECT BY LEVEL <= 1000;
Table created.

CREATE INDEX index_usage_ix1 ON INDEX_USAGE (id);
Index created.
Then we start monitoring the usage of the index and display the results from the V$OBJECT_USAGE view.
ALTER INDEX index_usage_ix1 MONITORING USAGE;
Index altered.

SELECT monitoring, used, start_monitoring, end_monitoring
FROM v$object_usage
WHERE index_name = 'INDEX_USAGE_IX1';
MONITORING USED START_MONITORING    END_MONITORING      
---------- ---- ------------------- -------------------
YES NO 03/22/2008 11:44:20
1 rows selected
Next, we access the table using the index, disable the monitoring and display the results again.
SELECT *
FROM index_usage
WHERE id = 123;
ID                     NAME                                        
---------------------- -------------------
123 xyz123
1 rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=10)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDEX_USAGE' (TABLE) (Cost=2 Card=1 Bytes=10)
2 1 INDEX (RANGE SCAN) OF 'INDEX_USAGE_IX1' (INDEX) (Cost=1 Card=1)

ALTER INDEX index_usage_ix1 NOMONITORING USAGE;
Index altered.

SELECT monitoring, used,start_monitoring, end_monitoring
FROM v$object_usage
WHERE index_name = 'INDEX_USAGE_IX1';
MONITORING USED START_MONITORING    END_MONITORING      
---------- ---- ------------------- -------------------
NO YES 03/22/2008 11:44:20 03/22/2008 11:47:35
1 rows selected
This shows us that the index was used during our monitoring period.

This feature can help us to identify unused indexes which are candidates for removal. Removing unnecessary indexes can improve performance by reducing overhead during updates.

2 comments:

mikael said...

hello,

Usefull article. Does someone know the database overhead of index monitoring ?

Mark Lishman said...

On Ask Tom, when asked if there is significant overhead when monitoring multiple indexes, Tom Kyte replies "not that I can measure, no".


Donald Burleson states the following on his web site.

"In sum, the index monitoring feature has very little overhead and it is quite useful for locating and dropping unneeded Oracle indexes."


There is also a discussion about this subject here