SYSTEMSTATE DUMP

Hi guys!
I already posted about Hang Analyze (part1, part2) and ASHDUMP. Now, in the same ‘package’, let me show you about SYSTEMSTATE DUMP.

Systemstate is basically made by the process state for all process in instance (or instances) at the time the systemstate is called.
Through a systemstate it’s possible to identify enqeues, rowcache locks, mutexes, library cache pins and locks, latch free situations, and other kind of chains.

It’s a good tool to add in a SR, but it’s quite hard to habituate on reading/interpreting the file. To undertand exactly how to read a systemstate I’d recommend you the best: Read the manual!
The doc Reading and Understanding Systemstate Dumps (Doc ID 423153.1) has a very good explanation with examples, I’m not able to to it better. 🙂

What I can do is share about the SYSTEMSTATE levels. I had some difficult to find it…
But before I show how you can do it, it’s important to mention that Oracle does not recommend you to use ‘numeric events’ without a SR (MOS), according to Note: 75713.1.

So, systemstate dump has several levels:

Level

Content

2

dump (not including the lock element)

10

dump

11

dump + global cache of RAC

256

short stack (function stack)

258

256 + 2 -> short stack + dump (not including the lock element)

266

256 + 10 -> short stack + dump

267

256 + 11 -> short stack + dump + global cache of RAC

Levels 11 and 267 will dump global cache, will generate a large trace file, under normal circumstances is not recommended.

Under normal circumstances, if the process is not too much, it is recommended to use 266 because it can dump out the process of the function stack, it can be used to analyze the process in what to do.
But the more time-consuming to generate short stack, if the process is very large, such as the 2000 process, it may take more than 30 minutes. In this case, you can generate a level 10 or level 258, level 258 will collect more than level 10 short short stack, but less than level 10 to collect some lock element data.

In addition to the RAC system, please pay attention Bug 11800959 – A SYSTEMSTATE dump with level> = 10 in RAC dumps huge BUSY GLOBAL CACHE ELEMENTS – can hang / crash instances (Doc ID 11800959.8). The Bug is fixed in the 11.2.0.3. For versions <= 11.2.0.2 of the RAC, when the system lock an element for a bunch of time, when executing level 10, 266 or 267 of systemstate dump, it can cause database hang or crash. It may be solved by using level 258.