如何收集Concurrent Request的SQL Trace

1. System Administrator > Concurrent > Program > Define

注意：如果仅仅是"Enable Trace",收集到的Sql Trace并不包含binds and waits

(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.)

2.Concurrent: Allow Debugging

Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option: Concurrent: Allow Debugging
Set profile to Yes

这个Profile如果设置成Yes，那么在运行Concurrent Request的时候，Debug Options项就变成Enable状态（如果为No，那么Debug Options按钮为灰显）

3.进入Debug Options

勾选SQL Trace，并选择"SQL Trace with Binds and Waits"

4.找到对应的sql trace文件

select name, value
from v$parameter
where name like 'user_dump_dest';

App Server上，切换上边的路径，然后

ls *Concurrent Request ID*

草稿

Find Trace File Name

Run the following SQL to find out the Rawtrace name and location for the concurrent program. TheSQL prompts the user for the request id

prompt
acceptrequestprompt'Pleaseentertheconcurrentrequestidfortheappropriateconcurrentprogram:'
prompt

columntraceidformata8
columntracenameformata80
columnuser_concurrent_program_nameformata40
columnexecnameformata15
columnenable_traceformata12
setlines80
setpages22
setheadoff

SELECT'Requestid:'||request_id,
'Traceid:'||oracle_Process_id,
'TraceFlag:'||req.enable_trace,
'TraceName:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog.Name:'||prog.user_concurrent_program_name,
'FileName:'||execname.execution_file_name||execname.subroutine_name,
'Status:'||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SIDSerial:'||ses.sid||','||ses.serial#,
'Module:'||ses.module
fromfnd_concurrent_requestsreq,v$sessionses,v$processproc,
v$parameterdest,v$parameterdbnm,fnd_concurrent_programs_vlprog,
fnd_executablesexecname
wherereq.request_id=&request
andreq.oracle_process_id=proc.spid(+)
andproc.addr=ses.paddr(+)
anddest.name='user_dump_dest'
anddbnm.name='db_name'
andreq.concurrent_program_id=prog.concurrent_program_id
andreq.program_application_id=prog.application_id
--- andprog.application_id=execname.application_id
and prog.executable_application_id = execname.application_id
andprog.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');

Ref Note:453527.1