我们在做sql优化的时候会用到10046和10053跟踪诊断，当然其他场景也会用到。今天在读《DBA攻坚指南》的时候又看到了10053的知识，这里对这两个做个总结。
一.10046事件
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
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
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]$
二.10053事件
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
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
