How to trace Performance issue on the Database

Performance can be investigated based on the System State dump and Hang Analyze. 

Applies to Oracle Database Environments. 

The following needs to be set so the next occurrence of the same issue can be captured.

The idea is reproduce the Performance issue at will and capture the trace. Even if the issue occurs intermittently, the following trace can capture when it occurs for the next time.

-----------------------------------------
-- System State and Hang Analyze
-----------------------------------------

connect / as sysdba

oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
oradebug hanganalyze 3
oradebug  dump systemstate 266
oradebug  dump systemstate 266
oradebug tracefile_name /* This is the file name to check for traces */
exit

After you have collected the traces, you may want to kill the session the causes the hang or performance behavior.

For example; you can set up the above trace to find Row Cache Lock issue. 

Get systemstate dump while the problem is occurring:
Steps: 
----------------------------------------- If this is RAC: ----------------------------------------- sqlplus -prelim '/ as sysdba' SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug -g all dump systemstate 267 -- wait for 15 or 20 seconds -- SQL> oradebug -g all dump systemstate 267 SQL> oradebug tracefile_name The last oradebug command will give you the name and location of the trace file. The systemstate dumps must be taken while the issue is occurring, or the data will not be useful. If this is non-RAC, then do the following instead: SQL> oradebug setmypid SQL> oradebug unlimit; SQL> oradebug dump systemstate 266 -- wait 30 seconds -- SQL> oradebug dump systemstate 266 -- wait 30 seconds -- SQL> oradebug dump systemstate 266 SQL> oradebug tracefile_name The last oradebug command will give you the name and location of the trace file. The systemstate dumps must be taken while the issue is occurring, or the data will not be useful.

Thanks.