Sunday, October 14, 2007

Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command

Subject: Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command
Doc ID: Note:136642.1 Type: BULLETIN
Last Revision Date: 02-JUL-2007 Status: PUBLISHED


Checked for relevance on 03-July-2007

PURPOSE
This document explains how to identify unused indexes in order to remove them.


SCOPE & APPLICATION
DBAs must be aware that unused indexes:
* consume storage space
* degrade performance by unnecessary overheads during DML operations

REFERENCES

Note 144070.1 Identifying unused indexes in Oracle9i


How to Identify Unused Indexes in Order to Remove Them:
=======================================================
The examples below work if the user logged is the owner of the index.

1. Set the index under MONITORING:

SQL> alter index I_EMP monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:17:19


2. Check if the monitored indexes are used or not through the USED column in
V$OBJECT_USAGE view:

SQL> select sal from emp where ename='SMITH';

SAL
----------
800


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'I_EMP' (NON-UNIQUE)


The explain plan indicates the index is used.


SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES YES 03/14/2001 09:17:19


If the index was not used, the DBA could drop this unused index.


3. To stop monitoring an index, use the following SQL statement:

SQL> alter index i_emp nomonitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP NO YES 03/14/2001 09:17:19 03/14/2001 09:55:24


As soon as you turn monitoring on again for the index, both columns
MONITORING and USED are reset.


SQL> alter index i_emp monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:57:27

5 comments:

Unknown said...

louis vuitton outlet stores
louis vuitton handbags
gucci outlet
ray ban sunglasses outlet
michael kors outlet
louis vuitton handbags
louis vuitton outlet
lebron james shoes 13
louis vuitton
coach factory outlet
kobe 9
coach factory outlet
cheap nfl jerseys
coach outlet
ralph lauren outlet
jordan 13
nike sb
christian louboutin shoes
cheap oakleys
coach outlet store online
toms shoes
louis vuitton handbags
jordan retro 3
hollister clothing
michael kors outlet
michael kors canada
asics running shoes
designer handbags
beats headphones
cheap jordan shoes
michael kors outlet online
christian louboutin outlet
oakley vault
oakley vault
louis vuitton bags
nike trainers
louis vuitton outlet
jordan shoes
nike air max
michael kors outlet
20166.4wengdongdong

raybanoutlet001 said...

ralph lauren outlet
ugg outlet
pandora jewelry
christian louboutin shoes
polo ralph lauren
michael kors outlet
valentino outlet
ugg boots
ugg outlet
ugg outlet

Unknown said...

longchamp bags
adidas gazelle sale
adidas stan smith uk
led light up shoes
adidas tubular UK
nmd
gucci belts for men
pandora jewelry
nike air max
air jordans

jeje said...

nike zoom
nike air force 1
foamposites
nike roshe run
jordan shoes
pandora bracelet
jordan shoes
nike air zoom
adidas stan smith
ultra boost

Anonymous said...

kobe shoes
golden goose
Jordan Travis Scott
palm angels outlet
off white x jordan 1
stephen curry shoes
goyard
jordan outlet
supreme clothing
off white outlet