如何收集Concurrent Request的SQL Trace

SQLTrace与并发程序调试指南

最新推荐文章于 2020-10-26 16:45:54 发布

最新推荐文章于 2020-10-26 16:45:54 发布 · 120 阅读

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