合 Oracle中的SQL_TRACE是什么？诊断事件event是什么？常用的events有哪些？常用的10046及10053诊断事件的区别是什么？

SQL_TRACE

SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具。在日常的数据库问题诊断和解决中，SQL_TRACE是非常常用的方法。可以设置SQL_TRACE为TRUE，但是一般不推荐在全局指定为TRUE，只建议在会话级别指定。若在全局设定则对性能会造成较大影响。

诊断事件

Oracle为数据库提供了多种的诊断工具，诊断事件（Event）是其中一种非常实用的方法，它能使DBA可以方便地转储数据库各种结构及跟踪特定事件的发生过程。
通过如下的脚本可以获取所有的诊断事件列表：

在Linux系统下诊断事件的消息文件放在目录：$ORACLE_HOME/rdbms/mesg/oraus.msg。oraus.msg文件属于文本文件，可以直接打开查看。

诊断事件可以像普通的ORA错误一样采用oerr命令来查询，如下所示：

设置诊断事件有两种方法，一种是在pfile参数文件中设置事件，这样数据库在OPEN后，将影响到所有的会话。设置格式如下：

通过冒号（:）符号，可以连续设置多个事件，也可以通过连续使用event来设置多个事件。如：

或者分开写，如：

另一种方法是在会话过程中使用ALTER SESSION SET EVENTS命令，只对当前会话有影响。设置格式如下：

通过英文冒号（:）符号，可以连续设置多个事件，也可以通过连续使用ALTER SESSION SET EVENTS来设置多个事件，如：

格式说明：

• eventnumber指触发dump的事件号，事件号可以是Oracle错误号（出现相应错误时跟踪指定的事件）或Oralce内部事件号，内部事件号在10000到10999之间，不能与immediate关键字同用。

• immediate关键字表示命令发出后，立即将指定的结构dump到跟踪文件中，这个关键字只用在ALTER SESSION语句中，并且不能与eventnumber、forever关键字同用。

• trace name是关键词，trace name位于第二、三项，除它们外的其它限定词是供Oracle内部开发组用的。

• eventname指事件名称，即要进行dump的实际结构名。若eventname为context，则指根据内部事件号进行跟踪。

• forever关键字表示事件在实例或会话的周期内保持有效状态，不能与immediate同用。

• level为事件级别关键字。但在dump错误栈（errorstack）时不存在级别。level通常位于1-10之间（10046有时用到12），10意味着转储事件所有的信息。例如当转储控制文件时，level 1表示转储控制文件头，而level 10表明转储控制文件全部内容。

• levelnumber表示事件级别号，一般从1到10，1表示只dump结构头部信息，10表示dump结构的所有信息。

• 转储所生成的trace文件在user_dump_dest初始化参数指定的位置。

• 移除所有的诊断事件（Event）：

如果设置了诊断事件（Event），那么如何知道在系统中设置了哪些诊断事件（Event）呢？如果事件是在pfile文件中设置的，那么可以用“SHOW PARAMETER EVENT”来查看。如果是通过“ALTER SYSTEM|SESSION”来设置的，那么可以使用如下的SQL来查看：

但是，10046和10053事件不能通过这种方式查询，只能通过oradebug来查询，如下：

设置诊断事件需要注意的是，即可以使用“ALTER SYSTEM EVENTS”，也可以使用“ALTER SYSTEM EVENT”，它们的区别在如下2点：

①　EVENTS可以动态修改，可以使用“ALTER SESSION”或“ALTER SYSTEM”设置，只影响内存不影响参数文件

②　EVENT不能动态修改，只能使用“ALTER SYSTEM”或在参数文件里设置，必须重启库方可生效

另外，“ALTER SYSTEM”会记录到告警日志中，“ALTER SESSION”不会记录在告警日志中。

10046和10053

最为常用的诊断事件是10046和10053事件。10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强。Oracle的10046事件，可以跟踪应用程序所执行的SQL语句，并且得到其解析次数、执行次数、CPU使用时间等信息。这对DBA来分析、定位数据库性能问题是非常有用的。
10046事件可分成不同的级别（LEVEL），分别追踪记录不同程度的有用信息。对于这些不同的级别，应当注意的是向下兼容的，即高一级的TRACE信息包含低于此级的所有信息。
10046事件可以设置以下级别:
①　level 0：禁用SQL_TRACE，等价于SQL_TRACE=FALSE
②　level 1：启用标准的SQL_TRACE功能跟踪SQL语句，包括解析、执行、提取、提交、回滚、响应时间、服务时间、处理的行数，物理读和写的数目、执行计划以及其它一些额外信息，等价于SQL_TRACE=TRUE
③　level 4：Level 1 + 包括绑定变量（Bind Values）的详细信息
④　level 8：Level 1 + 包括等待事件
⑤　level 12：包括绑定变量与等待事件，包含Level 1 + Level 4 + Level 8
10053事件是最常用的Oracle优化器跟踪Trace。10053事件解析优化器为什么选择某个执行计划但并不记录这个执行计划到底运行地如何。10046事件并不解释优化器的工作，但它记录了在SQL解析阶段所遇到的等待事件和所消耗的CPU等资源，以及执行阶段的各项指标。需要注意的是，如果一个SQL语句已经被解析过，那么就不会生成10053的trace文件，但10046的trace文件可以重复生成。
简而言之，10046事件记录SQL如何运行，而10053记录优化器为什么为这个SQL选择某个执行计划。

在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的跟踪:

此时的SQL操作将被跟踪:

结束跟踪:

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

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

设置跟踪SID为26的会话:

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

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。