Session Trace (10064) for a concurrent request

Description:

Oracles have lot of trace utility the oradebug is one the utility to trace particular running concurrent request using oracle spid.

Steps:

  • Check Request ID from Find Concurrent request screen
  • Run this query to get the SPID, for that concurrent request ID (25006 in my case) when prompted

SELECT a.request_id, d.sid, d.serial# , c.SPID FROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_processes b,v$process c,v$session d WHERE a.controlling_manager = .concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = ‘R’;

  • Check and SPID in OS level (to know the concurrent request running Node)

oracle@123# ps -ef|grep 25006

              oracle 20644  5494   0 16:33:11 pts/9       0:00 grep 25006

              oracle 25006     1   2 07:37:10 ?         523:58 oraclePROD1 (LOCAL=NO)

  • Set OSPID (25006in my case) for ORADEBUG

SQL> oradebug setospid 25006

  • Enable trace for 10046 event with level 12

SQL> oradebug event 10046 trace name context forever, level 12

  • Locate Trace file as

SQL> oradebug tracefile_name

/d01/app/oracle/diag/rdbms/prod/PROD1/trace/<trace_file>.trc

  • Wait for 15-20 minutes
  • Disable trace

 SQL> oradebug event 10046 trace name context off

  • Perform TKPROF for that trace file.

tkprof <Source_File.trc> <Output_File.txt> explain=apps/<apps_Password>  sys=no sort=fchela

 

Reference:

dba-oracle.com

Recent Posts