SystemState Dump Introduction

SystemState Dump introduction by janezhang-oracle on December 13, 2012

When a database has a serious performance problem or hang, we are very much in need of systemstate dump to know what the process is doing, waiting for what, who is the resource holder, who is blocking the others. In the event of these problems, the timely collection of systemstate dump is very helpful in analyzing the cause of the problem.

In some cases, the database will automatically generate SystemState dump, such as "waited TOO LONG for A ROW CACHE ENQUEUE LOCK".
SystemState dump is generated most of the time by hand, and the specific commands are:

If you have a lot of connections, such as thousands of connections, it can take a few 10 minutes to generate a dump and consume hundreds of m of disk space.
1. Log on to the database with SYSDBA:
$sqlplus/as SYSDBA
Or
$sqlplus-prelim/as sysdba <== When the database is already slow or hang to fail to connect

Sql>oradebug Setmypid
Sql>oradebug Unlimit;
Sql>oradebug dump systemstate 266;
Wait 1-2 minutes.
Sql>oradebug dump systemstate 266;
Wait 1-2 minutes.
Sql>oradebug dump systemstate 266;
Sql>oradebug Tracefile_name;==> This is the generated file name


2. Usually in addition to systemstate dump, it is best to generate hang analyze at the same time to visually understand the wait relationship between database processes.

$sqlplus/as SYSDBA
Or
$sqlplus-prelim/as sysdba <== When the database is already slow or hang to fail to connect

Sql>oradebug Setmypid
Sql>oradebug Unlimit;
Sql>oradebug Dump Hanganalyze 3
Wait 1-2 minutes.
Sql>oradebug Dump Hanganalyze 3
Wait 1-2 minutes.
Sql>oradebug Dump Hanganalyze 3
Sql>oradebug Tracefile_name;==> This is the generated file name

For a RAC database, SystemState dump is required for each instance at the same time, then log on to any instance (no need to execute on all instances):

$sqlplus/as SYSDBA
Or
$sqlplus-prelim/as sysdba <== When the database is already slow or hang to fail to connect

Sql>oradebug Setmypid
Sql>oradebug Unlimit
Sql>oradebug-g all dump systemstate 266 <==-g All means dump is generated for all instances
Wait 1-2 minutes.
Sql>oradebug-g all dump systemstate 266
Wait 1-2 minutes.
Sql>oradebug-g all dump systemstate 266

To generate the hang analyze on the RAC:
Sql>oradebug Setmypid
Sql>oradebug Unlimit
Sql>oradebug-g All Hanganalyze 3
Wait 1-2 minutes.
Sql>oradebug-g All Hanganalyze 3
Wait 1-2 minutes.
Sql>oradebug-g All Hanganalyze 3

When the above command executes, SystemState dump is generated on each instance, and the resulting information is placed in the Diag trace file under the backgroud_dump_dest of each instance.

The above commands are executed three times to compare the process changes, to see if it is true hang, or very slow.

systemstate Dump has multiple levels:

2:dump (not including lock element)
10:dump
11:dump + global Cache of RAC
256:short stack (function stack)
258:256+2-->short Stack +dump (excluding lock Element)
266:256+10-->short stack+ Dump
267:256+11-->short stack+ Dump + global cache of RAC

Level 11 and 267 will dump global cache, generating a larger trace file, which is generally not recommended.

In general, if the process is not too many, it is recommended to use 266, because this can dump out the process's function stack, which can be used to analyze what the process is doing.
But generating a short stack can be time consuming, and if there are many processes, such as 2000 processes, it may take more than 30 minutes. In this case, level 10 can be generatedor level 258, the level 258 collects the short stack more than level 10, but collects some lock element data less than level 10.

Also for RAC systems, follow the bug 11800959-a systemstate dump with level >= in RAC dumps huge BUSY GLOBAL CACHE Elements-can Hang /crash instances (Doc ID 11800959.8). This bug is fixed on 11.2.0.3, and for <=11.2.0.2 RAC, when the lock element in the system is a lot, if you perform a level 10, 266, or 267 systemstate dump, May cause the database to hang or crash, in which case you can use level 258.

The following is a test for generating systemstate dump to see the space occupied by each level:

There are 37 processes in this example:

-RW-R-----1 Oracle Oinstall 72721 21:50 rac10g2_ora_31092.trc==>256 (short stack, 2K per process)

-RW-R-----1 Oracle Oinstall 2724863 21:52 rac10g2_ora_31654.trc==>10 (dump, 72K per process)
-RW-R-----1 Oracle Oinstall 2731935 21:53 rac10g2_ora_32214.trc==>266 (dump + short Stack, 72K per process)

Rac:
-RW-R-----1 Oracle oinstall 55873057 21:49 rac10g2_ora_30658.trc ==>11 (dump+global cache, per process 1.4M)
-rw-r-----1 Oracle oinstall 55879249 21:48 rac10g2_ora_28615.trc ==>267 (dump+global cache+short stack, per process 1.4M)

As a result, you can see that if you dump global cache (level 11 and 267, it takes up much more space than other levels).

SystemState Dump Introduction