| 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?
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.
>
>
>
 |
 |