RE: Finding Indexes that are not being used

From: Veronica Levin <vlevin_at_victoria.com.ni>
Date: Wed, 25 Oct 2000 09:16:01 -0600
Message-Id: <10660.120174@fatcity.com>

Morning listers!
I was wondering if there is a way or an sql script to find out how = many and
wich indexes have not been used for a period of time...=20 The amount of indexes in my production database is almost three times = data
(I know it makes no sense) and I would like to prove that most of those
indexes are not being used and should be droped. If anyone could help me I would appreciate. Thanks in advance!

Saludos,

Ver=F3nica Levin Enr=EDquez
Administrador AIX
Compa=F1=EDa Cervecera de Nicaragua
Tel=E9fono: 505-2493779
Fax: 505-2443979
Email: vlevin_at_victoria.com.ni=20
http://www.victoria.com.ni

-----Mensaje original-----
De: Gait, Christopher [mailto:cgait_at_condor.nrl.navy.mil] Enviado el: Lunes 3 de Julio de 2000 11:44 PM Para: Multiple recipients of list ORACLE-L Asunto: Finding Indexes that are not being used

Inspired by a message about using X$BH and DBA_Objects to track down = indexes
being used which I read today on the list (and now can't find), I wrote = up a
script for TOAD that does a little more than that, adding the = associated
table_name and using V$BH instead of X$BH. Does this script make sense? = It
seems to produce a reasonable list of indexes which have not been used = since
instance startup (am I right that V$BH is initialized at startup like = the
other V$ views?) Can someone look this over and see if it passes a = sanity
check?

With a few very slight variations (basically chopping off the outer = query)
this same script works for tables and other objects.=20

   /*=20
   Query to find indexes not being used in the DB.    Can be run regularly in a job and results loaded into a table to = track
usage patterns.

   Use v_Exclude_1 and _2 to remove items by naming convention (for = instance
all %_PK and %FK_I indexes).

   Chris Gait, Arlington, VA, July 3, 2000    */
SELECT DISTINCT

	 Table_Name,=20
	 Index_Name
FROM
	 DBA_Indexes
WHERE
	 Index_Name

IN
( SELECT=20

     Object_Name
 FROM
         DBA_Objects
 WHERE

 	Owner =3D UPPER(:v_Schema)
 	AND
 	Object_Type =3D UPPER(:v_Object_Type)
 	AND
 	Object_Name NOT LIKE UPPER(:v_Exclude_1)
 	AND
 	Object_Name NOT LIKE UPPER(:v_Exclude_2) =20

MINUS
 SELECT=20
  DISTINCT
         Object_Name
 FROM=20

     V$BH b,
     DBA_Objects o
 WHERE=20
     b.Objd =3D o.Object_ID
     AND
     Owner =3D UPPER(:v_Schema)
     AND
     Object_Type =3D UPPER(:v_Object_Type)
     AND
     Object_Name NOT LIKE UPPER(:v_Exclude_1)
     AND
     Object_Name NOT LIKE UPPER(:v_Exclude_2))

--=20
Author: Gait, Christopher
  INET: cgait_at_condor.nrl.navy.mil

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
Received on Wed Oct 25 2000 - 10:16:01 CDT