RE : Finding Indexes that are not being used

Chris,

 I find your idea very interesting. You may find that this runs somewhat faster than your first draft :  

   select u.name "TABLE OWNER",
          oi.name "INDEX NAME",
          ot.name "TABLE NAME"
   from sys.obj$ oi,
        sys.ind$ i,
        sys.obj$ ot,
        sys.user$ u
   where ot.owner# = u.user#
     and oi.name not like upper('&exclude')
     and ot.obj# = i.bo#
     and oi.owner# != 0 -- forget SYS
     and oi.obj# = i.obj#
     and i.obj# in (select obj#
                    from sys.ind$
                    minus
                    select objd
                    from V$BH);

Now, on the principle itself ? I find it easier to reason on USED indexes, but it's almost the same thing. The question is how fast those buffers will age out and will make an unnoticed appearance in the SGA between your polls (or between startup and your execution of the query, since, yes, all V$ views are initialized when the instance starts up). Quite obviously, the better your hit-ratio the less often you have to poll. From my experience, stuff stays a long time in the SGA and I'd say that checking every half hour or hour may well be more than enough (especially has Oracle keeps indexes in memory longer than tables) but it probably has to be tested (if you carry out experiments I would be quite interested me if you could send me the results, together with a measure of activity such as the number of sessions).  

Another, and totally different way, to obtain the same result would be to collect all the SQL statements (V$SQL_TEXT), and EXPLAIN all of them. But it may be more difficult to put in place (parsing schema, optimizer goal, ... you must be careful about the context). Very good idea indeed. But beware of the once-a-quarter big batch program.

-- 
Regards,

  Stephane Faroult
  email: sfaroult_at_oriolecorp.com 
  Oriole Corporation
  Voice:  +44  (0) 7050-696-269 
  Fax:    +44  (0) 7050-696-449 
  Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------


> 

> 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)