Systemstate Dump warning

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!