Re: Performance impact of "MONITORING USAGE"

"Vladimir Andreev" <flado_at_imail.de> wrote in message news:7b208869.0312020910.16442526_at_posting.google.com...
> I am trying to convince my customer to switch on index monitoring on
> their 9.2.0.4 production database. To do that successfully, I must
> first convince myself that:
> a) their instance won't start crashing
> b) monitoring will have a negligible impact on the application
> performance.
>
> On point a), I searched MetaLink's Bug Database and found only a
> handful of pretty harmless bugs logged against this feature. So I feel
> on the safe side.
> On point b) I was able to find only a single remark by Tom Kyte on his
> website: "none that I can measure".

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.

> Now, I am inclined to believe that the performance impact should be
> very small, for these reasons:
> 1. To monitor index usage, one needs to only add a single line of code
> (to flip the flag in v$object_usage) near the end of the procedure
> doing the hard parses.
> 2. Even if access to v$object_usage needs to be serialized, this
> should not require a new latch get, as sessions are already serialized
> on the shared pool and library cache latches while hard-parsing.
>
> On the other hand, I have doubts based on this:
> 1. The Oracle Performance Tuning Guide suggests that index monitoring
> should be turned on for a period and not permanently: "You can find
> indexes that are not being used by using the ALTER INDEX MONITORING
> USAGE functionality over a period of time that is representative of
> your workload." Why not forever?

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". So how do you tell the difference between an index that gets hit once in a blue moon and one that gets clobbered to death every few seconds? Er, you can't.

Not, that is, unless you flip the monitoring state of the index, because that re-sets the falg back to 'No'. So if you periodically flip the monitoring state back and forth, let's say every 15 minutes, you'd be able to capture the state of the usage flag each time. If you ended up with a pattern 'Yes,No,No,No,Yes,No,No,No,No', it's not a very useful index. But if your pattern read "Yes,Yes,Yes,Yes,No,Yes,Yes,Yes,No,Yes..." then it looks as if it might be.

Good tool, huh? Not. Switching back and forth that frequently... lots of hard parsing, huge amounts of CPU, lots of library cache latch issues.

> 2. The usage data must survive an instance failure, meaining any
> changes to it should generate redo, and they are visible immediately
> after the modification has taken place (unlike TABLE monitoring data,
> which is flushed to disk once every three hours). This should have its
> cost.
>
> So, to come to the question:
> I would appreciate if anyone can enlighten me on the real mechanics
> behind the "monitoring usage" feature, or test cases showing the
> (absence of) performance impact. I was pretty happy whith Steve Adams'
> explanation of the mechanics behind TABLE monitoring, but he hasn't
> published one for index monitoring on his website (www.ixora.com.au)
> yet, alas.
>
> Thanks in advance,
>
> Flado
>
> (Vladimir Andreev)

You have 9i Release 2, right? Then provided you are collecting segment statistics (which you are by default, and if not set statistics_level to TYPICAL or ALL and you will be), you can query the new 9iR2 view v$segment_statistics. There, you'll find columns for logical reads and physical writes (amongst others). Even the most useless index in the world has to be physically read and written from time to time, because the table it's built on is modified, and the index has to be maintained. 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.

The overhead of setting segment_statistics to ALL is quite high, but for TYPICAL, it's barely noticeable. And you can set and forget it.

Like all v$ views, v$segment_statistics is cumulative since instance startup, so the raw numbers may not be very meaningful (the fact that you've done 800,000,000 physical reads since 1999 is not the world's most useful piece of information)... but you can grab the statistics at the start of a monitoring period, and grab them again at the end, calculate the difference between the two and voila! you have the reads and writes performed during your monitoring period.

HTH.
Regards
HJR Received on Tue Dec 02 2003 - 13:12:10 CST