Trace Concurrent Request的執行過程

有一支Concurrent Request執行時間很久,想找出是那一段SQL花費了最多的時間。
可以到Program內找出想要Trace的Concurrent Request,再勾選Trace來追踨執行的過程。


按F11,輸入Concurrent Request Name後,勾選「Enable Trace」,
記得Trace完成後,要將它取消,否則每次Concurrent Request被執行,就會產生一次Trace File。


用下列的SQL來查Trace File的名稱與位置:
SELECT request_id ,oracle_Process_id,req.enable_trace,
            dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id|| 'APPS.trc' as "trace_name",
            prog.user_concurrent_program_name "Report Name",
             execname.execution_file_name|| execname.subroutine_name  "File Name"
             ,ses.sid,ses.serial#,proc.spid
    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;

找到Trace File之後,再以指令 tkprof 來簡化 Trace的內容
$ tkprof