| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: HOW TO KNOW INDEX NOT IN USE?
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.
dwilliams_at_lifetouch.com
-----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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
INET: oracledbam_at_hotmail.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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). Received on Mon Feb 04 2002 - 14:32:59 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |