| 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?
I neither find a X% tables nor a X% view (in 8.i)
I searched in SYS and SYSTEM
In article <38E354E0.449D_at_yahoo.com>,
connor_mcdonald_at_yahoo.com wrote:
> 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
>
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Mar 31 2000 - 04:51:15 CST
 |
 |