RE: Identify unused indexes in 8i ?

Good words: your final sentence is very important.

My only caveat is that without scouring the source code, it's difficult to determine whether an index might be used in the future. As in the stock market, "historical performance is no guarantee of future performance." The problem is that some business processes are activated only once a year. In this case, then *to be sure* about an index's relevance, your measurement interval needs to be at least a year long--an impractically long analysis period for most people.

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-----
Gogala
Sent: Thursday, August 29, 2002 11:53 PM To: Multiple recipients of list ORACLE-L

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: 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).