How to trace a Concurrent Request and generate the TKPROF file

I hope this document will help you to find-out the long-running concurrent request from an oracle applications 11i/R12.

1. Generate Trace File

Enable Tracing For The Concurrent Manager  Program
Select the Enable Trace Checkbox
Note : Checking the Trace Check box on the Concurrent Program gives an Event 10046 Level 8 trace. So even if the trace is set for Binds and Waits on the Submission form once the concurrent program is encountered in the trace it will reset to level 8 so no binds will be present in the trace after that point.
Turn On Tracing
•Responsibility: System Administrator
•Navigate: Profiles > System
•Query Profile Option Concurrent: Allow Debugging
•Set profile to Yes
Run Concurrent Program With Tracing Turned On
•Logon to the Responsibility that runs the Concurrent Program
•In the Submit Request Screen click on Debug Options (B)
•Select the Checkbox for SQL Trace

If the Debug option is greyed out & not updateable set the profile Concurrent: Allow Debugging to Yes
2. Find Trace File Name
Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id
prompt
accept request prompt ‘Please enter the concurrent request id for the appropriate concurrent program:’
prompt
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
SELECT ‘Request id: ‘||request_id ,
‘Trace id: ‘||oracle_Process_id,
‘Trace Flag: ‘||req.enable_trace,
‘Trace Name:
‘||dest.value||’/'||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,
‘Prog. Name: ‘||prog.user_concurrent_program_name,
‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,
‘Status : ‘||decode(phase_code,’R',’Running’)
||’-'||decode(status_code,’R',’Normal’),
‘SID Serial: ‘||ses.sid||’,'|| ses.serial#,
‘Module : ‘||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name=’user_dump_dest’
and dbnm.name=’db_name’
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
To check the timeline of the request :
SELECT request_id, TO_CHAR( request_date, ‘DD-MON-YYYY HH24:MI:SS’ )
request_date, TO_CHAR( requested_start_date,’DD-MON-YYYY HH24:MI:SS’ )
requested_start_date, TO_CHAR( actual_start_date, ‘DD-MON-YYYY HH24:MI:SS’ )
actual_start_date, TO_CHAR( actual_completion_date, ‘DD-MON-YYYY HH24:MI:SS’ )
actual_completion_date, TO_CHAR( sysdate, ‘DD-MON-YYYY HH24:MI:SS’ )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) – actual_start_date ) * 24, 2 ) duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER(‘&p_request_id’);
You may produce a complete report reference of the request ID using the Note:187504.1 bde_request.sql Process and Session info for one Concurrent Request(11.5)

3. TKPROF Trace File
Once you have obtained the Raw trace file you need to format the file using TKPROF.
$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no
Where:
raw_trace_file.trc: Name of trace file
output_file: tkprof out file
explain:  This option provides the explain plan for the sql statements
sort:  This provides the sort criteria in which all sql  statements will be sorted.  This will bring the bad sql at  the top of the outputfile.
sys=no:  Disables sql statements issued by user SYS
Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10″ long running queries
$ tkprof sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10