DBMS_STATS lib cahce latching from invalidations?
Date: Tue, 20 Jul 2010 11:32:08 -0500 (CDT)
Message-ID: <4b4a3fc925f90f6a6452a22c9c8174b8.squirrel_at_society.servebeer.com>
Howdy,
Stuck in 10.1.0.5.0 for now, I'm seeing major library cache latch issues (resembles a hang) on our production ERP DB when running my DBMS_STATS.GATHER_DATABASE_STATS job. I currently set the "no_invalidate" option to "false" (don't ask). We have a 3rd party bolt on that does not use bind variables. Scanning V$SQL finds ~40K variants of just 4 different SQLs from this bolt on. Attempts to use binds in this bolt on have so far failed. The library cache is currently ~1.75GB according to V$SGASTAT.
My theory is that the invalidations on the thousands of unshared SQLs is what's causing the latch issues. My plan was initially to revert back to using the default "auto" on "no_invalidate", but MOS 557661.1 says the effects of this are:
"all current cached cursors depending on this object are marked for rolling
invalidation"
...which seems to imply that I would likely still have a library cache
latching issue with that option. So I'm thinking I should set
"no_invalidate" to "true" and deal with the few exceptions that caused me to
force invalidating to start with instead of effectively bringing the system
down when stats are collected.
Thoughts?
TIA!
Rich
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 20 2010 - 11:32:08 CDT