Yes, you are right. I forgot to mention monitoring, On 2002.08.30 01:23 "Khedr, Waleed" wrote: > Or when you are done moving these suspected indexes to a new Tablespace, > you can monitor the read/write statistics for the files in that Tablespace > (v$filestat). > > Another idea is to put the indexes in the unusable state and wait for an > error when the index gets used. > > Regards, > > Waleed > > -----Original Message----- > To: Multiple recipients of list ORACLE-L > Sent: 8/30/02 12:53 AM > > Your methodology is sound and bulletproof, but is also fairly > effort-intensive and > time-consuming. Another trick is to make a list of all indexes that are > suspected for > not being used, move them all into the same, otherwise empty, tablespace > and take the > whole tablespace offline. If any of the existing jobs slow down > significantly, then > you have disabled a wrong index. It is a "gun slinger" method but if you > are not going > to do a significant business damage, you might be able to afford it. > The method that I use is to find documentation for the project that has > caused the index > to be created. Trace files and performance analysis must be attached to > the project documentation. > If the index was used at one time, I examine the programs that have been > using it and > run them with SQL_TRACE on (or 10046, level 1). If the index is no > longer used, I discard > it. I have retired quite a few tables and indexes since the start of all > that HIPAA frenzy. > Good documentation and a strict process of applying changes to the > production database, > with a sign-off required for every step along the way is of paramount > importance for keeping > track of objects in your database. > > > On 2002.08.29 17:37 Cary Millsap wrote: > > I expect that the answer is probably that an "unused index" is any > index > > that the Oracle query optimizer will never choose for any SQL > statement > > in the entire application. > > > > One way to compute whether an index is unused is: > > > > 1. Extract SQL from your application source code (which is nice > because > > you can do it all at once without polling) and from v$sql (which you > > must do if your apps generate SQL dynamically). > > > > 2. Compute the current* execution plan for each statement using > explain > > plan. (*The reason I say "current" is that many input factors can > change > > these plans, including schema changes, db or instance statistics > > changes, init.ora changes, and application code changes.) > > > > 3. For each index in your application, determine whether that index is > > referenced in any plan generated in step 2. Any index that is never > > referenced is almost certain to be "unused." > > > > We have built a tool under the name "Hotsos Project Laredo" > > (www.hotsos.com/products/laredo) that does steps 1 and 2. The > difficult > > trick is collecting *all* of your SQL. The method of just looking at > > object I/O statistics for a given index, for example, isn't reliable > > because it is only rearward-looking in time. Just because an index > > hasn't been used since instance startup doesn't mean that it won't be > > used tomorrow. > > > > Making Laredo do step 3 would probably not be nearly as difficult as > the > > work we've done perfecting steps 1 and 2. Let me know if you're > > interested. Improving our tools is part of how we make a living... > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 > > Honolulu > > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas > > - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark > > > > > > > > -----Original Message----- > > Mladen > > Sent: Thursday, August 29, 2002 3:39 PM > > To: Multiple recipients of list ORACLE-L > > > > What are "unused indexes"? > > > > > -----Original Message----- > > > From: Bob Robert [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, August 29, 2002 3:33 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: Identify unused indexes in 8i ? > > > > > > > > > All, > > > > > > Is there a way to identify unused indexes in Oracle > > > 8i. I know that it is easy to check with 9i. I would > > > appreciate if you could share your experience (or > > > ideas) with me. > > > > > > Thanks, > > > Bob > > > > > > > > > > > > __________________________________________________ > > > Do You Yahoo!? > > > Yahoo! Finance - Get real-time stock quotes > > > http://finance.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Bob Robert > > > 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: Gogala, Mladen > > 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: Cary Millsap > > 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). > > > > -- > Mladen Gogala > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mladen Gogala > 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: Khedr, Waleed > 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). >
-- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala 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).
