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 YouA 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
