使用SQL_TRACE进行数据库诊断
http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm
包dbms_system定义如下：
SQL> desc dbms_system
PROCEDURE ADD_PARAMETER_VALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARNAME VARCHAR2 IN
VALUE VARCHAR2 IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
POSITION BINARY_INTEGER IN DEFAULT
PROCEDURE DIST_TXN_SYNC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
INST_NUM NUMBER IN
PROCEDURE GET_ENV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAR VARCHAR2 IN
VAL VARCHAR2 OUT
PROCEDURE KCFRMS
PROCEDURE KSDDDT
PROCEDURE KSDFLS
PROCEDURE KSDIND
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LVL BINARY_INTEGER IN
PROCEDURE KSDWRT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DEST BINARY_INTEGER IN
TST VARCHAR2 IN
PROCEDURE READ_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IEV BINARY_INTEGER IN
OEV BINARY_INTEGER OUT
PROCEDURE REMOVE_PARAMETER_VALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARNAME VARCHAR2 IN
VALUE VARCHAR2 IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
PROCEDURE REMOVE_PARAMETER_VALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARNAME VARCHAR2 IN
POSITION BINARY_INTEGER IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
PROCEDURE SET_BOOL_PARAM_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
BVAL BOOLEAN IN
PROCEDURE SET_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
PROCEDURE SET_INT_PARAM_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
INTVAL BINARY_INTEGER IN
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
PROCEDURE WAIT_FOR_EVENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
EVENT VARCHAR2 IN
EXTENDED_ID BINARY_INTEGER IN
TIMEOUT BINARY_INTEGER IN
--开启sql_trace功能
exec dbms_system.set_sql_trace_in_session(42,1853,true);
--关闭sql_trace功能
exec dbms_system.set_sql_trace_in_session(42,1853,false);
--查询trace文件位置：
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
FROM v$process a, v$session b, v$parameter c, v$instance d
WHERE a.addr = b.paddr
AND b.audsid = userenv('sessionid')
AND c.name = 'user_dump_dest';
二、针对别的窗口开启10046事件，此功能可以在sql执行前开启，也可以在sql执行后开启。
--开启10046事件
exec dbms_system.set_ev(42,1853,10046,12,'');
--关闭10046事件
exec dbms_system.set_ev(42,1853,10046,0,'');
--查询trace文件位置：
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
FROM v$process a, v$session b, v$parameter c, v$instance d
WHERE a.addr = b.paddr
AND b.audsid = userenv('sessionid')
AND c.name = 'user_dump_dest';
