Oracle的10053事件详解

DBA小记 2020-10-27

4566

一、10046和10053主要做了什么

10053事件是最常用的Oracle优化器optimizer跟踪trace， 10053可以作为我们解析优化器为什么选择某个执行计划，其中的理由的辅助工具，但并不告诉我们这个执行计划到底运行地如何。

而10046并不解释optimizer优化器的工作，但它同样说明了在SQL解析parse阶段所遇到的等待事件和所消耗的CPU等资源，以及Execute执行和Fetch阶段的各项指标。

简而言之10046告诉我们SQL(执行计划)运行地如何，10053告诉我们优化器为什么为这个SQL选择某个执行计划。

二、10053事件概述

我们在查看一条SQL语句的执行计划时，只看到了CBO最终告诉我们的执行计划结果，但是我们并不知道CBO为何要这样做。

特别是当执行计划明显失真时，我们特别想搞清楚为什么CBO会做出这样的一个选择，那么就可以用10053事件来分析SQL分析过程的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的trace文件路径

10053事件同10046事件一样也会在同样的路径下产生一个trace文件。值得注意的是，10053生成的trace文件不能够使用tkprof工具处理。tkprof工具只能处理sql_trace或者100046事件产生的trace文件。对于10053事件只能直接阅读原始文件。

10053事件生成trace文件目录和SQL_TRACE一样。

在Oracle 10g中，SQL_TRACE生成的trace文件默认路径是$ORACLE_BASE/admin/SID/udump.

在Oracle 11g中，trace 默认路径在：$ORACLE_BASE/diag/rdbms/SID/SID/trace目录下

四、10053的启用和关闭

启用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信息。

五、10053事件示例

5.1、设定trace 文件标识

SQL> alter session set tracefile_identifier='my_10053';

设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录（user_dump_dest）查找文件名里带有标识的文件即可。

5.2、启动10053事件

SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

5.3、执行事务

SQL> select * from ec_org_person eop where eop.id='75546';

10053事件的使用方法和10046相同，首先给事件设置一个级别（level 1），然后运行SQL（或者直接使用explain plan的方式产生执行计划），最后关闭事件

SQL> explain plan for select * from ec_org_person eop where eop.id='75546';

5.4、关闭10053事件

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

5.5、查询trace文件名

--未设置标识tracefile_identifier使用

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;

-设置标识tracefile_identifier使用

SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
       p.spid || '_' || e.VALUE || '.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,
       (SELECT VALUE FROM v$parameter WHERE NAME = 'tracefile_identifier') e;
trace_file_name
--------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_11937_my_10053.trc

六、trace文件解析

6.1 文件内容可分几个重要区域

参数区：初始化参数，隐含参数，这些参数可以左右oracle工作方式。

SQL区：执行的SQL语句，是否使用绑定变量，是否进行了转换操作。

系统信息区：操作系统统计信息 cpu主频CPU执行时间IO寻址时间单块读时间多块读时间。

对象统计信息区：

数据访问方式：访问方式不一样计算代价的方法也不一样，全表扫描走索引多表关联代价都不同。

关联查询：把每张表都作为驱动表去组合，择优选择“代价”最小的关联方式，与哪个表在前无关系。

代价的最后修正：oracle会对选择出来的代价再进行最后的修正，使其更准确一些，更合理一些。

选择出最终执行计划：这个过程是非常快速的，毫秒级可以搞定。

6.2 分析trace文件

more oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_11937 my_10053.trc
Trace file /oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_11937_my_10053.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = oracle/app/oracle/product/11.2/db1
System name:      Linux
Node name:      DFJK-TEST-16
Release:      2.6.32-220.el6.x86_64
Version:      #1 SMP Wed Nov 9 08:03:13 EST 2011
Machine:      x86_64
VM name:      VMWare Version: 6
Instance name: xddb
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 11937, image: oracle@DFJK-TEST-16 (TNS V1-V3)
*** 2020-08-26 09:22:53.629
*** SESSION ID:(5.52795) 2020-08-26 09:22:53.629
*** CLIENT ID:() 2020-08-26 09:22:53.629
*** SERVICE NAME:(SYS$USERS) 2020-08-26 09:22:53.629
*** MODULE NAME:(SQL*Plus) 2020-08-26 09:22:53.629
*** ACTION NAME:() 2020-08-26 09:22:53.629
 
Registered qb: SEL$1 0x166d1638 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=210660 hint_alias="EOP"@"SEL$1"
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)

1、上边部分是10053事件trace文件的最前部分，是trace文件通用的，包含了操作系统、数据库和会话的信息。

**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=3ksc7zwyc8x3c) -----
 select * from ec_org_person eop where eop.id='75546'
*******************************************

2、上边部分，从Predicate Move-Around (PM)开始，CBO优化器主要的工作是对SQL语句的谓词进行分析、重写，把它改为最符合逻辑的SQL语句。

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
…… ……

3、上边这一部分解释trace文件中常用到的一些缩写的指标含义，指标在trace文件中经常被用到，所以在trace的开头列举出来各自含义，以便更加容易的阅读trace文件。

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
Bug Fix Control Environment
 *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
…… ……

4、上边这一部分是数据库的参数信息，初始化参数，隐含参数，这些参数可以左右Oracle工作方式。

Bug Fix Control Environment
    fix  3834770 = 1      
    fix  3746511 = enabled
    fix  4519016 = enabled
    fix  3118776 = enabled
    fix  4488689 = enabled
    fix  2194204 = disabled
    fix  2660592 = enabled
    fix  2320291 = enabled
…… ……

5、上边这一部分是一些修复bug的信息。

*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "EOP"."ID" "ID","EOP"."LOGNAME" "LOGNAME","EOP"."LOGPASS" "LOGPASS","EOP"."NAME" "NAME","EOP"."STATE" "STATE","EOP"."ORGID" "ORGID","EOP"."USERNUM" "USERNUM","EOP"."LOGINEORTIMES" "L
OGINEORTIMES","EOP"."EMPNUM" "EMPNUM","EOP"."DEPTNAME" "DEPTNAME","EOP"."MSG" "MSG","EOP"."NET_WORK" "NET_WORK" FROM "DFZC_BXMC"."EC_ORG_PERSON" "EOP" WHERE "EOP"."ID"=75546
kkoqbc: optimizing query block SEL$1 (#0)

6、上边这一部分是绑定变量的描述，如果SQL中有变量绑定，并且SQL语句执行了bind peeking,在这一项中会有相应的信息。

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: EC_ORG_PERSON  Alias: EOP
    #Rows: 2049  #Blks:  32  AvgRowLen:  92.00  ChainCnt:  0.00
Index Stats::
  Index: EC_ORG_PERSON_LOGNAME  Col#: 2
    LVLS: 1  #LB: 6  #DK: 2039  LB/K: 1.00  DB/K: 1.00  CLUF: 1791.00
  Index: SYS_C0096208  Col#: 1
    LVLS: 1  #LB: 5  #DK: 2049  LB/K: 1.00  DB/K: 1.00  CLUF: 1185.00
***************************************

7、上边这一部分，BASE STATISTICAL INFORMATION，主要是SQL语句中饮用到的基本对象信息，包括关联表和各自索引的信息，这些信息可以在相关的视图中查到比如user_tables 和 user_index,这些值在CBO计算代价的时候都会被考虑到。

表信息的部分中包含了表的行数，数据块数，平均行长，对于字段，只列出了谓词条件中包含的字段，对谓词中没有出现的字段，因为它不影响执行计划的选择，所以CBO不考虑将它考虑到代价中来。

索引部分中列出了索引的高度、索引页数块（LB, Leaf Blocks）,每个索引键值占据的数据块数（LB/K, Leaf Blocks/Key）,每个索引键值对应的数据块数（DB/K,Data Blokcs/Key）, 索引的聚合因子（CLUF，Clustering Factor）。

集群因子（CLUF），它表示索引中的键值和原表上的数据分布的一种关系，当索引键值和表中数据的排列顺序大致相同时，表示索引键值指向的数据块越集中，CLUF 因子越小，越有利于索引的使用。反之，CLUF的值越大，越不利于索引的使用。

当我们发现SQL执行计划异常，从Cardinality上无法解释时，也许应该考虑下是否是Clustering Factor的影响导致的。

***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for EC_ORG_PERSON[EOP]
  Column (#1): ID(
    AvgLen: 5 NDV: 2049 Nulls: 0 Density: 0.000488 Min: 64531 Max: 78089
  Table: EC_ORG_PERSON  Alias: EOP
    Card: Original: 2049.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  10.04  Resp: 10.04  Degree: 0
      Cost_io: 10.00  Cost_cpu: 637906
      Resp_io: 10.00  Resp_cpu: 637906
  Access Path: index (UniqueScan)
    Index: SYS_C0096208
    resc_io: 2.00  resc_cpu: 15663
    ix_sel: 0.000488  ix_sel_with_filters: 0.000488
    Cost: 2.00  Resp: 2.00  Degree: 1
  Access Path: index (AllEqUnique)
    Index: SYS_C0096208
    resc_io: 2.00  resc_cpu: 15663
    ix_sel: 0.000488  ix_sel_with_filters: 0.000488
    Cost: 2.00  Resp: 2.00  Degree: 1
 One row Card: 1.000000
  Best:: AccessPath: IndexUnique
  Index: SYS_C0096208
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0
***************************************

8、上边这一部分是CBO计算的每个对象单独访问的代价。CBO要计算出每个对象单独访问时的代价，通过比较所有的数据访问代价，选择出代价最小的一种访问方式。

两个指标对分析执行计划比较重要：

Card: Original: 2049

原记录数，就是操作数据源的输入记录数，这里值就是表的实际记录数2049。

Card: Rounded:1

输出的记录数，CBO计算出来通过条件过滤，预计得到的记录数。

上边的信息还可以看到，SQL执行可以使用哪几种方式访问数据：

Access Path: TableScan
Access Path: index (UniqueScan)
Access Path: index (AllEqUnique)

代价从Cost值可以看出来分别是10、2、2。所以采用访问唯一索引的方式数据库代价是最低的。

***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  EC_ORG_PERSON[EOP]#0
***********************
Best so far:  Table#: 0  cost: 2.0011  card: 1.0000  bytes: 92
***********************

9、上边这一部分，如果SQL是多表查询的话，会分析关联方式的代价和CPU消耗代价resp_cpu。

表的连接方式列举（前三种比较常用）：

嵌套连接（Nested loops join）

排序合并连接（Sort-Merge join）

散列连接（Hash join）

集群连接（Cluster join）

笛卡尔连接（Cartesian join）

索引连接（Index join）

============
Plan Table
============
----------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name         | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |              |       |       |     2 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | EC_ORG_PERSON|     1 |    92 |     2 |  00:00:01 |
| 2   |   INDEX UNIQUE SCAN          | SYS_C0096208 |     1 |       |     1 |  00:00:01 |
----------------------------------------------------+-----------------------------------+

10、通过一系列的计算和比较，CBO最终选择了上边的执行计划。

Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true
…… ……

11、trace文件的最后一部分信息依然是参数和修复bug的信息。

七、总结

分析10053事件的trace文件，发现CBO一定最终选择的是代价最低的数据访问路径作为SQL的执行计划。

如果感觉CBO做出的执行计划不是最优的，就应该去分析为什么出现这样的执行计划。考虑提供给CBO的统计信息是否真实，代价高的数据访问方式的分析是否真实。

CBO只是一个数据模型，它只是将搜集到的各种信息通过固定的方式进行计算，如果能够保证给CBO提供的各种信息是正确的，CBO通常就应该会计算出最优的执行计划。

10053事件可以使我们进入CBO优化器内部，去了解CBO是如何工作的，按照什么样的依据给出的执行计划。也能够通过trace文件看出CBO使用的统计信息数据是否正确，如果信息不准确造成的问题，可以重新分析问题数据库对象，使CBO做出正确的执行计划。

oracle

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

Oracle的10053事件详解

评论