Useful tracing commands oradebug, oracle traces

Below are some userful commands that I use when detecting system/database hang or tracing a slow running or hanging session.

Oracle 10046 event:  We can use to trace the execution of a session or trace the system activities. 

alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;

 On sqlplus
--ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
--ALTER SESSION SET EVENTS='10046 TRACE NAME CONTEXT FOREVER,LEVEL 12';

Run the problematic SQL statement and wait till it is finished

--ALTER SESSION SET EVENTS='10046 TRACE NAME CONTEXT OFF';

tkprof dblab_ora_954796.trc output1.txt sys=no explain=system/ob1dblab sort=EXECPU,FCHCPU

select p.spid
from v$process p,v$session s
where s.paddr = p.addr
and s.audsid = userenv('SESSIONID')
/

Useful oradebug command when system hang or database hangconnect / as sysdba
oradebug setospid 12345 < -- where 12345 is the ospid of the hanging sessionoradebug unlimitoradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
<wait 1 minute>
oradebug dump errorstack 3
<wait 1 minute>
oradebug dump errorstack 3
oradebug event 10046 trace name context off'

connect / as sysdba
oradebug set mypid
oradebug unlimit
oradebug hang analyze 3
$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
wait 90 seconds
oradebug hanganalyze 3

oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266


alter session set tracefile_identifier = "my_test_session";
exec dbms_system.set_sql_trace_in_session(sid=>1057, serial#=>37621, sql_trace=>true);
exec dbms_system.set_sql_trace_in_session(sid=>1057, serial#=>37621, sql_trace=>false);