Oracle的10046事件详解

DBA小记 2020-10-27

3306

10046事件是Oracle提供的内部事件，是增强版的SQL_TRACE。

一、10046的级别

不同的Level对应不同的跟踪级别：

0级：SQL_TRACE=FASLE。

1级：SQL_TRACE=TRUE，默认级别。启用标准的SQL_TRACE功能 ( 默认) 包含了 SQL语句、响应时间、服务时间、处理的行数，物理读和写的数目、执行计划以及其他一些额外信息。到版本10.2中执行计划写入到 trace 的条件是仅当相关游标已经关闭时，且与之相关的执行统计信息是所有执行次数的总和数据。到版本11.1中仅在每次游标的第一次执行后将执行计划写入到trace ，执行统计信息仅仅和这第一次执行相关。

4级：比level 1时多出绑定变量的trace。

8级：比level 1多出等待事件，特别对于9i中指出 latch free等待事件很有用，对于分析全表扫描和索引扫描很有用。

12级：4级+8级，比level 1多出绑定变量和等待事件。

16级：在11g中为每一次执行生成STAT信息，仅在11.1之后可用

32级：比level1少执行计划

64级：和level1相比在第一次执行后还可能生成执行计划信息；条件是某个游标在前一次执行的前提下运行耗时变长了一分钟。仅在 11.2.0.2中可用。

Level 28 (4+8+16) 代表同时启用level4、level8、level16。

level 68 ( 64+4 )代表同时启用level64、level4

对于4级的10046，若用tkprof格式化，则会隐藏每一点SQL语句在做什么以及怎么做。

对于8级的10046，等待事件得到的比较多，此时我们不妨用tkprof对等待事件进行汇总。

所以，理解扩展SQL跟踪文件的格式，是每一个面临性能问题或故障排除任务的数据库工程师必备技能。

二、10046 trace文件路径

从11gR1开始，Oracle引入了新的诊断结构，以参数DIAGNOSTIC_DEST控制存放trace文件与core文件的路径。可以用以下命令，获取DIAGNOSTIC_DEST的位置：

SQL> show parameter diagnostic_dest;
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                  string       oracle/app/oracle

11gR1以前，如果是用户进程，10046 trace文件会被生成在user_dump_dest下，如果是后台进程，trace文件会被生成在background_dump_dest下：

SQL> show parameter user_dump_dest ;
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string       oracle/app/oracle/diag/rdbms/xddb/xddb/trace
SQL> show parameter background_dump_dest;
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string       oracle/app/oracle/diag/rdbms/xddb/xddb/trace

注：

通过设定tracefile_identifier即alter session set tracefile_identifier='my_trace' ，通过这个设置可以帮助我们更容易的找到生成的trace文件。

通过SQL查看当前session的trace文件位置：

select a.value || '/' || b.instance_name || '_ora_' || c.spid || '.trc' trace_file
  from (select value from v$parameter where name = 'user_dump_dest') a,
       (select instance_name from v$instance) b,
       (select spid
          from v$process
         where addr =
               (select paddr
                  from v$session
                 where sid = (select distinct sid from v$mystat))) c;
或：
select tracefile
  from v$process
 where addr = (select paddr
                 from v$session
                where sid = (select distinct sid from v$mystat));

三、10046 trace相关参数

1.timed_statistics

timed_statistics这个参数决定了是否收集与时间相关的统计信息，如果这个参数为FALSE的话，那么SQL Trace的结果基本没有多大的用处，默认情况下这个参数设置为TRUE。

SQL> show parameter timed_statistics ;
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                 boolean       TRUE
SQL> ALTER SESSION SET timed_statistics=true ;

2.max_dump_file_size

max_dump_file_size这个参数指定dump文件的大小，也就是决定是否限制SQL Trace文件的大小，在一个很忙的系统上面做SQL Trace的话可能会生成很多的信息，因此最好在会话级别将这个参数设置成unlimited（默认）。

SQL> show parameter max_dump_file_size;
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                 string       unlimited
SQL> ALTER SESSION SET max_dump_file_size=unlimited ;

3. tracefile_identifier

tracefile_identifier这个参数给Trace文件设置识别字符串，设置一个易读的字串能更快的找到Trace文件。

SQL> show parameter tracefile_identifier;

NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
tracefile_identifier                 string
当前session启用：
SQL> ALTER SESSION SET tracefile_identifier=’my_trace’;

4. diagnostic_dest

diagnostic_dest这个参数11g新增的，用于控制存放trace文件与core文件的路径，默认是$ORACLE_BASE目录。

SQL> show parameter diagnostic_dest;
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                  string       oracle/app/oracle

5. user_dump_dest

user_dump_dest参数指定用户进程trace文件目录。

SQL> show parameter user_dump_dest ;
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string       oracle/app/oracle/diag/rdbms/xddb/xddb/trace 
SQL> alter system set user_dump_dest ='/oracle/app/oracle/diag/rdbms/xddb/xddb/trace' scope=both; 更改其目录

6. background_dump_dest

background_dump_dest参数用于后台进程产生的trace文件存储目录下。

SQL> show parameter background_dump_dest ;
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string       oracle/app/oracle/diag/rdbms/xddb/xddb/trace
SQL> alter system set
background_dump_dest='/oracle/app/oracle/diag/rdbms/xddb/xddb/trace' scope=both; 更改其目录

四、10046 trace设置方法

4.1全局设置

在参数文件中增加:

event="10046 trace name context forever,level 12"

此设置对所有用户的所有进程生效、包括后台进程。

如果要关闭可以通过移除这个参数并且重启实例，或者使用下面的alter system命令可以关闭跟踪。

 SQL> alter system set events '10046 trace name context off';

4.2实例级别跟踪

在实例级别设置跟踪需要非常小心，这是因为整体性能会由于所有session都被跟踪而受到影响，这个设置将会跟踪在这个参数设置“以后”创建的每个session。已经存在的session不会被跟踪，系统层的10046跟踪适用于当我们知道问题session会出现，但是不能预先识别它的时候，在这种情况下，可以打开系统层跟踪小段时间，当问题被重现以后立即将其关闭，然后从已经生成的trace中查找需要的信息，用以下命令打开系统层的跟踪：

 SQL> alter system set events '10046 trace name context forever,level 12';
 用以下命令关闭在所有session中的10046跟踪：
 SQL> alter system set events '10046 trace name context off';

4.3对当前session设置

（1）语法：

alter session set events '[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : .......'

（2）通过alter session的方式修改，需要alter session的系统权限:

SQL> alter session set tracefile_identifier='my_trace_10046';       --trace文件标识
SQL> alter session set statistics_level=all;               --实例级别设置需要注意，会消耗大量的CPU
SQL> alter session set timed_statistics = true;      --默认是true
SQL> alter session set max_dump_file_size = unlimited;       --默认是unlimited
SQL> alter session set events '10046 trace name context forever, level 12'; --开启level 12级别的10046事件。
Session altered.
SQL> select count(*) from test.test;
  COUNT(*)
----------
    227626
SQL> alter session set events '10046 trace name context off';
Session altered.

（3）查看session sql trace文件

SQL> select tracefile
  from v$process
 where addr = (select paddr
                 from v$session
                where sid = (select distinct sid from v$mystat));
TRACEFILE
--------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_6888_my_trace_10046.trc

4.4对其他用户session设置

4.4.1通过DBMS_SYSTEM.SET_EV系统包来实现

SQL> desc dbms_system
...
PROCEDURE SET_EV
 Argument Name                  Type                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SI                        BINARY_INTEGER            IN
 SE                        BINARY_INTEGER            IN
 EV                        BINARY_INTEGER            IN
 LE                        BINARY_INTEGER            IN
 NM                        VARCHAR2            IN
...

其中的参数SI（SID）、SE（SERIAL#）、NM（username）来自v$session视图:

SQL> select sid,serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
       5      52733 TEST
    492       4299 SYS
执行跟踪:
SQL> exec dbms_system.set_ev(5,52733,10046,12,'');
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(5,52733,true); --有时候需要提前开启SQL_TRACE才可以生成trace文件。
PL/SQL procedure successfully completed

测试执行SQL语句：

SQL> select * from tab;
TNAME                         TABTYPE      CLUSTERID
------------------------------ ------- ----------
COURSE                         TABLE
SC                         TABLE
STUDENT                    TABLE
TEACHER                    TABLE
TEST                         TABLE
5 rows selected.
SQL> select * from course;
      C_ID C_NAME                          T_ID
---------- -------------------------------- ----------
       1 语文                              1
       2 数学                              2
       3 英语                              3
       4 物理                              4

结束跟踪：

SQL> exec dbms_system.set_sql_trace_in_session(5,52733,false);
SQL> exec dbms_system.set_ev(5,52733,10046,0,'');
PL/SQL procedure successfully completed.

获取trace文件：

SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=5);

读取当前session设置的参数：

declare lev integer;
 begin
   dbms_system.read_ev('10046',lev);
   dbms_output.put_line(lev);
 end;
/

针对所有event：

SQL> set feedback off
SQL> set serveroutput on
SQL> declare
event_level number;
begin
for event_number in 10000..10999 loop
sys.dbms_system.read_ev(event_number, event_level);
if (event_level > 0) then
sys.dbms_output.put_line(
'Event ' ||
to_char(event_number) ||
 ' is set at level ' ||
 to_char(event_level)
 );
 end if;
 end loop;
 end;

4.4.2通过DBMS_MONITOR.SESSION_TRACE_ENABLE实现

PROCEDURE SESSION_TRACE_ENABLE
 Argument Name                     Type                     In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSION_ID                     BINARY_INTEGER              IN     DEFAULT
 SERIAL_NUM                     BINARY_INTEGER              IN     DEFAULT
 WAITS                            BOOLEAN               IN     DEFAULT
 BINDS                            BOOLEAN               IN     DEFAULT
 PLAN_STAT                     VARCHAR2              IN     DEFAULT
-- 实例

-- 开启级别为12的Trace，当前会话
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>true,BINDS=>true);


-- 开启级别为12的Trace，其他会话
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(:sid, :serial, true, true);


-- 关闭Trace，当前会话
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE();
-- 关闭Trace，其他会话
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(:sid, :serial);

4.4.3 10046事件进程级别跟踪

假设需要对9834号进程做跟踪，其语法如下所示：

conn  as sysdba
alter session set tracefile_identifier = 'trace_10046'
oradebug setospid=9834
oradebug unlimit
oradebug evnet 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug tracefile_name
oradebug evnet 10046 trace name context off

五、分析trace文件

5.1 可直接查看

more oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_6888_my_trace_10046.trc

trace 文件可以看出 sql 语句经过了 parse(解析) -> exec(执行) -> fetch(从游标中获取数据) 几个过程。这3个分类与通过调用DBMS_SQL的子例程DBMS_SQL.PARSE,DBMS_SQL.EXECUTE,DBMS_SQL.FETCH_ROWS来跑SQL的步调相一致。

5.2 tkprof工具查看

tkprof /oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_6888_my_trace_10046.trc  tkprof_10046.txt


SQL ID: br5bhw1m4892h Plan Hash: 1950795681
select count(*)
from
 test.test
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.14       0.22       3135       3139          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.14       0.24       3135       3139          0           1


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=3139 pr=3135 pw=0 time=227352 us)
    227626     227626     227626   TABLE ACCESS FULL TEST (cr=3139 pr=3135 pw=0 time=684591 us cost=855 size=0 card=227626)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                         40        0.01          0.10
  SQL*Net message from client                     2       12.47         12.47
********************************************************************************

oracle

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。