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:mssql_2002_at_yahoo.com]
> > > 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: mssql_2002_at_yahoo.com
> > >
> > > 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: 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.com
> > --
> > Author: Gogala, Mladen
> > INET: MGogala_at_oxhp.com
> >
> > 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: 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.com
> > --
> > Author: Cary Millsap
> > INET: cary.millsap_at_hotsos.com
> >
> > 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: 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).
> >
>
> --
> Mladen Gogala
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.net
>
> 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: 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.com
> --
> Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.COM
>
> 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: 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).
>
--
Mladen Gogala
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mladen Gogala
INET: mgogala_at_adelphia.net
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: 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 Fri Aug 30 2002 - 08:28:41 CDT
