Seema - I'm not sure that I understand your question. Do you mean: Which
indexes aren't being used by the application?
Assuming that is your question, well it's tough. If your application
offers some support, that might narrow things down.
If your application is simple, you could inspect all SQL the
application and take the SQL that will be executed and then narrow it down
to the SQL that might use that table/index.
Another approach might be to sample the SQL in the buffer, run it
through EXPLAIN PLAN and search for this index. Don Burleson's book "High
Performance Tuning with Oracle STATSPACK" offers some scripts along this
line.
Oracle's AUDIT mechanism may be able to track index usage.
In the best case, you are overly concerned that there is one or two
extra indexes on your schema. In that case, take a chill pill and go find
something more important to worry about.
In the worst case, you've inherited a database where the developer
went crazy and created dozens of indexes on each table. In that case, you
have my sympathy. Make careful note of any indexes you drop so when some
obscure program that is only executed once a month suddenly has terrible
performance, you can quickly recreate the index you shouldn't have dropped.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Monday, February 04, 2002 11:55 AM
To: Multiple recipients of list ORACLE-L
Hi
I there any view which can tell us which indexes are not in use?
Thx
-Seema
_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
