I think there's another complication in using buffer cache (x$bh or v$bh). If
the index is scanned in parallel, the blocks are not cached in buffer cache.
To avoid counting buffers created due to index update, maybe we can simply say
where v$bh.status in ('CR','READ').
Yong Huang
- Tanel Poder <tanel.poder.003_at_mail.ee> wrote:
> Jacques provided some great suggestions already (about monitoring index
> tablespace read/write ratio), so monitoring buffer cache should be done as a
> last resort IMHO. Monitoring only for existence of index root block in cache
> wouldn't be good enough anyway, I'd check for touch count and last touch time
> too (TCH and TIM columns in x$bh), but again, as Joze pointed out nicely,
> these blocks can get to cache because of updates... So no luck in tracking
> indexes from there :)
>
> Tanel.
>
> ----- Original Message -----
> From: Daniel Fink
> To: Multiple recipients of list ORACLE-L
> Sent: Tuesday, November 18, 2003 11:34 PM
> 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!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: yong321_at_yahoo.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).
Received on Tue Nov 18 2003 - 17:34:33 CST
