目录 主要说明 常用使用方式 会话级别开启 ORADEBUG开启 DBMS_SYSTEM包开启 DBMS_MONITOR包开启 DBMS_SUPPORT包开启 数据库级别开启 总结 参考 正文 作为SQL Trace的扩展功能，Oracle 10046 event(10046事件)是一个重要的调试事件，也可以说是系统性能分析时最重要的一个事件，它包含比SQL Trace更多的信息。但可惜的是，10046事件并不是Oracle官方提供给用户的诊断工具，官方文档上面也没有详细的说明，不过还是可以从很多国内外专家技术文档找到相关学习资料，本文主要参考Oracle MOS文档来学习总结下10046事件的用法。 关于SQL Trace的收集方式可以参考另一篇博文：Oracle 11g R2性能优化 SQL TRACE 关于10046 event trace的收集方式可以参考Oracle MOS文档：How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (文档 ID 376442.1) 回到顶部 主要说明 10046 event根据跟踪的信息详细程度可以分成不同的等级，高级别向低级别兼容，即级别高的跟踪信息包含级别低的信息。 主要级别说明如下表所示： 级别(Level) 说明(Description) 0 不生成跟踪统计信息，相当于SQL_TRACE=FALSE 1 生成标准跟踪统计信息，相当于SQL_TRACE=TRUE，为默认级别 2 与级别1相同 4 级别1基础上 + 绑定变量跟踪 8 级别1基础上 + 等待事件跟踪 12 级别4 + 级别8 16 级别1基础上 + 每次执行时写入执行计划信息(11g新增) 通常使用的级别为12，10046 event跟踪信息可以基本满足分析的需求。关于级别的详细说明可以参考国外大神Christian Antognini的博文：Event 10046 – Full List of Levels 回到顶部 常用使用方式 回到顶部 会话级别开启 根据参考的MOS文档描述，会话级别开启10046 event跟踪之前需设置几个会话级参数选项，如下： tracefile_identifier 设置生成trace文件的标识符。 statistics_level 指定数据库和操作系统统计信息的收集级别，取值为{typical|all|basic}。 timed_statistics 指定是否收集与时间相关的统计信息，当statistics_level参数值为typical或all时默认值为true，当statistics_level参数值为basic时默认值为false。 max_dump_file_size 指定trace文件(包括告警文件)的最大值，默认为unlimited，取值为{integer [K|M|G] | unlimited}。 会话级别参数设置： SCOTT@dbabd> alter session set tracefile_identifier = '10046_SCOTT'; Session altered. SCOTT@dbabd> alter session set timed_statistics = true; Session altered. SCOTT@dbabd> alter session set statistics_level = all; Session altered. SCOTT@dbabd> alter session set max_dump_file_size = unlimited; Session altered. 开启10046 event跟踪： -- 这里开启级别为12 SCOTT@dbabd> alter session set events '10046 trace name context forever,level 12'; Session altered. 执行需要跟踪语句： -- 以查询scott.dept表为例 SCOTT@dbabd> select * from dept; DEPTNO DNAME LOC ---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 关闭10046 event跟踪： SCOTT@dbabd> alter session set events '10046 trace name context off'; Session altered. 或 SCOTT@dbabd> exit; 回到顶部 ORADEBUG开启 oradebug是一个SQL*PLUS命令行工具，它可以跟踪会话、dump内存结构、唤醒进程等。同时，oradebug也可以用来开启10046事件跟踪，并且它可以直接显示trace文件的路径，想要使用oradebug需要有sysdba权限。 关于oradebug详细说明可以参考：ORADEBUG introduction 在oradebug工作之前，必须确定连接的进程号，oradebug主要有如下连接进程方式： SETMYPID Debug current process -- 跟踪当前会话进程 SETOSPID Set OS pid of process to debug -- 通过操作系统的进程 SETORAPID ['force'] Set Oracle pid of process to debug -- 通过Oracle的进程 如果无法获取OS pid或者Oracle pid，可以通过如下脚本获取： 获取OS pid和会话id(session id) -- 脚本 column line format a80 set heading off select 'ospid: ' || p.spid || ' # ''' || s.sid || ',' || s.serial# || ''' ' || s.osuser || ' ' || s.machine || ' ' || s.username || ' ' || s.program line from v$session s, v$process p where p.addr = s.paddr and s.username <> ' '; -- 输出 SYS@dbabd> column line format a80 SYS@dbabd> set heading off SYS@dbabd> select 'ospid: ' || p.spid || ' # ''' || s.sid || ',' || s.serial# || ''' ' || 2 s.osuser || ' ' || s.machine || ' ' || s.username || ' ' || 3 s.program line 4 from v$session s, v$process p 5 where p.addr = s.paddr 6 and s.username <> ' '; ospid: 24561 # '12,29' oracle dbabd SYS sqlplus@dbabd (TNS V1-V3) ospid: 9769 # '138,365' oracle dbabd SCOTT sqlplus@dbabd (TNS V1-V3) ospid: 24668 # '18,9' oracle dbabd SYS sqlplus@dbabd (TNS V1-V3) 一般通过操作系统进程就可以确定跟踪会话，也可以通过Oracle pid来跟踪。通过以上脚本也可以获取到会话id(session id)，这里以SCOTT用户为例，SCOTT用户的sid为138。 获取Oracle pid -- 脚本 select p.PID, p.SPID, s.SID from v$process p, v$session s where s.paddr = p.addr and s.sid = &SESSION_ID; -- 输入上述脚本得到的sid，如138 -- 输出 SYS@dbabd> select p.PID, p.SPID, s.SID 2 from v$process p, v$session s 3 where s.paddr = p.addr 4 and s.sid = &SESSION_ID; Enter value for session_id: 138 old 4: and s.sid = &SESSION_ID new 4: and s.sid = 138 25 9769 138 确定oradebug连接进程 SYS@dbabd> oradebug setospid 9769 Oracle pid: 25, Unix process pid: 9769, image: oracle@dbabd (TNS V1-V3) 或 SYS@dbabd> oradebug setorapid 25 Oracle pid: 25, Unix process pid: 9769, image: oracle@dbabd (TNS V1-V3) 配置trace文件的标识符 SYS@dbabd> oradebug settracefileid 10046_SCOTT_ORADEBUG; Statement processed. 开启10046 event跟踪 SYS@dbabd> oradebug event 10046 trace name context forever, level 12; Statement processed. SCOTT用户模拟执行语句 SCOTT@dbabd> select * from dept; DEPTNO DNAME LOC ---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 关闭10046 event跟踪 SYS@dbabd> oradebug event 10046 trace name context off; Statement processed. 关闭trace文件 SYS@dbabd> oradebug close_trace; Statement processed. 查询当前trace文件路径 SYS@dbabd> oradebug tracefile_name; /data/app/oracle/diag/rdbms/dbabd/dbabd/trace/dbabd_ora_9769_10046_SCOTT_ORADEBUG.trc 回到顶部 DBMS_SYSTEM包开启 通过DBMS_SYSTEM包也可以开启10046 event跟踪，主要是包中存储过程SET_EV来完成，查看存储过程所需的参数选项： PROCEDURE SET_EV Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SI BINARY_INTEGER IN -- SID SE BINARY_INTEGER IN -- SERIAL# EV BINARY_INTEGER IN -- 事件代码，如10046 LE BINARY_INTEGER IN -- 事件级别，如12 NM VARCHAR2 IN 通过以上参数选项分析，采用DBMS_SYSTEM包的方式最少需要获取SID，获取SID可以参考oradebug当中的脚本，这里不再重复说明，为了方便查找trace文件，也可以事先配置trace文件的标识符，还是以SCOTT用户为例，trace文件标识符为10046_SCOTT_DBMSSYSTEM。 配置trace文件标识符 SYS@dbabd> oradebug setospid 9769 Statement processed. SYS@dbabd> oradebug settracefileid 10046_SCOTT_DBMSSYSTEM; Statement processed. 开启10046 event跟踪： SYS@dbabd> exec dbms_system.set_ev(138,365,10046,12,''); PL/SQL procedure successfully completed. 关闭10046 event跟踪： SYS@dbabd> exec dbms_system.set_ev(138,365,10046,0,''); PL/SQL procedure successfully completed. 或 SYS@dbabd> exec dbms_system.set_sql_trace_in_session(138,365,false); PL/SQL procedure successfully completed. 回到顶部 DBMS_MONITOR包开启 DBMS_MONITOR包也可以开启10046 event跟踪，通地包中存储过程SESSION_TRACE_ENABLE来完成。从本文前面部分的说明也可以了解到，级别为12的10046 event跟踪就是包含绑定变量与等待事件信息。关于存储过程SESSION_TRACE_ENABLE选项说明可以参考另一篇博文：DBMS_MONITOR包跟踪，这里以SCOTT用户为例，trace文件标识符为10046_SCOTT_DBMSMONITOR。 配置trace文件标识符 SYS@dbabd> oradebug setospid 9769 Statement processed. SYS@dbabd> oradebug settracefileid 10046_SCOTT_DBMSMONITOR; Statement processed. 开启10046 event跟踪 SYS@dbabd> exec dbms_monitor.session_trace_enable(138,365,true,true,'all_executions'); PL/SQL procedure successfully completed. 关闭10046 event跟踪 SYS@dbabd> exec dbms_monitor.session_trace_disable(138,365); PL/SQL procedure successfully completed. 回到顶部 DBMS_SUPPORT包开启 还有一个DBMS_SUPPORT包可以开启10046 event跟踪。不过这个包的说明在官方文档中没有找到，默认没有安装，可以通过查看Oracle MOS文档安装：How to Install the DBMS_SUPPORT Package (文档 ID 377204.1)，关于包的说明也可以参考文档：The DBMS_SUPPORT Package (文档 ID 62294.1)。这里以SCOTT用户为例，trace文件标识符为10046_SCOTT_DBMSSUPPORT。 查看DBMS_SUPPORT包信息 SYS@dbabd> desc dbms_support; ERROR: ORA-04043: object dbms_support does not exist 默认没有安装，提示未找到。 安装DBMS_SUPPORT包 SYS@dbabd> @?/rdbms/admin/dbmssupp.sql Package created. Package body created. SYS@dbabd> grant execute on dbms_support to schema_owner; -- 授权执行权限给需要用户，如SCOTT SYS@dbabd> create public synonym dbms_support for dbms_support; -- 创建同名的同义词 Synonym created. 查看DBMS_SUPPORT包 SYS@dbabd> desc dbms_support; FUNCTION MYSID RETURNS NUMBER -- 查询当前会话的SID FUNCTION PACKAGE_VERSION RETURNS VARCHAR2 -- 查询包版本 PROCEDURE START_TRACE -- 开启当前会话跟踪 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- WAITS BOOLEAN IN DEFAULT BINDS BOOLEAN IN DEFAULT PROCEDURE START_TRACE_IN_SESSION -- 开启其他会话跟踪 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SID NUMBER IN -- 对应SID SERIAL NUMBER IN -- 对应SERIAL# WAITS BOOLEAN IN DEFAULT -- 对应是否加入等待事件跟踪 BINDS BOOLEAN IN DEFAULT -- 对应是否加入绑定变量跟踪 PROCEDURE STOP_TRACE -- 关闭当前会话跟踪 PROCEDURE STOP_TRACE_IN_SESSION -- 关闭其他会话跟踪 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SID NUMBER IN SERIAL NUMBER IN -- 查询当前会话的SID SYS@dbabd> select dbms_support.mysid from dual; MYSID ---------- 12 -- 查询包版本 SYS@dbabd> select dbms_support.package_version from dual; PACKAGE_VERSION -------------------------------------------------------------------------------- DBMS_SUPPORT Version 1.0 (17-Aug-1998) - Requires Oracle 7.2 - 8.0.5 配置trace文件标识符 SYS@dbabd> oradebug setospid 9769 Statement processed. SYS@dbabd> oradebug settracefileid 10046_SCOTT_DBMSSUPPORT; Statement processed. 开启10046 event跟踪 SYS@dbabd> exec dbms_support.start_trace_in_session(138,365,true,true); PL/SQL procedure successfully completed. 关闭10046 event跟踪 SYS@dbabd> exec dbms_support.stop_trace_in_session(138,365); PL/SQL procedure successfully completed. 回到顶部 数据库级别开启 10046 event跟踪也可以在数据库级别开启，但通常很少会这样用，因为会导致所有会话都被跟踪而影响整体性能。当动态开启整个数据库的跟踪时，会对之后创建的每个会话进行跟踪，而已经存在的会话不会被跟踪。 数据库级别的10046 event跟踪通常用于当提前知道会话会出现性能问题，但却无法明确具体是哪个会话时。 当问题会话被定位以及跟踪之后，确认收集了相关的跟踪信息就可以将数据库级别的跟踪进行关闭，因为这会影响系统整体的性能。开启数据库级别跟踪的用户需要有sysdba的权限，动态开启可以使用以下方式： 语句开启10046 event跟踪 SYS@dbabd> alter system set events '10046 trace name context forever,level 12'; System altered. 语句关闭10046 event跟踪 alter system set events '10046 trace name context off'; System altered. DBMS_MONITOR包也可以开启数据库级别的10046 event跟踪，主要由DATABASE_TRACE_DISABLE和DATABASE_TRACE_ENABLE存储过程来完成。关于这两个存储过程的参数选项说明如下： PROCEDURE DATABASE_TRACE_ENABLE -- 开启 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- WAITS BOOLEAN IN DEFAULT -- 对应是否加入等待事件跟踪 BINDS BOOLEAN IN DEFAULT -- 对应是否加入绑定变量跟踪 INSTANCE_NAME VARCHAR2 IN DEFAULT -- 实例名，如dbabd PLAN_STAT VARCHAR2 IN DEFAULT PROCEDURE DATABASE_TRACE_DISABLE -- 关闭 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- INSTANCE_NAME VARCHAR2 IN DEFAULT -- 实例名，如dbabd DBMS_MONITOR包开启10046 event跟踪 SYS@dbabd> exec dbms_monitor.database_trace_enable(true,true,'dbabd','all_executions'); PL/SQL procedure successfully completed. DBMS_MONITOR包关闭10046 event跟踪 SYS@dbabd> exec dbms_monitor.database_trace_disable('dbabd'); PL/SQL procedure successfully completed. 回到顶部 总结 以上总结了主要的几种开启10046 event跟踪的方法，但还是有很多方面并未涉及，包括多会话跟踪、指定sql_id过滤跟踪等，这些跟踪涉及得更为细致，对于具体问题的分析更为有效，日后也会进行总结梳理。对于10046 event产生的trace文件，同样也可以通过tkprof工具进行格式化后增加可阅读性，关于tkprof工具的主要使用方式也可参考另一篇博文：Oracle 11g R2性能优化 tkprof 回到顶部 参考 How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (文档 ID 376442.1) The DBMS_SUPPORT Package (文档 ID 62294.1) Oracle TKPROF & SQL_TRACE https://antognini.ch/2012/08/event-10046-full-list-of-levels/ http://www.juliandyke.com/Diagnostics/Tools/ORADEBUG/ORADEBUG.php https://www.cnblogs.com/youngerger/p/8541885.html https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof ☆〖本人水平有限，文中如有错误还请留言批评指正！〗☆ 作者: H_Johnny 出处: http://www.cnblogs.com/dbabd/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接。 如果您觉得文章不错，请帮忙点击右下角的推https://www.cnblogs.com/dbabd/p/10319479.html