Oracle 11g R2性能优化 10046 event
正文
作为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 <ospid> set os pid of process to debug -- 通过操作系统的进程 setorapid <orapid> ['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
http://www.juliandyke.com/diagnostics/tools/oradebug/oradebug.php
☆〖本人水平有限,文中如有错误还请留言批评指正!〗☆
上一篇: 分享两种实现Winform程序的多语言支持的解决方案
下一篇: 端午节图片,屈原,粽子,赛龙舟