How To Trace Concurrent Programs

CASE 1 : Concurrent Program Tracing without bind variables

1)   Follow the following navigation to enable logging for Concurrent Program

1. Goto Sysadmin > Concurrent > Program > Define
2. Query the concurrent program
3. Check the trace box to enable trace

2) Execute the concurrent program and note down the request id

CASE 2: Concurrent Program Tracing with bind variables and waits

1) Note down the following values

• SQL>SELECT value FROM v$parameter WHERE name = “max_dump_file_size”;
• SQL>SELECT value FROM v$parameter WHERE name = “timed_statistics”;

2)   Execute the following commands as sysdba

• SQL>ALTER SYSTEM SET max_dump_file_size = unlimited;
• SQL>ALTER SYSTEM SET timed_statistics = true;
• SQL>ALTER SYSTEM SET EVENTS ‘10046 trace name context forever, level 12’;

3)   Execute the concurrent program and note down the request id

4)Turn off tracing the reset the values.

• SQL>ALTER SYSTEM SET EVENTS ‘10046 trace name context off’;
• SQL>ALTER SYSTEM SET max_dump_file_size = <value from step 1>;
• SQL>ALTER SYSTEM SET timed_statistics = <value from step 1>;

CASE 3: Enabling the trace for a concurrent request for which you donot have privileges to run the concurrent Request.

1)   Now, Run the concurrent program and get the request id .

2)  Get the oracle_process_id for that concurrent request.

SELECT request_id,
oracle_process_id
FROM   fnd_concurrent_requests
WHERE  request_id IN (“xxxxxxx”);

3)   Now get the session details ( SID and Serial ) using value obtained from step 2

col “SID/SERIAL” format a10 col username format a15
col osuser format a15 col program format a40
SELECT s.sid
|| “,”
|| s.serial# “SID/SERIAL”,
s.username,
s.osuser,
s.status,
p.spid       “OS PID”,
s.inst_id,
s.MODULE
FROM   sys.gv_$session s,
sys.gv_$process p
WHERE  s.paddr = p.addr
AND s.inst_id = p.inst_id
AND p.spid = &value_from_step2
ORDER  BY To_number(p.spid);

4)   Execute the following command to enable the trace :

EXECUTE dbms_support.start_trace_in_session (&sid, &serial, binds=>true, waits=>
true);

5)   Collect the trace from udump location and investigate the issue.

 Reference Metalink Note: 296559.1 to know more about tracing.