Performance impact of "MONITORING USAGE"

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".
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?
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)
Received on Tue Dec 02 2003 - 11:10:17 CST