Wednesday, August 29, 2012

Performance: How to enable Database trace for the Apps session which is already started running

You know how to enable Database trace for Forms & concurrent request, if you know in advance. But if you wish to enable Database trace for the Apps session which already started, then you can use oradebug .

This is required if you wish to do DB tracing when a program suddenly hangs / slow   and you do not want to cancel and resubmit.


Steps to enable DB trace dynamically

1) Identify the sid using below sql.

select sid,program,module from v$session
where program like '%f60web%';

2) Identify the SPID

select spid from 
v$process 
where addr in (
     select paddr 
       from v$session 
      where sid in (&&<from step1>)
                        );

3) Enable trace using ORADEBUG.

sqlplus /nolog
connect / as sysdba
set echo on
ORADEBUG SETOSPID <spidfrom step2>
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8

4) This enables trace for the running session.
  4.1)  Trace file will be found in the user_dump_dest. 
  4.2)  tkprof tracefile.trc output.txt explain=apps/<passwd> sort=prsela,exeela,fchel

5) If you want to disable , use below command from the same session (step 3)
ORADEBUG EVENT 10046 TRACE NAME CONTEXT off (Rem to turn off; to Switch off the Trace)


Hope this helps.

No comments: