| 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?
David Spaisman wrote:
>
> Connor:
>
> Does this apply to Oracle 8.0.4 as well? Would I need to read x$bh and join
> it to $obj?
>
> THanks.
>
> David
>
> Connor McDonald wrote:
>
> > 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
> > --
> > ===========================================
> > Connor McDonald
> > http://www.oracledba.co.uk
> >
> > We are born naked, wet and hungry...then things get worse
X$BH and OBJ$ are present in 7.x and 8.x
HTH
--
We are born naked, wet and hungry...then things get worse Received on Thu Mar 30 2000 - 07:21:36 CST
 |
 |