Thursday, December 20, 2007

Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command

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

9 comments:

Anonymous said...

Hello all
http://www.loveinactioninc.com/ - vardenafil 20mg
Patients who have used this tablet confessed to having a better erection and therefore an improved sex life.
[url=http://www.loveinactioninc.com/]levitra cost[/url]
Levitra is sublingually taken and must be strictly taken with a prescription from your doctor.
levitra cost

For men, the inability to have an erection is indeed a big disappointment not just for them but to both partners.

Anonymous said...

Hi,
http://www.hammondneighbors.com/ - generic klonopin
Through the use of therapy, both medicinal and social, one can formulate a plan to rid their mind of anxiety once and for all.
[url=http://www.hammondneighbors.com/]buy clonazepam[/url]
Klonopin has been around for nearly two decades now, and has already helped millions of individual anxiety sufferers worldwide deal with their problem.
generic clonazepam
Klonopin is meant to be a short term anxiety solution(2-4 months), as continued use can lead to dependency.

Anonymous said...

Amiable brief and this fill someone in on helped me alot in my college assignement. Thanks you on your information.

Anonymous said...

Sorry for my bad english. Thank you so much for your good post. Your post helped me in my college assignment, If you can provide me more details please email me.

viagra said...

Wow, nice post,there are many person searching about that now they will find enough resources by your post

Anonymous said...

gt cialisukcialissoftyrf.com , cialis cost , free cialis , free viagra , viagraprescriptioncheapestviagravut.com , viagra reviews

Anonymous said...

Payday Loans Online http://www.legitpaydayloansonline3.com Fundpopog [url=http://www.legitpaydayloansonline1.com]Payday Loans Online[/url] Stype Payday Loans fast payday loans online They value giving you excellent customer service since other loans you are not high interest rates in the future.The check cashing service is so quick and simple that as that there to begin to repair loan in a nature.

Anonymous said...

teds woodworking , http://woodworkingplans1.com/#LiCsNeisp ted woodworking

Anonymous said...


viagra[url=http://www.charlestownehotels.com/images/lib/buyviagra/] buy viagra [/url]http://www.charlestownehotels.com/images/lib/buyviagra/