Oracle 11g R2性能优化 10046 event

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了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}。

会话级别参数设置：

[email protected]> alter session set tracefile_identifier = '10046_SCOTT';
Session altered.

[email protected]> alter session set timed_statistics = true;
Session altered.

[email protected]> alter session set statistics_level = all;
Session altered.

[email protected]> alter session set max_dump_file_size = unlimited;
Session altered.
  • 开启10046 event跟踪：
-- 这里开启级别为12
[email protected]> alter session set events '10046 trace name context forever,level 12';
Session altered.
  • 执行需要跟踪语句：
-- 以查询scott.dept表为例
[email protected]> select * from dept;

    DEPTNO DNAME                                      LOC
---------- ------------------------------------------ ---------------------------------------
        10 ACCOUNTING                                 NEW YORK
        20 RESEARCH                                   DALLAS
        30 SALES                                      CHICAGO
        40 OPERATIONS                                 BOSTON
[email protected]> alter session set events '10046 trace name context off';
Session altered.
或
[email protected]> 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 <> ' ';

-- 输出
[email protected]> column line format a80
[email protected]> set heading off
[email protected]> 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 [email protected] (TNS V1-V3)
ospid: 9769 # '138,365' oracle dbabd SCOTT [email protected] (TNS V1-V3)
ospid: 24668 # '18,9' oracle dbabd SYS [email protected] (TNS V1-V3)

一般通过操作系统进程就可以确定跟踪会话，也可以通过Oracle pid来跟踪。通过以上脚本也可以获取到会话id(session id)，这里以SCOTT用户为例，SCOTT用户的sid为138。

-- 脚本
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

-- 输出
[email protected]> select p.PID,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连接进程
[email protected]> oradebug setospid 9769
Oracle pid: 25,Unix process pid: 9769,image: [email protected] (TNS V1-V3)
或
[email protected]> oradebug setorapid 25
Oracle pid: 25,image: [email protected] (TNS V1-V3)
  • 配置trace文件的标识符
[email protected]> oradebug settracefileid 10046_SCOTT_ORADEBUG;
Statement processed.
  • 开启10046 event跟踪
[email protected]> oradebug event 10046 trace name context forever,level 12;
Statement processed.
  • SCOTT用户模拟执行语句
[email protected]> select * from dept;

    DEPTNO DNAME                                      LOC
---------- ------------------------------------------ ---------------------------------------
        10 ACCOUNTING                                 NEW YORK
        20 RESEARCH                                   DALLAS
        30 SALES                                      CHICAGO
        40 OPERATIONS                                 BOSTON
[email protected]> oradebug event 10046 trace name context off;
Statement processed.
[email protected]> oradebug close_trace;
Statement processed.
[email protected]> 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。

[email protected]> oradebug setospid 9769
Statement processed.
[email protected]> oradebug settracefileid 10046_SCOTT_DBMSSYSTEM;
Statement processed.
  • 开启10046 event跟踪：
[email protected]> exec dbms_system.set_ev(138,365,10046,12,'');
PL/sql procedure successfully completed.
[email protected]> exec dbms_system.set_ev(138,'');
PL/sql procedure successfully completed.
或
[email protected]> exec dbms_system.set_sql_trace_in_session(138,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。

[email protected]> oradebug setospid 9769
Statement processed.
[email protected]> oradebug settracefileid 10046_SCOTT_DBMSMONITOR;
Statement processed.
  • 开启10046 event跟踪
[email protected]> exec dbms_monitor.session_trace_enable(138,true,'all_executions');
PL/sql procedure successfully completed.
[email protected]> 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包信息
[email protected]> desc dbms_support;
ERROR:
ORA-04043: object dbms_support does not exist

默认没有安装，提示未找到。

  • 安装DBMS_SUPPORT包
[email protected]> @?/rdbms/admin/dbmssupp.sql
Package created.
Package body created.

[email protected]> grant execute on dbms_support to schema_owner;  -- 授权执行权限给需要用户，如SCOTT

[email protected]> create public synonym dbms_support for dbms_support;  -- 创建同名的同义词
Synonym created.
  • 查看DBMS_SUPPORT包
[email protected]> 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
[email protected]> select dbms_support.mysid from dual;

     MYSID
----------
        12

-- 查询包版本
[email protected]> select dbms_support.package_version from dual;

PACKAGE_VERSION
--------------------------------------------------------------------------------
DBMS_SUPPORT Version 1.0 (17-Aug-1998) - Requires Oracle 7.2 - 8.0.5
[email protected]> oradebug setospid 9769
Statement processed.
[email protected]> oradebug settracefileid 10046_SCOTT_DBMSSUPPORT;
Statement processed.
  • 开启10046 event跟踪
[email protected]> exec dbms_support.start_trace_in_session(138,true);
PL/sql procedure successfully completed.
[email protected]> exec dbms_support.stop_trace_in_session(138,365);
PL/sql procedure successfully completed.

数据库级别开启

10046 event跟踪也可以在数据库级别开启，但通常很少会这样用，因为会导致所有会话都被跟踪而影响整体性能。当动态开启整个数据库的跟踪时，会对之后创建的每个会话进行跟踪，而已经存在的会话不会被跟踪。

数据库级别的10046 event跟踪通常用于当提前知道会话会出现性能问题，但却无法明确具体是哪个会话时。

当问题会话被定位以及跟踪之后，确认收集了相关的跟踪信息就可以将数据库级别的跟踪进行关闭，因为这会影响系统整体的性能。开启数据库级别跟踪的用户需要有sysdba的权限，动态开启可以使用以下方式：

  • 语句开启10046 event跟踪
[email protected]> 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跟踪
[email protected]> exec dbms_monitor.database_trace_enable(true,'dbabd','all_executions');
PL/sql procedure successfully completed.
  • DBMS_MONITOR包关闭10046 event跟踪
[email protected]> 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

☆〖本人水平有限，文中如有错误还请留言批评指正！〗☆

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问