Whilst investigating the latest of our many library cache contention problems on 11.2, I made the fatal mistake of relying on my previous experience combined with a standard Oracle Support note describing how to diagnose such problems.
When the system was apparently hung (although the reality was that one session was holding a library cache mutex or latch that other sessions were waiting for – hopefully more on that later), I decided a Systemstate dump would be a good idea. (On a side-note, I often prefer Hanganalyze but I suspect that’s more to do with me finding the resulting trace file easier to read.)
What a mistake. Particularly as this was a Production system and I’d managed to talk one of the clients good DBAs into helping me out! As soon as the statement was run, the system went into a much worse state and, to cut a medium but too exciting story short, we ended up having to restart everything.
To make matters worse, another good colleague reminded me that he had sent out a warning to people that he’d hit the same experience earlier, due to bug 11800959 – “A SYSTEMSTATE dump with level >= 10 in RAC env dumps unnecessary huge BUSY GLOBAL CACHE ELEMENTS”. In the usual email flurry, I’d put that whole conversation to one side.
So please don’t make the same mistake as I did and ignore the warning – tread carefully before you start taking Systemstate dumps on 11.2!
Hi Doug
“hopefully more on that later”
I’ll certainly be following any further threads on this subject with keen interest. Library cache contention problems on 11.2 have been something of an issue for me of late too! We’re running single-instance 11.2.0.2.1 and have been seeing spikes in “cursor: pin S wait on X” and “library cache lock” waits. I’ve made a reasonable amount of headway, chipping away at some bugs we’ve seen where huge numbers of child cursors are being needlessly generated (11930680, 10636231, a couple fixed in 11.2.0.2.1, and yet another bug involving secure view merging which I found a workaround for and am told the issue doesn’t reproduce in 11.2.0.2.3). None the less, we continue to see the occaisional spike that appears to involve queries that access dual.
Looking at ASH and V$SQL_SHARED_CURSOR I can see the contention occurs for the same set of statements, and it’s when a child is being purged and another created. However, since the parsing schema etc. for the child is the same, i just can’t figure out why a query like SELECT 1 FROM dual would need to generate a new child!
Finally, there is a statement when a parent cursor is continually leaking memory. When it hits the 300 – 400MB mark, we start to see average 90+ msec waits for the shared pool latch (presumably because of the work it’s doing whilst allocating more more memory). I’m currently working through this one with support.
Anyway, should I make some headway with these, I’ll be sure the post the info here in case it’s of interest to yourself or other readers in a similar predicament…
Hi Austin,
First of all, apologies for the very slow reply but I’ve been waiting until I find out more about the particular problems we’re facing but that effort has been abandoned for now for the usual reasons – lack of time, resources and desire to investigate things properly.
But I was interested to look at the various bugs you mentioned and these chime with various issues I’ve seen over the past year and a bit, predominantly around large numbers of child cursors being created for obtuse reasons. It seems to me that things got a lot better between 11.2.0.1 and 11.2.0.2 but they’re still far from perfect!
I’m very interested in how you get on with your issues and hopefully I will eventually make some progress on the ones I’ve seen.
Cheers,
Doug
Hi Doug
No need to apologise. I’m having similar time and resource problems too!
The good news is that the leaking parent cursor was resolved with patch 10082277.
We do still see some high version counts, but I’m looking at applying PSU 3 shortly and willsee what I’m left with.
I’ll keep you posted…
Nice one 🙂 Thanks!