Murali If I'm not mistaken, the first Oracle version that offers real support for this is Oracle9i. I think what you are doing is about all that can be done -- capture SQL, run explain plan on it and make a list of the indexes that are used. If you look around, there are scripts to help and packages that automate this. But in the end you still have to answer the question of how long do you wait to see if an index isn't used before you drop it -- a day, a week, a month? There is always that danger that there will be a really critical report that is only run on irregular occasions that will need that index. For example, a manufacturing plant tends to go through a production season. The applications running at the first of the season are different from those run toward the end. If you pick a month at the first of the season, you may drop indexes needed toward the end. If you have a definite indication that your application is over-indexed, this effort may be worth your trouble.
Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Tuesday, April 01, 2003 10:59 AM To: Multiple recipients of list ORACLE-L How do you monitor if the indexes are being used in 8i? Individually the tkprof and explain plans do the trick. However from a running system, how can the index usage be determined? If the index is being used? How many times did the index get used during a specific time frame etc. Regards Menon _____ Do you Yahoo!? Yahoo! Tax <http://us.rd.yahoo.com/finance/mailsig/*http://tax.yahoo.com> Center - File online, calculators, forms, and more -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [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).
