Re: DBMS_STATS lib cahce latching from invalidations?
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 26 Jul 2010 08:23:38 -0500 (CDT)
Way back when, I posted:
> 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.
Well, not a whole lot of responses. Perhaps my misspelling of "cache" in
the subject scared folks?
In any case, my theory seems to have been correct. I changed the
"no_invalidate" parameter of DBMS_STATS.GATHER_DATABASE_STATS to "TRUE" and
the library latch contention was reduced by almost 100% during the stats
collection run this weekend. There was just a few short waits, which I
normally see throughout the day anyway, most likely from the unshared SQL I
mentioned in the post.
I did get a reply to consider CURSOR_SHARING. Although the reply specified
FORCE, unless my job depended on it, there is no way I'd change it to
anything but the default of EXACT. I've been down that special road to
Hades before. It's like using a bandaid on a stab wound -- looks good on
the surface, but the real damage is hidden deep.
Posted for posterity.
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
