Re: INDEX USAGE

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Thu, 5 Jun 2003 09:07:36 +0200
Message-ID: <bbmqb2$b0s7l$1@ID-152732.news.dfncis.de>

"W.Breitling" <member28455_at_dbforums.com> schrieb im Newsbeitrag news:2958603.1054745576_at_dbforums.com...
>
> I don't have first hand experience with 9i in a production setting yet,
> but the problem I see with using v$segment_statistics as well as using
> v$bh (as touted in "Is the index still being used ?") in determining
> used/unused indexes is that an index presence in those views does not
> necessarily indicate that the index in question is actively being used
> in sql plans. They could just be there because they need to be
> maintained as a result of DML on the row(s) they index. So it could
> still be just dead weight.
>

True.

If it can be found in the cache,
it says only if the index is being used _at all_, not by what app and how many times and so on ... but at least this is a starting point, especially if You are on 8i like me and others.

The appropriate answer would be an explain plan of _all_ statements against the database ...
quite a hard job. And even more hard when the statements come from outside, like from Forms,
and not from a stored procedure. Received on Thu Jun 05 2003 - 02:07:36 CDT