Finding Indexes that are not being used

From: Gait, Christopher <cgait_at_condor.nrl.navy.mil>
Date: Tue, 4 Jul 2000 00:35:43 -0400
Message-Id: <10547.111146@fatcity.com>

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.

   /*
   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, 
	 Index_Name
FROM
	 DBA_Indexes
WHERE
	 Index_Name

IN
( SELECT
     Object_Name
 FROM
         DBA_Objects
 WHERE

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

MINUS
 SELECT
  DISTINCT
         Object_Name
 FROM

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

Received on Mon Jul 03 2000 - 23:35:43 CDT