Re: Performance impact of "MONITORING USAGE"

"Vladimir Andreev" <flado_at_imail.de> wrote in message news:7b208869.0312050214.78abc8d4_at_posting.google.com...
> > 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

The view isn't called v$segstat, so I'm not sure what you're getting at. One doesn't state an index is useful just because there are a lot of LIOs against it. One measures the LIOs against the number of physical operations. A wild imbalance in favour of LIOs indicates a lot of caching going on, and hence (because of the LRU mechanism) a lot of use for that index. Ergo, it's a useful index (meaning that Oracle -and Users- have found a use for it in their execution plans).

I didn't, either, suggest that tuning stops at identifying those indexes which are used a lot. But knowing which are used a lot is certainly a first step. And yes, that's exactly what v$segment_statistics can show you.

HJR

-- 
------------------------------------
Oracle insights at www.dizwell.com
------------------------------------