RE: ** find whether table or index being accessed

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-----
Sent: Tuesday, November 18, 2003 10:34 PM To: Multiple recipients of list ORACLE-L

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! <http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com> SiteBuilder - Free, easy-to-use web site design software

Do you Yahoo!?
Protect your identity with <http://antispam.yahoo.com/whatsnewfree> Yahoo! Mail AddressGuard

Do you Yahoo!?
Protect <http://antispam.yahoo.com/whatsnewfree> your identity with Yahoo! Mail AddressGuard

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joze Senegacnik
  INET: JozeS_at_hermes-plus.si

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).