| 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?
effler_at_yahoo.com wrote:
>
> 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
> >
> > X$BH and OBJ$ are present in 7.x and 8.x
> >
> > 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.
You must be connected as SYS - it will not be sufficent to look for objects of that name.
--
We are born naked, wet and hungry...then things get worse Received on Fri Mar 31 2000 - 05:32:23 CST
 |
 |