RE: Identify unused indexes in 8i ?

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