Diagnostics for Database Performance Issues - Oracle

 

Diagnostics for Database Performance Issues ( Doc ID 781198.1 )

SRDC - How to Collect Standard Information for a Database Performance Problem ( Doc ID 1595092.1 )

 

Gather following data :

 

1. Generate AWRDIFF report , AWR , ADDM and ASH reports for problem time .

  SQL > @?/rdbms/admin/awrddrpt.sql

  SQL > @?/rdbms/admin/awrrpt.sql

  SQL > @?/rdbms/admin/addmrpt.sql

  SQL > @?/rdbms/admin/ashrpt.sql

 

 

2.A. Collect HangAnalyze traces

***********************************

    Generate 2 HangAnalyze Level 3 traces 1 minute apart, connected as the sys user:

# sqlplus / as sysdba

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug tracefile_name

SQL> oradebug hanganalyze 3

 

2.B.  Collect Systemstate dump

***********************************

Generate 1 Systemstate Level 258 , connected as the sys user:

# sqlplus / as sysdba

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug tracefile_name

SQL> oradebug dump systemstate 258

 

3. Collect alert.log and any other Oracle trace files that got generated during the time of the hang(performance issue)

 

sqlplus " / as sysdba"

 

If there are problems making this connection then in 10gR2 and above, the sqlplus "preliminary connection" can be used :

 

sqlplus -prelim " / as sysdba" (in 11.2.0.2 hanganalyze will not produce output with prelim connection)

 

HangAnalyze

============

Gather the hanganalyze using the following command.

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug hanganalyze 3

After a minimum 1 minute interval gather second hang analyze using the following command

SQL> oradebug hanganalyze 3

After a minimum 1 minute interval gather third hang analyze using the following command

SQL> oradebug hanganalyze 3

 

 

SystemState Dump

================

Gather the systemstates by executing the following

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug dump systemstate 266

After a minimum 1 minute interval gather second system states using the following command

SQL> oradebug dump systemstate 266

After a minimum 1 minute interval gather third system states using the following command

SQL> oradebug dump systemstate 266

SQL> oradebug tracefile_name

 

HangAnalyze in RAC

================

Gather the hanganalyze using the following command.

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug -g all hanganalyze 3

After a minimum 1 minute interval gather second hang analyze using the following command

SQL> oradebug -g all hanganalyze 3

After a minimum 1 minute interval gather third hang analyze using the following command

SQL> oradebug -g all hanganalyze 3

 

 

SystemState Dump IN RAC <<<<<< system state dump is a resource intensive operation

=====================

Gather the systemstates by executing the following

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug -G all dump systemstate 267

After a minimum 1 minute interval gather second system states using the following command

SQL> oradebug -G all dump systemstate 267

After a minimum 1 minute interval gather third system states using the following command

SQL> oradebug -G all dump systemstate 267

SQL> oradebug tracefile_name