本文介绍在EBS 11i/R12中如何为表单,OAF页面和并发程序启动trace。
1, Trace有什么用
SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段,是强有力的辅助诊断工具.在日常的数据库问题诊断和解决中,SQL_TRACE是非常常用的方法。
A trace file is a log of SQL run in a particular session or sessions focused on selects, inserts, updates, and deletes. A trace file can be used in many circumstances including reviewing performance, finding tables and views referenced, or finding the root of an error.
对开发人员来说,可以使用它找到影响性能的 SQL ,程序出错的原因。
2, 表单中启用trace
1)打开需要启用trace的表单
2)选择菜单:Help –> Diagnostics –> Trace
3, OAF中启用trace
1) 以系统管理员职责,在用户层设置预制文件:FND: Diagnostics 的值为Yes,进入诊断模式
2)以预置文件设置的同一用户登录首页,在页面的顶部点击Global按钮 Diagnostics
3) 根据需要,选择Trace打头的几个选项来进入SQLTrace模式,点击Save按钮
4, 并发程序启用trace
1) 进入并发程序定义:Sysadmin> Concurrent > Program > Define,查询出需要trace的并发程序
2) 勾选“Enable Trace”
3) 在高于R12.1的EBS版本中,还可以修改SQL Trace选项
5, 查找trace文件
1)表单启动trace后,会弹出一个对话框提示用户trace文件的存放位置。2)在11G以前版本中,可以使用下面的SQL查找trace文件名和文件的保存位置,输入参数为request_id:
prompt
accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:'
prompt
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
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;
3)可以使用下面的SQL片段查询trace文件的保存位置
Set Serveroutput ON
DECLARE
DB_Version VARCHAR2(2);
Trace_location VARCHAR2(240);
BEGIN
SELECT SUBSTR(vi.version, 1, INSTR(vi.version, '.')-1) INTO DB_Version FROM v$instance vi;
IF TO_NUMBER(DB_Version) >= 11 THEN
/* Following Line included as Execute Immediate as V$DIAG_INFO does not exist in DB Versions prior to 11g */
EXECUTE IMMEDIATE 'SELECT value FROM V$DIAG_INFO WHERE NAME = ''Diag Trace''' INTO Trace_location ;
ELSE
SELECT value INTO Trace_location FROM v$parameter WHERE name = 'user_dump_dest';
END IF ;
dbms_output.put_line('Trace File should be located in the directory :- '|| trace_location);
END;
/
参考:
metalink: [ID 296559.1]
how_to_find_the_table_view_of_form_application_in_oracle_ebs