| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** find whether table or index being accessed
No, differently from table access, index access doesn't require reading of index segment header. The query reads the root block directly. Root block address is probably gotten from IND$ table columns file# and block#+1. The block# represents index segment header location in a datafile, root block is always next to index segment header block, even when root block split occurs.
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.
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: Tanel Poder
INET: tanel.poder.003_at_mail.ee
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:04:26 CST
 |
 |