Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about what systemstate dump means. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

When the database has serious performance problems or hang, we really need to use systemstate dump to know what the process is doing, what it is waiting for, who is the holder of the resource, and who is blocking others. When the above problems occur, 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".

Most of the time, systemstate dump needs to be generated manually. The specific commands are:

If there are many connections, such as thousands of connections, it may take dozens of minutes to generate dump and it will take up hundreds of MB of disk space)

1. Log in to the database with sysdba:

$sqlplus / as sysdba

Or

$sqlplus-prelim / as sysdba oradebug setmypid

SQL > oradebug unlimit

SQL > oradebug dump systemstate 266

Wait 1 minute or 2 minutes.

SQL > oradebug dump systemstate 266

Wait 1 minute or 2 minutes.

SQL > oradebug dump systemstate 266

SQL > oradebug tracefile_name;== > this is the generated file name

two。 In general, 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 oradebug setmypid

SQL > oradebug unlimit

SQL > oradebug dump hanganalyze 3

Wait 1 minute or 2 minutes.

SQL > oradebug dump hanganalyze 3

Wait 1 minute or 2 minutes.

SQL > oradebug dump hanganalyze 3

SQL > oradebug tracefile_name;== > this is the generated file name

For RAC database, if you need systemstate dump of each instance at the same time, log in to any one instance (no need to execute in all instances):

$sqlplus / as sysdba

Or

$sqlplus-prelim / as sysdba oradebug setmypid

SQL > oradebug unlimit

SQL > oradebug-g all dump systemstate 266 oradebug-g all dump systemstate 266

Wait 1 minute or 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 minute or 2 minutes.

SQL > oradebug-g all hanganalyze 3

Wait 1 minute or 2 minutes.

SQL > oradebug-g all hanganalyze 3

After the above command is executed, a systemstate dump is generated for each instance, and the generated 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 changes in the process to see if it is real hang or slow.

Systemstate dump has several levels:

2: dump (excluding lock element)

10: dump

11: dump + global cache of RAC

256: short stack (function stack)

258: 256 short stack 2-> short stack + dump (excluding lock element)

266: 256 million 10-- > short stack+ dump

267: 25611-- > short stack+ dump + global cache of RAC

Level 11 and 267 will dump global cache and generate larger trace files, which are generally not recommended.

In general, if there are not too many processes, it is recommended to use 266, because this can dump the function stack of the process, which can be used to analyze what the process is doing.

But generating short stack is time-consuming, and if there are a large number of processes, such as 2000, it may take more than 30 minutes. In this case, you can generate level 10 or level 258, which collects more short short stack than level 10, but less lock element data than level 10.

Also for RAC systems, please follow Bug 11800959-A SYSTEMSTATE dump with level > = 10 in RAC dumps huge BUSY GLOBAL CACHE ELEMENTS-can hang/crash instances (Doc ID 11800959.8). This Bug was fixed on 11.2.0.3, for 256k (short stack, 2K per process)

-rw-r- 1 oracle oinstall 2724863 Aug 31 21:52 rac10g2_ora_31654.trc== > 10 (dump, 72k per process)

-rw-r- 1 oracle oinstall 2731935 Aug 31 21:53 rac10g2_ora_32214.trc== > 266 (dump + short stack, 72k per process)

RAC:

-rw-r- 1 oracle oinstall 55873057 Aug 31 21:49 rac10g2_ora_30658.trc = > 11 (dump+global cache, 1.4m per process)

-rw-r- 1 oracle oinstall 55879249 Aug 31 21:48 rac10g2_ora_28615.trc = > 267 (dump+global cache+short stack, 1.4m per process)

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

The above is the meaning of the systemstate dump shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.