Trace on Concurrent Program

Trace on Concurrent Program

Method 1 : Enabling Trace for the concurrent Program

This is a normal method of tracing the concurrent manager by setting “enable tracing” is a simple sql_trace. This doesnot capture the bind variables and wait events.

To get the complete details of the execution of the concurrent request, it is adviced that the event 10046 be set for Waits and Binds (Level 12).

Method 2 : How to enable event 10046 for a concurrent request

Above method is a normal method of tracing the concurrent manager by setting “enable tracing” is a simple sql_trace. This doesnot capture the bind variables and wait events. To get the complete details of the execution of the concurrent request, it is adviced that the event 10046 be set.

This is a brief document, which talks about the method to follow to set the event for a concurrent request.

Get the concurrent request id from the applications screen.

Once the concurrent requesr id is known,

Get the oracle process id for the request.

To get the oracle process id:

Sql> select oracle_process_id, oracle_session_id, v$session.serial# from v$process, v$session, fnd_concurrent_requests where request_id=&request id and v$process.pid=fnd_concurrent_requests.oracle_process_id and fnd_concurrent_requests.oracle_session_id=v$session.sid;

Once the PID, serial# and Sid are known, the event 10046 can be enabled:

Connect as sysdba

Oradebug setorapid <PID>

Oradebug unlimit

Oradebug events 10046 trace name context forever, level < the level of the event >

Or

Execute dbms_system.set_ev (SID, serial#, 10046, <the level >,’’)

This gives the trace file in the user_dump_destination of the database.

Example :

Once the oracle process id is known: you can set the level 12 trace by:

connect internal

oradebug setorapid <the oracleprocess id

oradebug unlimit

oradebug event 10046 trace name context forever, level 12

Send the raw trace along with the tkprof.

Method 3 : Enabling Trace – Generic Method for Oracle Apps

If you are not comfortable with the above method then use the following method to get the trace file:

Please make sure to reset the profile to NULL after the traces.

Enabling Trace – Generic Method for Oracle Apps

Set the profile ‘Initialization SQL Statement – Custom’ at user level to the following value :

BEGIN FND_CTL.FND_SESS_CTL('','', 'TRUE', 'TRUE','','ALTER SESSION SET EVENTS ='''''' 10046 TRACE NAME CONTEXT FOREVER, LEVEL <x> '''''); END;

This will trace all the sessions of that user at level <x>

Where <x> is

• Level 1 - Standard Sql Trace

• Level 4 - Level 1 PLUS Bind values

BINDS #1:

bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=24 offset=0

bfp=80000001000cbc78 bln=22 avl=04 flg=05 value=144867

• Level 8 - Level 1 PLUS Wait Statistics

WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0

WAIT #1: nam='db file sequential read' ela= 2 p1=330 p2=121062 p3=1

• Level 12 - Level 1 PLUS Bind values and Wait Statistics

BEGIN FND_CTL.FND_SESS_CTL('','', 'TRUE', 'TRUE','','ALTER SESSION SET EVENTS ='''''' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '''''); END;

This gives the trace file in the user_dump_destination of the database.

Running TKPROF :

Once you get the trace file from the user_dump_destination of the database. Please run the tkprof utility as mentioned below.

tkprof tracefile outputfile Explain=apps/<appspasswd>