10053和10046事件用法简单介绍 -m6米乐安卓版下载

我们在做sql优化的时候会用到10046和10053跟踪诊断，当然其他场景也会用到。今天在读《dba攻坚指南》的时候又看到了10053的知识，这里对这两个做个总结。

1.1.10046事件介绍

10046事件是oracle提供的内部跟踪事件，是对sql_trace的增强，通过10046可以通知oracle内核执行sql_trace类的跟踪操作。
如果我们需要获得更多的跟踪信息，就需要用到10046事件，而在实际工作中最常用的就是10046事件。

1.2.10046事件跟踪级别

• 1 - 启用标准的sql_trace功能,等价于sql_trace
• 4 - level 1 加上绑定值(bind values) [ bind=true ]
• 8 - level 1 等待事件跟踪 [ wait=true ]
• 12 - level 1 level 4 level 8
  从11g开始增加了以下两个跟踪级别。
• 16 – 为每次sql执行生成stat信息输出 [ plan_stat=all_executions ]
• 32 – 不转储执行统计信息 [ plan_stat=never ]
  从 11.2.0.2开始增加以下级别。
• 64 – 自适应的stat转储 [ plan_stat=adaptive ]

1.3.启用和停止

--启用跟踪
sql> alter session set events '10046 trace name context forever, level 12';
--停止跟踪
sql> alter session set events '10046 trace name context off'; 
--11g之后的可选设置方式类似。
alter session set events 'sql_trace wait=true';
--对其他session设置跟踪，可以通过dbms_system的set_ev过程来实现
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、se来自v$session视图
sql> select sid,serial#,username from v$session where username is not null;
       sid    serial# username
---------- ---------- ------------------------------
         8       2041 sys
         9        437 eygle
--执行跟踪
sql> exec dbms_system.set_ev(9,437,10046,8,'');
pl/sql procedure successfully completed.
--结束跟踪
sql> exec dbms_system.set_ev(9,437,10046,0,'');
pl/sql procedure successfully completed.

1.4.产生的跟踪文件

基于会话级别跟踪产生的文件，可以通过查询v$diag_info视图，找到跟踪文件的名称和位置信息，查看其中的内容。

select value trace_file from v$diag_info where name='default trace file';
--或者
show parameter user_dump_dest

1.5.对生成的trc文件进行格式化

tkprof

[oracle@single ~]$ tkprof
usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   use 'schema.tablename' with 'explain=' option.
  explain=user/password    connect to oracle and issue explain plan.
  print=integer    list only the first 'integer' sql statements.
  aggregate=yes|no
  insert=filename  list sql statements and data inside insert statements.
  sys=no           tkprof does not list sql statements run as user sys.
  record=filename  record non-recursive statements found in the trace file.
  waits=yes|no     record summary for any wait events found in the trace file.
  sort=option      set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor
[oracle@single ~]$

1.6.示例

我们就用select * from scott.dept;这个作为示例，来看看它详细的执行计划。

sql> alter session set events '10046 trace name context forever,level 8';
session altered.
sql> select * from scott.dept;
    deptno dname	  loc
---------- -------------- -------------
	10 accounting	  new york
	20 research	  dallas
	30 sales	  chicago
	40 operations	  boston
sql> alter session set events '10046 trace name context off';
session altered.
sql> select value trace_file from v$diag_info where name='default trace file';
trace_file
--------------------------------------------------------------------------------
/data/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1853.trc
sql> exit
disconnected from oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production
with the partitioning, olap, data mining and real application testing options
[oracle@single ~]$ cd /data/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@single trace]$ tkprof orcl_ora_1853.trc 10046.txt
tkprof: release 11.2.0.4.0 - development on fri may 6 21:09:19 2022
米乐app官网下载 copyright (c) 1982, 2011, oracle and/or its affiliates.  all rights reserved.
[oracle@single trace]$
[oracle@single trace]$ cat 10046.txt 
tkprof: release 11.2.0.4.0 - development on fri may 6 21:09:19 2022
米乐app官网下载 copyright (c) 1982, 2011, oracle and/or its affiliates.  all rights reserved.
trace file: orcl_ora_1853.trc
sort options: default
********************************************************************************
count    = number of times oci procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
sql id: f6hhpzwv5jrna plan hash: 3383998547
select * 
from
 scott.dept
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        1      0.07       0.07          0          0          0           0
execute      1      0.00       0.00          0          0          0           0
fetch        2      0.00       0.00          0          7          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.07       0.07          0          7          0           4
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
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  table access full dept (cr=7 pr=0 pw=0 time=448 us cost=3 size=108 card=6)
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
  sql*net message from client                     2        8.05          8.05
********************************************************************************
sql id: 06nvwn223659v plan hash: 0
alter session set events '10046 trace name context off'
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        1      0.00       0.00          0          0          0           0
execute      1      0.00       0.00          0          0          0           0
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0
misses in library cache during parse: 0
parsing user id: sys
********************************************************************************
overall totals for all non-recursive statements
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        2      0.07       0.07          0          0          0           0
execute      2      0.00       0.00          0          0          0           0
fetch        2      0.00       0.00          0          7          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.07       0.07          0          7          0           4
misses in library cache during parse: 1
elapsed times include waiting on following events:
  event waited on                             times   max. wait  total waited
  ----------------------------------------   waited  ----------  ------------
  sql*net message to client                       3        0.00          0.00
  sql*net message from client                     3        8.05         15.55
overall totals for all recursive statements
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        0      0.00       0.00          0          0          0           0
execute      0      0.00       0.00          0          0          0           0
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0
misses in library cache during parse: 0
    2  user  sql statements in session.
    0  internal sql statements in session.
    2  sql statements in session.
********************************************************************************
trace file: orcl_ora_1853.trc
trace file compatibility: 11.1.0.7
sort options: default
       1  session in tracefile.
       2  user  sql statements in trace file.
       0  internal sql statements in trace file.
       2  sql statements in trace file.
       2  unique sql statements in trace file.
      50  lines in trace file.
       8  elapsed seconds in trace file.
[oracle@single trace]$ 

2.1.10053事件介绍

当一个sql出现性能问题时，可以使用sql_trace或10046事件来跟踪sql，通过生成的跟踪信息来了解sql的执行过程。
当我们查看一条sql的执行计划时，只能看到cbo最终显示的执行计划结果，却不知道cbo是根据什么来做的。
如果遇到了执行计划异常，则可以借助oracle 10053事件进行跟踪。
10053事件是oracle提供的用于跟踪sql语句成本计算的内部事件，它能记载cbo模式下，oracle优化器是如何计算sql成本，并生成相应的执行计划的。
注意：10053只对cbo有效，而且如果一个sql语句已经解析过，就不会产生新的跟踪信息。

2.2.启用和停止

--启用
alter session set events='10053 trace name context forever,level 1';
or
alter session set events='10053 trace name context forever,level 2';
--停止
alter session set events '10053 trace name context off';
--对其他session设置
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
sql> select sid,serial#,username from v$session where username is not null;
       sid    serial# username
---------- ---------- ------------------------------
         8       2041 sys
         9        437 eygle
执行跟踪。
sql> exec dbms_system.set_ev(9,437,10053,1,'');
pl/sql procedure successfully completed.
结束跟踪。
sql> exec dbms_system.set_ev(9,437,10053,0,'');
pl/sql procedure successfully completed.

2.3.示例

sql> alter session set events='10053 trace name context forever,level 2';
session altered.
sql> explain plan for select * from scott.dept;
explained.
sql> alter session set events '10053 trace name context off';
session altered.
sql> select value trace_file from v$diag_info where name='default trace file';
trace_file
--------------------------------------------------------------------------------
/data/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3099.trc
sql> exit
disconnected from oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production
with the partitioning, olap, data mining and real application testing options
[oracle@single trace]$ cd select value trace_file from v$diag_info where name='default trace file';
-bash: cd: select: no such file or directory
[oracle@single trace]$ cd /data/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@single trace]$ more orcl_ora_3099.trc