Re: Performance impact of "MONITORING USAGE"

> I'm surprised at that comment, because the official performance tuning
> course notes state that the feature should only be turned on or off 'during
> periods of light database activity'. Reason? IIIRC, switching it on or off
> counts as a piece of DML that invalidates every execution plan in your
> Libraary Cache which happens to include a reference to the affected segment.
> So whilst the monitoring process itself is trivial, its consequence is to
> send your rate of hard parsing through the roof.

Never been to an official performance tuning course. And anyway, I would only switch it once. I am allowed to only drop indexes that are *never* used.

>
> This is the other reason I wouldn't even contemplate using this exciting new
> feature: it's useless. Switching it on means that if your index is used
> during a query, a flag is set in v$object_usage called 'Yes'. Not "yes I've
> been used once" or "yes I've been used a thousand times"... just "yes".

It doesn't matter. Counts would be nice, in order to be able to correlate query usage with index usage, but the real benefit is whether the index gets used at all. I'm happy with that.

> But a truly
> useful index would have huge numbers of logical reads when compared to
> physical writes. When you see v$segment_statistics, then, not only can you
> spot the useful indexes, you can actually quantify *how* useful they are...
> because the view counts the precise number of reads and writes each segment
> has been subject to.

I have to disagree on this.
Consider: I use the index to do 10000 lookups consuming 20000 logical reads, but preventing 10000 full scans, which would consume 20M logical reads. Is the index useful? Maybe. The better plan might have been to use a *single* full scan (e.g. hash join) instead of the 10000 lookups -- then it is not useful. Or the 10000 lookups might have come from 10000 executions of a statement -- then it is useful. And there's more: is an index that is frequently full-scanned more useful that one that is used for lookups? I'd say no. Yet v$segstat will show you orders of magnitude more reads against the full-scanned index than against the exact index. Or create a useless index on a 2-value column in a 2M-row table. If the index ever gets used, it will do so many LIOs that will make you name it "Index of the month"... yet it won't change the fact that the index is useless.

Bottom line: You cannot draw *ANY* conclusions about index usefullnes based on v$segstat.
Instead, use v$segstat to point you to the popular tables and indexes, and then go to v$sql_plan_statistics and v$sql_plan to get the filter predicates used on these segments and try to turn them into access predicates by tuning the SQL or creating indexes.

Thanks for your time.
Flado Received on Fri Dec 05 2003 - 04:14:32 CST