使用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功能，此功能可以在sql执行前开启，也可以在sql执行后开启。

--开启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';
网站名称：使用SQL_TRACE进行数据库诊断
标题来源：http://bjjierui.cn/article/gsdpdp.html