Re: What Indexes are being used?

Connor,
At a previous client, I had the same issue to deal with. The join does tell you about indexes in the buffer and if the index never appears in the buffer, it is not used. BUT, doesn’t the index appear ion the buffer if there are any changes in the fields that are indexed? IE, if the table is changed (update, insert or delete), isn’t the index updated to reflect the change and doesn’t it appear in the buffer cache? I went a ways down this path before I realized that the presence of the index in the buffer cache does not mean that it is used to support query access.

I was not able to find anything in the "X" tables (in 7.3.4) that seemed to help identify indexes used to support SQL queries.

James

In article <38DF5C45.623B_at_yahoo.com>,
connor_mcdonald_at_yahoo.com wrote:
> Bruce Mackay wrote:
> >
> > Hi everyone,
> >
> > I've inherited support of a system with many indexes, probably too
> > many indexes!

[ Snipped ]
> > Bruce Mackay, BMM Pty Ltd
>
> Join X$BH (the buffer cache) to OBJ$
>
> This will tell you what objects are in the buffer cache. Using this
> you can periodically determine what indexes are in the cache - any
index
> that never appears in the cache is probably a candidate for removal.
>
> Connor McDonald
> http://www.oracledba.co.uk

--
Life is complex; it has real and imaginary parts.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Mar 28 2000 - 09:17:30 CST