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