| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: best way to identify an unused index
A little fast on the send trigger & short on the goggle search. Here's
a link to the article:
http://searchoracle.techtarget.com/tip/1,289483,sid41_gci970961,00.html=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Justin Cave (DDBC)
Sent: Thursday, May 12, 2005 2:26 PM
To: Paula_Stankus_at_doh.state.fl.us; arivenes_at_llnl.gov;
dba.orcl_at_gmail.com; oracle-l_at_freelists.org
Subject: RE: best way to identify an unused index
Howard Rogers has an excellent article on finding unused indexes
http://www.dizwell.com/html/useful_indexes.html
Enabling index monitoring, as Howard points out, invalidates any plans in the library cache that use the index, which will cause your system to potentially have to hard parse a number of statements. Index monitoring is also a bit crude in that it becomes difficult to identify indexes whose cost outweight their benefit-- say an index on a frequently inserted table that is used for one small, offline report query-- without repeatedly flipping the monitoring status, which increases the performance impact.
Justin Cave <jcave_at_ddbcinc.com>
Distributed Database Consulting, Inc.
http://www.ddbcinc.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
Paula_Stankus_at_doh.state.fl.us
Sent: Thursday, May 12, 2005 2:03 PM
To: Paula_Stankus_at_doh.state.fl.us; arivenes_at_llnl.gov;
dba.orcl_at_gmail.com; oracle-l_at_freelists.org
Subject: RE: best way to identify an unused index
OK in 9i much easier per Tom Kyte: Any performance hit on using this?
select=3D3D20
index_name,monitoring,used,start_monitoring,end_monitoring
2 from v$object_usage;
no rows selected
ops$tkyte_at_ORA920.US.ORACLE.COM> ops$tkyte_at_ORA920.US.ORACLE.COM> ops$tkyte_at_ORA920.US.ORACLE.COM> alter index t_pk monitoring usage2 /
Index altered.
ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> select=3D3D20
index_name,monitoring,used,start_monitoring,end_monitoring
2 from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
T_PK YES NO 05/26/2003 10:06:32
ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> set echo off
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stankus, Paula G
Sent: Thursday, May 12, 2005 1:58 PM
To: arivenes_at_llnl.gov; dba.orcl_at_gmail.com; oracle-l_at_freelists.org
Subject: RE:best way to identify an unused index
In Oracle 9i what is the best way to identify an unused index?
--
http://www.freelists.org/webpage/oracle-l
BEGIN-ANTISPAM-VOTING-LINKS
 |
 |