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

Still you have a problem of once-in-period ( once a week/month/quater/year ) usage of that index.

  Michael.

In article <930914194.17032.0.nnrp-09.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> 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:
> >
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sat Jul 03 1999 - 03:58:29 CDT