Re: ** find whether table or index being accessed

[Tanel Poder]

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.   ----- 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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