Oracle-L: RE: ** find whether table or index being accessed

From: <Jared.Still_at_radisys.com>
Date: Tue, 18 Nov 2003 15:14:25 -0800
Message-ID: <F001.005D714D.20031118151425@fatcity.com>

You know, I remember reading the stored outlines trick, but I completely forgot about it.

Nice catch Jacques.

"Jacques Kilchoer" <Jacques.Kilchoer_at_quest.com> Sent by: ml-errors_at_fatcity.com
11/18/2003 02:19 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE:  ** find whether table or index being accessed

I was too lazy to look for it on asktom.oracle.com, but here's what I read at the site a while ago (if you search on index usage or something like that you should find Mr. Kyte's answer). Tom Kyte has the following suggestions:
a) In Oracle 8.0 and earlier - put an index all by itself in a tablespace, and check reads and writes on the tablespace. If reads are close to writes - index not being used (only read for updates.) If reads much larger than writes - indexes being used.
b) In Oracle 9.0 and later - use alter index ... monitoring and check v$object_usage
c) In Oracle 8.1 (your case): See Chapter 11 of his book "expert one-on-one Oracle" - use stored outlines. Use an ON LOGON trigger to enable automatic outline generation (and disable it after a while) - look in user_outline_hints to see if the index is being used.

Finally, even though an index is used, that doesn't mean it's necessary. e.g. if you have
index IDX1 on MYTABLE (N1, N2)
and index IDX2 on MYTABLE (N1, N2, N3)
IDX1 may be used by some queries but might not be necessary because the query could use IDX2.

-----Original Message-----

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:

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

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------