Re: indexing
Date: Tue, 19 Feb 2013 12:32:01 -0700
Message-Id: <1CA6147E-9BCB-42D9-AAA6-064FF43583A8_at_centrexcc.com>
A word of caution: index usage monitoring is not foolproof. Just because a monitored index never shows up in v$object_usage does not mean it is not used. IIRC there are (at least) two cases of index usage which are not recorded in v$object_usage. I believe it was Richard Foote who showed in a Hotsos presentation that an index which is used in resolving/following a foreign key relationship is not being recorded. Basically, only indexes which are used in an access plan get recorded. However, an index may be used by the optimizer during hard parsing even if it does not get used in the plan eventually. I am sure it was Jonathan Lewis who demonstrated this. An index may be used to get a more accurate cardinality estimate for a row source but not any further. This does not get recorded in v$object_usage but if you take that index away - because it is "unused" - the cardinality estimate changes and with it potentially the access path.
On 2013-02-19, at 11:39 AM, Tim Gorman wrote:
>
> Monitor the indexes either by using ALTER INDEX ... MONITORING USAGE
> command and then querying V$OBJECT_USAGE afterwards, or (if you are
> licensed for AWR) query the DBA_HIST_SQL_PLAN for OBJECT_NAME IN
> (/index-name-list/) and view the SQL text captured. The former
> mechanism (i.e. index monitoring) is designed for identifying unused
> indexes and is guaranteed accurate (see Tim Hall's excellent post at
> "http://www.oracle-base.com/articles/10g/index-monitoring.php"), and the
> latter suggestion (i.e. mining AWR data) is just a quick 'n' dirty way
> to detect index usage, but is based on sampled data and is not
> guaranteed accurate.
>
> Once unused indexes have been empirically proven, then you can document
> the cost in terms of space (by querying DBA_SEGMENTS) and estimate the
> relative cost to INSERT, UPDATE, and DELETE performance by constructing
> a copy of the table and it's indexes and using bogus workload generated
> within a PL/SQL procedure performing a fixed number of INSERT, UPDATE,
> and DELETE operations against the copied table. By running the PL/SQL
> procedure as a baseline, then removing the unused indexes and re-running
> the PL/SQL procedure, you should be able to provide a good estimate of
> the impact of the useless and unused indexes on transactions.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 19 2013 - 20:32:01 CET