Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index monitoring and analyzing indexes - best solution?

Re: Index monitoring and analyzing indexes - best solution?

From: Tim Gorman <tim_at_evdbt.com>
Date: Wed, 21 Feb 2007 12:31:05 -0700
Message-ID: <45DC9DF9.6050103@evdbt.com>




  


The index monitoring functionality is a bit dodgy because of this
oversight by Oracle.

I think it's more useful to monitor V$SQL_PLAN (and the corresponding data structures in STATSPACK) to determine whether an index is getting used or not.  Play your cards right, and V$SQL_PLAN can even tell you who and what is using it (so you can filter out ANALYZE and DBMS_STATS), and also help you determine whether it *should* be used at all...



Jay.Miller@tdameritrade.com wrote:

I was about to write a script to load information on any unused indexes to a table, then another to drop index monitoring and then reenable it.  I figured I'd run the first just before gathering stale stats and the other just after gathering stale stats.

 

Before doing that I was wondering if anyone had a better way to handle this or had an existing script to do the same thing?

 

 

For anyone wondering what I'm talking about gathering stats on an index marks it as used.  Really annoying.

 

Jay Miller

 

 

-- http://www.freelists.org/webpage/oracle-l Received on Wed Feb 21 2007 - 13:31:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US