DBMS_STATS lib cahce latching from invalidations?
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 20 Jul 2010 11:32:08 -0500 (CDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
