Trace a Concurrent Request in Oracle EBS And Generate TKPROF File
Step 1: Enable Tracing For The Concurrent Manager Program
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Step 2: Turn On Tracing
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Step 3: 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
Step 4: 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
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;
Step 5: 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/ 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: his 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 raw_trace_file.trc output_file sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10
Step 1: Enable Tracing For The Concurrent Manager Program
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Step 2: Turn On Tracing
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Step 3: 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
Step 4: 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
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;
Step 5: 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/ 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: his 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 raw_trace_file.trc output_file sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10