题目
部分
在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的命令帮助。
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗
—————优质麦课————
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
本文分享自微信公众号 – DB宝（lhrdba）。
如有侵权，请联系 [email protected] 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。