| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: What Indexes are being used?
James Lorenzen wrote:
>
> 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.
I use the FLAGS and CHANGES columns to check whether the buffer entry came from a straight read. If they are both 0 then the index was read, if not, then typically a write to that block has occurred.
I can't categorically claim that this is correct - its just what I've observed and hence use.
HTH
--
We are born naked, wet and hungry...then things get worse Received on Wed Mar 29 2000 - 04:39:03 CST
 |
 |