【DB笔试面试688】在Oracle中，跟踪会话执行语句的方法有哪几种？

AiDBA宝典

发布于 2019-11-15 15:48:56

1K00

代码可运行

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

运行总次数：0

代码可运行

♣

题目部分

在Oracle中，跟踪会话执行语句的方法有哪几种？

♣

答案部分

因为TRACE的目标范围不同，所以导致必须使用不同的方法。若作用于数据库全局的，则改初始化参数。若只作用于当前会话的，则就用ALTER SESSION命令。若作用于其它会话的，则就用DBMS_SYSTEM包。

①　SQL_TRACE参数设置：非常传统的方法

SQL_TRACE可以作为初始化参数在全局启用，也可以通过命令行方式在具体会话启用。在参数文件（PFILE/SPFILE）中指定：SQL_TRACE=TRUE或ALTER SYSTEM SET SQL_TRACE=TRUE;。

通过在全局启用SQL_TRACE可以跟踪到所有后台进程及所有用户进程的活动，通过跟踪文件的实时变化，可以清晰地看到各个进程之间的紧密协调。需要注意的是，在全局启用SQL_TRACE会产生大量trace文件，很容易耗尽磁盘空间，这通常会导致比较严重的性能问题，所以在生产环境中要谨慎使用，并且及时关闭。

在大多数时候使用SQL_TRACE跟踪的都是当前会话的进程。通过跟踪当前进程可以发现当前操作的后台数据库递归活动，这在研究数据库新特性时尤其有效，在研究SQL执行，发现后台错误等方面也非常有用。

在会话级启用和停止SQL_TRACE的方式如下所示：

启用当前SESSION的跟踪:

1SQL> ALTER SESSION SET SQL_TRACE=TRUE;
2Session altered.

此时的SQL操作将被跟踪:

1SQL> SELECT COUNT(*) FROM DBA_USERS;
2  COUNT(*)
3----------
4        44

结束跟踪:

1SQL> ALTER SESSION SET SQL_TRACE=FALSE;
2Session altered.

在很多时候需要跟踪其他用户的进程，而不是当前用户，这可以通过Oracle提供的系统包DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成。

通过V$SESSION可以获得SID、SERIAL#等信息，获得进程信息，选择需要跟踪的进程：

 1SQL> col spid for a8
 2SQL> col spid for a10
 3SQL> col username for a10
 4SQL> col tracefile for a80
 5SQL> SELECT S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.USERNAME, P.TRACEFILE
 6  2    FROM GV$SESSION S, GV$PROCESS P
 7  3   WHERE P.ADDR = S.PADDR
 8  4     AND S.INST_ID = P.INST_ID
 9  5     AND S.USERNAME IS NOT NULL;
10   INST_ID        SID    SERIAL# SPID       USERNAME   TRACEFILE
11---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------------------
12         1        144        293 37302      SYS        /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_37302_lhr.trc
13         1        146       1557 37340      SYS        /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_37340.trc
14         1         26        321 37380      LHR        /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_37380.trc
15         1        152       3243 37400      SYS        /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_37400.trc

设置跟踪SID为26的会话:

1SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(26,321,TRUE);
2PL/SQL procedure successfully completed.

可以等候片刻，等待SID为26的会话执行SQL，完成后，停止跟踪：

1SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(26,321,FALSE);
2PL/SQL procedure successfully completed.

②　使用10046事件

全局设定：参数文件中指定：EVENT="10046 TRACE NAME CONTEXT FOREVER,LEVEL 12"

或者：

1SQL> ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';--启用
2SQL> ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT OFF';--关闭

当前会话设定：

1SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
2SQL> --执行SQL语句
3SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

在Oracle 11g中还可以对单个SQL打开10046事件跟踪，命令如下所示：

1ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] WAIT=TRUE,BIND=TRUE,PLAN_STAT=ALL_EXECUTIONS,LEVEL=12';

关闭单个SQL的跟踪命令如下所示：

1ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] OFF';

若要针对每个新连接的会话生成10046跟踪文件，则可以使用登录触发器。如下的代码是跟踪LHR用户的信息，在用户LHR登录数据库系统时会启动10046跟踪。

1CREATE OR REPLACE TRIGGER TRI_SET_TRACE_LHR
2AFTER LOGON ON DATABASE
3WHEN (USER IN ('LHR'))
4DECLARE
5BEGIN
6    EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL=ALL';
7    EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED';
8    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
9END TRI_SET_TRACE_LHR;

③　DBMS_SESSION包：只能跟踪当前会话，不能指定会话

跟踪当前会话：

1SQL> EXEC DBMS_SESSION.SET_SQL_TRACE(TRUE);
2SQL> 执行SQL
3SQL> EXEC DBMS_SESSION.SET_SQL_TRACE(FALSE);

DBMS_SESSION.SET_SQL_TRACE相当于ALTER SESSION SET SQL_TRACE，从生成的TRACE文件可以明确地看ALTER SESSION SET SQL_TRACE语句。使用DBMS_SESSION.SESSION_TRACE_ENABLE过程，不仅可以看到等待事件信息还可以看到绑定变量信息，相当于“ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';”语句：

1SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(WAITS=>TRUE,BINDS=>TRUE);
2SQL> 执行SQL
3SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();

④　DBMS_SYSTEM包

使用DBMS_SYSTEM.SET_EV设置10046事件：

1SQL> EXEC DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,12,'');--启用跟踪
2SQL> EXEC DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,0,'');--结束跟踪

⑤　DBMS_MONITOR包

该包是从Oracle 10g开始提供的，功能非常强大。可在模块级别、动作级别、客户端级别、数据库级别、会话级别进行跟踪，Oracle官方支持。

跟踪当前会话：

1SQL> EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE;
2SQL> --执行SQL
3SQL> EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE;

跟踪其他会话：

1SQL>EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>SID,SERIAL_NUM=>SERIAL#,WAITS=>TRUE,BINDS=>TRUE);
2SQL> --执行SQL
3SQL>EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(SESSION_ID=>SID,SERIAL_NUM=>SERIAL#);

⑥　oradebug

这是SQL*Plus的工具，需要提供OSPID或者Oracle PID。

跟踪当前会话：

 1SQL> oradebug setmypid;
 2Statement processed.
 3SQL> oradebug unlimit;
 4Statement processed.
 5SQL> oradebug event 10046 trace name context forever,level 12;
 6Statement processed.
 7SQL> 执行SQL
 8SQL> oradebug tracefile_name
 9SQL> oradebug event 10046 trace name context off;
10Statement processed.

跟踪其他会话：

1SQL> select spid,pid2  from v$process
2  2  where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat));
3SPID                PID
4------------ ----------
51457                 313
6SQL> oradebug setospid 1457;
7Statement processed.

或者：

1SQL> oradebug setorapid 313;
2Statement processed.
3SQL> oradebug unlimit;
4Statement processed.
5SQL> oradebug event 10046 trace name context forever,level 12;
6Statement processed.
7SQL> oradebug tracefile_name
8SQL> oradebug event 10046 trace name context off;
9Statement processed.

使用oradebug help可以查看oradebug的命令帮助。