RE: best way to identify an unused index
- From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
- To: <Paula_Stankus@xxxxxxxxxxxxxxx>, <arivenes@xxxxxxxx>, <dba.orcl@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 May 2005 12:25:32 -0600
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@xxxxxxxxxxx>
Distributed Database Consulting, Inc.
http://www.ddbcinc.com
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Paula_Stankus@xxxxxxxxxxxxxxx
Sent: Thursday, May 12, 2005 2:03 PM
To: Paula_Stankus@xxxxxxxxxxxxxxx; arivenes@xxxxxxxx;
dba.orcl@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxx>
ops$tkyte@xxxxxxxxxxxxxxxxxxxx>
ops$tkyte@xxxxxxxxxxxxxxxxxxxx> alter index t_pk monitoring usage
2 /
Index altered.
ops$tkyte@xxxxxxxxxxxxxxxxxxxx>
ops$tkyte@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxx>
ops$tkyte@xxxxxxxxxxxxxxxxxxxx> set echo off
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stankus, Paula G
Sent: Thursday, May 12, 2005 1:58 PM
To: arivenes@xxxxxxxx; dba.orcl@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE:best way to identify an unused index
In Oracle 9i what is the best way to identify an unused index?
--
//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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: