Re: ** find whether table or index being accessed

Joze,

Excellent Point! I knew there was something wrong about this, I just could not figure out what it was...

Daniel Fink

Joze Senegacnik wrote:

> Daniel,it will work but indexes are present in buffer cache
> also because of updates. The only possibility is to store each
> index in question in separate tablespace and monitor the i/o.
> If number of reads will be equal or little bit greater than
> the number of writes than this is a candidate. If number of
> reads is significantly bigger from number of writes then this
> index is used for speeding up the access. The answer is: you
> will never be 100% sure.Regards, Joze
>
> -----Original Message-----
> From: Daniel Fink [mailto:Daniel.Fink_at_Sun.COM]
> Sent: Tuesday, November 18, 2003 10:34 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: ** find whether table or index being
> accessed
>
> This is just an idea, so please test it thoroughly
> (and then test it again!) Any and all comments
> (including "Are you brain-dead, Dan?") are welcome.
>
> How about periodically sampling v$bh for index
> segment headers? This assumes that any index access
> reads the header (true/false?) for the statement
> using the index. I'd set the sample frequency fairly
> high (several times a day sounds reasonable) and
> monitor any impact. This will not show every index
> that is used, as one could be used and flushed from
> the cache between samples. However, I think it would
> be fairly likely to catch the ones really in use.
>
> Of course, under no circumstances remove indexes on
> primary keys, unique constraints or foreign keys,
> even if they don't show up.
>
> Daniel Fink
>
>
> A Joshi wrote:
>
> > Looking to see if any statement has accessed the
> > index in say 30 days. So basically : "how often
> > index blocks are being read". So I can decide to
> > drop unused indexes. TThanks Daniel for your help.
> >
> > Daniel Fink <Daniel.Fink_at_Sun.COM> wrote:
> >
> > Are you looking to see if statements are
> > using indexes or how often index blocks
> > are being read?
> >
> > Daniel Fink
> >
> > A Joshi wrote:
> >
> > > Hi, I had sent this some time back but
> > > got no answer for version 8.1.7. For
> > > table I understand auditing is an
> > > option. What about for index? Thank You
> > >
> > > A Joshi <ajoshi977_at_yahoo.com> wrote:
> > >
> > > Hi, Is there an easy way to
> > > find out if a table or an
> > > index is being used. I mean
> > > short of going thru all code
> > > or keeping looking at
> > > v$sqlarea. I mean even if
> > > code is covered there are
> > > always ad hoc SQL queries
> > > etc. Same for other objects
> > > like views etc. Is there a
> > > place where oracle stores
> > > objects accessed and any
> > > other related info. Thanks
> > > ------------------------------
> > > Do you Yahoo!?
> > > Yahoo! SiteBuilder - Free,
> > > easy-to-use web site design
> > > software
> > >
> > > ----------------------------------------
> > > Do you Yahoo!?
> > > Protect your identity with Yahoo! Mail
> > > AddressGuard
> >
> > ---------------------------------------------------
> > Do you Yahoo!?
> > Protect your identity with Yahoo! Mail
> > AddressGuard
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  INET: Daniel.Fink_at_Sun.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).