1.并发程序定义界面,勾选启用跟踪选项;
2.提交该并发请求;
3.使用请求号查询生成的trace文件路径,生成的文件在数据库服务器上,需要用数据库服务器账号登录;
路径使用以下sql查询,对应trace1和trace2字段,我所在的项目DEV环境的trace取自trace2:
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' "trace_1",
'Trace Name: ' || dest.value || '/' || dbnm.value || '_ora_' ||
oracle_process_id || '_SYSADMIN_CR' || req.request_id || '.trc' "trace_2",
--'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 apps.fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
apps.fnd_concurrent_programs_vl prog,
apps.fnd_executables execname
WHERE req.request_id = 12560569
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;
4.将trace文件下载到本地,在trace文件本地路径上,shif+右键,打开命令窗口
使用tkprof命令解析trc文件为更易读懂的txt文件。
tkprof XXX.trc output= XXX.txt
当然也可以在trc文件和txt文件名前面加上路径
tkprof f:\oracle\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1160.trc output=c:\aa.txt
5.打开生成的txt文件,建议使用EditPlus打开,更好读一些;
trace文件主要的参数:
count = number of times OCI procedure wasexecuted
cpu = cpu time in secondsexecuting ,cpu执行使用的时间
elapsed = elapsed time in secondsexecuting,整个sql执行的时间,包括cpu时间
disk = number of physical reads of buffers fromdisk
query = number of buffers gotten for consistentread
current = number of buffers gotten in current mode(usually for update)
rows = number of rowsprocessed by the fetch or execute call,返回的行数
查找txt文件里面的call count cpu elapsed disk query current rows参数,
主要查找cpu和elapsed时间最大的sql,查看其执行计划并对其进行分析优化;