About Oracle's systemstate dump

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

In some cases, the database automatically generates systemstate dump, such as "waited TOO LONG for A ROW CACHE ENQUEUE LOCK."

SystemState dump most of the time needs to be generated manually, the specific commands are:

If you have a lot of connections, such as thousands of connections, it can take up to 10 minutes to generate the dump, and it will occupy hundreds of m 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 be disconnected

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. Typically, in addition to systemstate dumps, it is best to generate hang analyze to visually understand the waiting relationships between database processes.

$sqlplus/as SYSDBA

Or

$sqlplus-prelim/as sysdba <== When the database is already slow or hang to be disconnected

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, which requires the systemstate dump of each instance at the same time, log on to any instance (without executing in all instances):

$sqlplus/as SYSDBA

Or

$sqlplus-prelim/as sysdba <== When the database is already slow or hang to be disconnected

Sql>oradebug Setmypid

Sql>oradebug Unlimit

Sql>oradebug-g all dump systemstate 266 <==-g All indicates that 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

Generate Hang analyze on 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

The above command generates SystemState dump in each instance, and the resulting information is placed in the Diag trace file in the backgroud_dump_dest of each instance.

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

SystemState dump has multiple levels:

2:dump (excluding 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 larger trace files, which are generally not recommended.