Re: Q: Identify indexes which are no longer used ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 2 Jul 1999 12:05:23 +0100
Message-ID: <930914194.17032.0.nnrp-09.9e984b29@news.demon.co.uk>

That's given me a cute idea for abusing the system:

If you have Oracle 8, and aren't yet using the 3 buffer pools, allocate some space to the keep pool, and allocate a suspect index to the keep pool.

If you then do

select set_ds,count(*) from x$bh group by set_ds at the end of each day, you can see if ANY blocks get into the keep pool - if so they have to be your index, if not then your index is not being used.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Jurij Modic wrote in message <37819916.15989712_at_news.siol.net>...
>On Thu, 01 Jul 1999 21:05:43 GMT, tedchyn_at_yahoo.com wrote:
>
>>Sir, is there a way identifying obsolete indexes which are no longer
>>used ?
>
>Beside the method suggested by Jonathan Lewis, another way to find
>this would be to periodically check the SYS.V$BH view for any block of
>the suspected index being in the buffer cache. Something like:
>
Received on Fri Jul 02 1999 - 06:05:23 CDT