CNDBA社区
一. 10053事件
当一个SQL出现性能问题的时候，可以使用SQL_TRACE 或者 10046事件来跟踪SQL. 通过生成的trace来了解SQL的执行过程。
Oracle SQL Trace 和 10046 事件
http://blog.csdn.net/tianlesoftware/archive/2010/09/02/5857023.aspx
Event 10053 执行计划 绑定变量 Bind peeking
http://blog.csdn.net/tianlesoftware/archive/2010/04/30/5544307.aspx
Oracle 跟踪事件 set event
http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4977827.aspx
现在来看一下10053事件。10053事件也是非公开的，在官网上也找不到相关信息。 我们在查看一条SQL的执行计划的时候，只能看到CBO 最终告诉我们的执行计划结果，但是不知道CBO 是根据什么来做的。 如果遇到了执行计划失真，如：一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引。无法进行分析判断。
而10053事件就提供了这样的功能。它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。
10053事件生成trace文件目录和SQL_TRACE一样。
在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.
在Oracle 11g，trace 默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下
对于10053事件的trace文件，我们只能直接阅读原始的trace文件，不能使用tkprof工具来处理，tkprof工具只能用来处理sql_trace 和 10046事件产生的trace文件。
10053事件有两个级别：
Level 2：2级是1级的一个子集，它包含以下内容：
Column statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)
Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：
Parameters used by the optimizer
Index statistics
启用10053事件：
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';
关闭10053事件：
ALTER SESSION SET EVENTS '10053 trace name context off';
说明：
（1）sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。应该通过v$sql_plan查看SQL的真实的执行计划。
（2）10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。
二． 示例：
1. 确定当前的trace 文件
1.1 设定trace 文件标识
SQL> alter session set tracefile_identifier='怀宁';
会话已更改。
设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。
1.2直接用如下SQL直接查出，当前的trace文件名。
/* Formatted on 2010/9/1 23:56:24 (QP5 v5.115.810.9015) */
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc'
AS "trace_file_name"
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
2. 启动10053事件
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
3. 执行事务
SQL> select * from all_tables where table_name='T';
4. 关闭10053事件
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';
三． 查看生成的trace文件
因为我们在做之前设置了标识，所以直接进入trace目录，找到含有 ‘怀宁’标识的trace 文件。
Trace file d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3756_怀宁.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x0x00000000
Memory (Avail/Total): Ph:1570M/4095M, Ph+PgF:4126M/8188M, VA:2874M/4095M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 32
Windows thread id: 3756, image: ORACLE.EXE (SHAD)
*** 2010-09-02 15:09:16.677
*** SESSION ID:(23.3388) 2010-09-02 15:09:16.677
*** CLIENT ID:() 2010-09-02 15:09:16.677
*** SERVICE NAME:(SYS$USERS) 2010-09-02 15:09:16.677
*** MODULE NAME:(sqlplus.exe) 2010-09-02 15:09:16.677
*** ACTION NAME:() 2010-09-02 15:09:16.677
*** TRACE CONTINUED FROM FILE d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3756.trc ***
Registered qb: SEL$1 0xdab3a30 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=5 objn=3232 hint_alias="ALL_TABLES"@"SEL$1"
Registered qb: SEL$2 0xdab1a9c (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$2 nbfros=11 flg=0
fro(0): flg=4 objn=18 hint_alias="CO"@"SEL$2"
fro(1): flg=4 objn=22 hint_alias="CU"@"SEL$2"
fro(2): flg=4 objn=18 hint_alias="CX"@"SEL$2"
fro(3): flg=4 objn=132 hint_alias="DS"@"SEL$2"
fro(4): flg=4 objn=4294951198 hint_alias="KSPPCV"@"SEL$2"
fro(5): flg=4 objn=4294950998 hint_alias="KSPPI"@"SEL$2"
fro(6): flg=4 objn=18 hint_alias="O"@"SEL$2"
fro(7): flg=4 objn=14 hint_alias="S"@"SEL$2"
fro(8): flg=4 objn=4 hint_alias="T"@"SEL$2"
fro(9): flg=4 objn=16 hint_alias="TS"@"SEL$2"
fro(10): flg=4 objn=22 hint_alias="U"@"SEL$2"
Registered qb: SEL$3 0xf8a701c (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$3 nbfros=1 flg=0
fro(0): flg=4 objn=61 hint_alias="OA"@"SEL$3"
Registered qb: SEL$4 0xf8a6acc (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$4 nbfros=1 flg=0
fro(0): flg=4 objn=4294951024 hint_alias="X$KZSRO"@"SEL$4"
Registered qb: SEL$5 0xf8a6384 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$5 nbfros=1 flg=0
fro(0): flg=5 objn=4294950942 hint_alias="V$ENABLEDPRIVS"@"SEL$5"
Registered qb: SEL$6 0xf8ba570 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$6 nbfros=1 flg=0
fro(0): flg=5 objn=4294951295 hint_alias="GV$ENABLEDPRIVS"@"SEL$6"
Registered qb: SEL$7 0xf8b9c50 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$7 nbfros=1 flg=0
fro(0): flg=4 objn=4294951025 hint_alias="X$KZSPR"@"SEL$7"
SPM: statement not found in SMB
**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=57m60mu8c3w33) -----
select * from all_tables where table_name='T'
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
SLP - select list pruning
DP - distinct placement
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
128: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
Bug Fix Control Environment
….
Starting SQL statement dump
user_id=0 user_name=SYS module=sqlplus.exe action=
sql_id=57m60mu8c3w33 plan_hash_value=-564731517 problem_type=3
----- Current SQL Statement for this session (sql_id=57m60mu8c3w33) -----
select * from all_tables where table_name='T'
sql_text_length=46
sql=select * from all_tables where table_name='T'
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-----------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 124 | |
| 1 | FILTER | | | | | |
| 2 | HASH JOIN | | 2 | 4704 | 124 | 00:00:02 |
| 3 | MERGE JOIN CARTESIAN | | 2 | 674 | 124 | 00:00:02 |
| 4 | NESTED LOOPS OUTER | | 2 | 564 | 124 | 00:00:02 |
| 5 | NESTED LOOPS OUTER | | 2 | 530 | 122 | 00:00:02 |
| 6 | NESTED LOOPS OUTER | | 2 | 514 | 120 | 00:00:02 |
| 7 | NESTED LOOPS OUTER | | 2 | 454 | 118 | 00:00:02 |
| 8 | NESTED LOOPS OUTER | | 2 | 364 | 116 | 00:00:02 |
| 9 | NESTED LOOPS | | 2 | 318 | 114 | 00:00:02 |
| 10 | NESTED LOOPS | | 2 | 280 | 112 | 00:00:02 |
| 11 | NESTED LOOPS | | 2 | 106 | 111 | 00:00:02 |
| 12 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 2 | 72 | 109 | 00:00:02 |
| 13 | INDEX SKIP SCAN | I_OBJ2 | 2 | | 107 | 00:00:02 |
| 14 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 | 00:00:01 |
| 15 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | |
| 16 | TABLE ACCESS CLUSTER | TAB$ | 1 | 87 | 1 | 00:00:01 |
| 17 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 | |
| 18 | TABLE ACCESS CLUSTER | TS$ | 1 | 19 | 1 | 00:00:01 |
| 19 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 | |
| 20 | TABLE ACCESS BY INDEX ROWID | DEFERRED_STG$ | 1 | 23 | 1 | 00:00:01 |
| 21 | INDEX UNIQUE SCAN | I_DEFERRED_STG1| 1 | | 0 | |
| 22 | TABLE ACCESS CLUSTER | SEG$ | 1 | 45 | 1 | 00:00:01 |
| 23 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 | |
| 24 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 30 | 2 | 00:00:01 |
| 25 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 | 00:00:01 |
| 26 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 1 | 00:00:01 |
| 27 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 | 00:00:01 |
| 28 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | |
| 29 | BUFFER SORT | | 1 | 55 | 123 | 00:00:02 |
| 30 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 0 | |
| 31 | FIXED TABLE FULL | X$KSPPCV | 100 | 197K | 0 | |
| 32 | NESTED LOOPS | | 1 | 21 | 2 | 00:00:01 |
| 33 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 8 | 2 | 00:00:01 |
| 34 | FIXED TABLE FULL | X$KZSRO | 1 | 13 | 0 | |
| 35 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 | |
-----------------------------------------------------------------+-----------------------------------+
…..
小结：
CBO 一定是选择代价最低的数据访问路径作为SQL 的执行计划。 如果觉得CBO做出的执行计划不是最优的，就应该去分析。 这时就用到了10053事件。 我们需要关注CBO选择的每一个代价最低的数据访问方式，以及提供给CBO的分析信息是否真实。
CBO 只是一个数学模型，它只是机械地将收集到的各种信息通过固定的方式进行计算，如果我们提供给CBO的信息是准确的，那么CBO 就能计算出最优的执行计划（排除bug）。
10053事件没有10046事件用的多，比如我们分析SQL，主要还是看SQL的执行计划和SQL_TRACE信息。 但是如果想了解CBO的内部，还得通过10053事件，从10053事件的trace文件中，我们可以深入的了解CBO的内部，了解CBO是如何工作的，根据什么依据得出最终的执行计划。
整理自网络
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：63306533; 聊天 群：40132017
版权声明：本文为博主原创文章，未经博主允许不得转载。
