How to Generate Database Systemstate Dumps and Hanganalyze

 
Generating Systemstate Dumps and Hanganalyze for your database is cruicial when diagnosing database performance or hanging issues.

Hanganalyze and Systemstate dumps provide information on the processes in the database at a specific point in time. Hanganalyze provides information on all processes involved in the hang chain, whereas systemstate provides information on all processes in the database. When looking at a potential hang situation, you need to determine whether a process is stuck or moving slowly. By collecting these dumps at 2 consecutive intervals  this can be established. If a process is stuck, these traces also provide the information to start further diagnosis and possibly help to provide the solution.

  • Hanganalyze is a summary and will confirm if the db is really hung or just slow and provides a consistent snapshot.
  • Systemstate dump shows what each process on the database is doing

Commands for Hanganalyze & Systemstate

Single Instance Non-RAC Database:

Hanganalyze
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- Wait one minute before getting the second hanganalyze
oradebug hanganalyze 3
oradebug tracefile_name
exit

Systemstate
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
oradebug dump systemstate 266
oradebug tracefile_name
exit

RAC Database

There are 2 bugs affecting RAC that without the relevant patches being applied on your system, make using level 266 or 267 very costly. Therefore without these fixes in place it highly unadvisable to use these level.

RAC Hanganalyze and Systemstate with fixes for bug 11800959 and 11827088
sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug  unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
oradebug -g all dump systemstate 266
exit

RAC Hanganalyze and Systemstate without fixes for bug 11800959 and 11827088
sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
oradebug -g all dump systemstate 258
exit

  • In RAC environments, a dump will be created for all RAC instances in the DIAG trace file for each instance.
Taken from Metalink Article # 452358.1 How to Collect Diagnostics for Database Hanging Issues

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart