Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: best way to identify an unused index

RE: best way to identify an unused index

From: Justin Cave (DDBC) <jcave_at_ddbcinc.com>
Date: Thu, 12 May 2005 12:25:32 -0600
Message-ID: <87E9F113CEF1D211A4C300902730187475961A@ddbcinc.ddbc.local>


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=3D20
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 usage
  2 /

Index altered.

ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> select=3D20 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



Teach CanIt if this mail (ID 32179039) is spam: Spam:
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3D3Ds&i=3D3D32179039&m=3D= 3Dccc
5=3D
c6629
316
Not spam:
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3D3Dn&i=3D3D32179039&m=3D= 3Dccc
5=3D
c6629
316
Forget vote:
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3D3Df&i=3D3D32179039&m=3D= 3Dccc
5=3D
c6629
316

END-ANTISPAM-VOTING-LINKS
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Thu May 12 2005 - 14:24:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US