Re: how to identify unused indexes

Jonathan Lewis wrote:
>
> The drawback to this is that an index will be 'used'
> if you insert a row into a table. In fact, given the
> general nature of indexes, you could find that a
> redundant index has more blocks pulled into the cache
> than a useful index if you insert a batch of records.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Mark D Powell wrote in message
> <1a710ddb.074bfaf3_at_usw-ex0102-015.remarq.com>...
> >Scott Dudley <scott_at_telesoft.com> wrote:
> >>
> >>is there a way to identify indexes that aren't accessed for
> >>select/update? trying to identify superfluous indexes in a
 database of
> >>some 300+ tables without having to go through hundreds of
 thousands of
> >>lines of code.
> >>
> >>thanks.
> >>
> >>Microsoft gives you Windows but Unix gives you the whole house!
> >>
> >I do not believe there is any built-in way of doing this. One
> >thing you might try is writing code that will periodically
> >sample the buffer pool for what objects are there. You can use
> >the v$cache view which normally exists only on parallel server
> >installs, but the script catparr can be ran on non-ops installs
> >to create this view. By storing all indexes found in a work
> >table with a unique key on the owner, index_name you should be
> >able to have just one entry per index so you do not need to
> >generate much data. I would use dbms_job to run the task.
> >
> >It is not perfect but this should allow you to identify most of
> >your used objects so it should allow you to narrow your list of
> >possible obsolete indexes way down.
> >
> >
> >
> >
> >
> >-- Mark D. Powell -- The only advice that counts is the advice that
> >you follow so follow your own advice. --
> >
> >-----------------------------------------------------------
> >
> >Got questions? Get answers over the phone at Keen.com.
> >Up to 100 minutes free!
> >http://www.keen.com
> >

I haven't done any exhaustive analysis but I remember using the flags in x$bh to determine whether an index blocks were dirty or not - it wasn't perfect but it gave more "confidence" in determining whether they got there from a read or a write.

CHeers

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse