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-----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.
From: Daniel Fink [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 18, 2003 10:34 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: ** find whether table or index being accessed
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
