oracle 10053事件—执行计划的解析

原创 Leo 2023-06-22

222

文档课题：oracle 10053事件—执行计划的解析.
数据库：oracle 11.2.0.4
1、理论知识
在查看SQL语句的执行计划时，CBO仅显示最终结果。当执行计划明显失真时，就急需知道CBO详细的选择过程。此时10053事件便闪亮登场，通过分析生成的trace文件揭开CBO的神秘外纱。
2、数据准备
2.1、建测试数据
SYS@orcl> create user leo identified by leo;

User created.

SYS@orcl> grant dba to leo;

Grant succeeded.

SYS@orcl> conn leo/leo;
Connected.
LEO@orcl> create table test as select rownum x from dba_objects;

Table created.

LEO@orcl> create index ind_text on test(x);

Index created.

LEO@orcl> exec dbms_stats.gather_table_stats('LEO','TEST',cascade => true);

PL/SQL procedure successfully completed.

LEO@orcl> create table test01 as select x,'T1' name from test where x<10000;

Table created.
2.2、生成trace文件
--开启10053事件分析
LEO@orcl> alter session set events '10053 trace name context forever,level 1';

Session altered.

--执行计划
LEO@orcl> explain plan for select test01.* from test,test01 where test.x<100 and test.x=test01.x;

Explained.

--关闭10053事件
LEO@orcl> alter session set events '10053 trace name context off';

Session altered.

LEO@orcl> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_37034.trc
3、分析trace文件
3.1、英文简写
******************************************
----- Current SQL Statement for this session (sql_id=9wfr0ywtvjmjv) -----
explain plan for select test01.* from test,test01 where test.x<100 and test.x=test01.x
*******************************************

--如下对trace文件中常用的英文简写进行说明，以便阅读trace文件。
*******************************************
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
……(省略若干行)

说明：从Predicate Move-Around (PM))开始进入10053事件的trace信息部分，该部分CBO的主要工作是对SQL语句的谓词进行分析、重写，将其改写成最符合逻辑的SQL语句.
**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"TEST"."X"<100 AND "TEST"."X"="TEST01"."X"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "TEST"."X"<100 AND "TEST"."X"="TEST01"."X" AND "TEST01"."X"<100

FPD:   transitive predicates are generated in query block SEL$1 (#0)
"TEST"."X"<100 AND "TEST"."X"="TEST01"."X" AND "TEST01"."X"<100
apadrv-start sqlid=11401672758971125307
:
    call(in-use=2208, alloc=16344), compile(in-use=65336, alloc=68496), execution(in-use=95208, alloc=97568)

说明：从逻辑上看“TEST”.”X”<100 AND “TEST”.”X”=”TEST01”.”X”和“TEST”.”X”<100 AND “TEST”.”X”=”TEST01”.”X” AND “TEST01”.”X”<100，这两个谓词条件是等价的，CBO把将其改写成这样主要是为了方便统计每一步的成本和估算Cardinality(基数)。
3.2、绑定变量
--如下是绑定变量的描述.
*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST01"."X" "X","TEST01"."NAME" "NAME" FROM "LEO"."TEST" "TEST","LEO"."TEST01" "TEST01" WHERE "TEST"."X"<100 AND "TEST"."X"="TEST01"."X" AND "TEST01"."X"<100
kkoqbc: optimizing query block SEL$1 (#0)

        :
    call(in-use=2360, alloc=16344), compile(in-use=66640, alloc=68496), execution(in-use=95208, alloc=97568)

kkoqbc-subheap (create addr=0x7f5566a9fa10)
3.3、BASE STATISTICAL INFORMATION
接下来是BASE STATISTICAL INFORMATION，主要是SQL语句中引用到的基本对象信息，包括关联表和各自索引的信息，这些信息可以在相关的视图中查到，比如user_tables和user_index，这些值在CBO计算代价的时候都会被考虑到。
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TEST01 Alias: TEST01 (NOT ANALYZED) 说明：test01表未经过分析.
    #Rows: 1716 #Blks: 21 AvgRowLen: 100.00 ChainCnt: 0.00
Column (#1): X( NO STATISTICS (using defaults)
    AvgLen: 13 NDV: 54 Nulls: 0 Density: 0.018648
***********************
Table Stats::
Table: TEST Alias: TEST
    #Rows: 91169 #Blks: 151 AvgRowLen: 5.00 ChainCnt: 0.00
Column (#1): X(
   AvgLen: 5 NDV: 91169 Nulls: 0 Density: 0.000011 Min: 1 Max: 91169
Index Stats::
Index: IND_TEXT Col#: 1
    LVLS: 1 #LB: 202 #DK: 91169 LB/K: 1.00 DB/K: 1.00 CLUF: 139.00
Access path analysis for TEST

相关分析：
此部分共列出3个对象信息，分别为test表、test01表和索引ind_text。
表信息：
Table: TEST Alias: TEST
#Rows: 91169 #Blks: 151 AvgRowLen: 5.00 ChainCnt: 0.00
说明：表信息部分包含表的行数、数据块数、平均行长。
字段信息：
只列出谓词条件中包含的字段，谓词条件中没有出现的字段不影响执行计划的选择，所以CBO不会将其考虑到代价中来。此场景列出的字段是X字段，其既是两表关联的字段，同时自身也是一个谓词条件。
Column (#1): X(
    AvgLen: 5 NDV: 91169 Nulls: 0 Density: 0.000011 Min: 1 Max: 91169
说明：X列的信息包含平均长度、非重复的值、空值、密度以及最大最小值，这些信息在CBO做执行计划时都会作为输入值。
索引信息：
Index Stats::
Index: IND_TEXT Col#: 1
LVLS: 1 #LB: 202 #DK: 91169 LB/K: 1.00 DB/K: 1.00 CLUF: 139.00
说明：该部分显示索引高度、索引页数块(LB,Leaf Blocks)、每个索引键值占据的数据块数(LB/K,Leaf Blocks/Key)、每个索引键值对应的数据块数(DB/K,Data Blocks/Key)、索引的聚合因子(CLUF,Clustering Factor)，值得注意的是CLUF索引的聚合因子，其表示索引中的键值和原表数据分布的一种关系，当索引键值和表中数据的排列顺序大致相同时，CBO以为索引键值指向的数据块越集中，CLUF因子越小越有利于索引的使用，反之CLUF的值越大越不利于索引的使用。了解该指标对分析SQL执行计划很有用处，当发现SQL执行计划异常，但从Cardinality上无法解释时，也许应该考虑下是否是Clustering Factor的影响导致的。
3.4、访问单个对象代价
3.4.1、TEST表
3.4.1.1、相关参数
CBO要计算出单独访问每个对象的代价，需通过比较所有的数据访问代价，然后选择代价最小的访问方式：
SINGLE TABLE ACCESS PATH
TEST表
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST[TEST]
Table: TEST Alias: TEST
   Card: Original: 91169.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00
Access Path: TableScan
    Cost: 43.52 Resp: 43.52 Degree: 0
      Cost_io: 43.00 Cost_cpu: 19309137
      Resp_io: 43.00 Resp_cpu: 19309137
Access Path: index (index (FFS))
    Index: IND_TEXT
    resc_io: 56.00 resc_cpu: 16937261
    ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
    Cost: 56.46 Resp: 56.46 Degree: 1
      Cost_io: 56.00 Cost_cpu: 16937261
      Resp_io: 56.00 Resp_cpu: 16937261
Access Path: index (IndexOnly)
    Index: IND_TEXT
    resc_io: 2.00 resc_cpu: 34243
    ix_sel: 0.001086 ix_sel_with_filters: 0.001086
    Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IND_TEXT
         Cost: 2.00 Degree: 1 Resp: 2.00 Card: 99.00 Bytes: 0

Access path analysis for TEST01
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST01[TEST01]
注意：如上数据有2个指标对于分析执行计划比较重要，如下所示：
Table: TEST Alias: TEST
Card: Original: 91169.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00
Card: Original: 91169.000000
原始记录数：
也就是操作数据源的输入记录数，此处表示表的实际记录数为91169.
LEO@orcl> select count(*) from test;

COUNT(*)
----------
     91169
Rounded: 99
输出的记录数，CBO计算出通过这些过滤条件预计得到的记录数，可知符合条件的记录数为99条(注意：有时可能不一样，有可能比较接近实际值)
LEO@orcl> select count(*) from test,test01 where test.x<100 and test.x=test01.x;

COUNT(*)
----------
        99
3.4.1.2、访问代价对比

3.4.2、TEST01表

--TEST01表未做表分析.

--TEST01表未建索引，只能全表扫描.

小结：至此CBO计算出每个表单独访问时的最小代价方式，为下一步多表关联查询提供代价计算的数据依据。
3.5、访问多个对象代价
3.5.1、相关理论

以下CBO会列出TEST、TEST01表的所有关联方式，并计算出每一种关联方式的代价，最终选择出代价最小的关联方式作为SQL的执行计划，通常会有如下六种情况：

3.5.2、TEST01关联TEST
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: TEST01[TEST01]#0 TEST[TEST]#1

***************
Now joining: TEST[TEST]#1
***************
NL Join
Outer table: Card: 99.00 Cost: 7.06 Resp: 7.06 Degree: 1 Bytes: 17
Access path analysis for TEST
Inner table: TEST Alias: TEST
Access Path: TableScan
    NL Join: Cost: 4108.88 Resp: 4108.88 Degree: 1
      Cost_io: 4057.00 Cost_cpu: 1913755937
      Resp_io: 4057.00 Resp_cpu: 1913755937
Access Path: index (index (FFS))
    Index: IND_TEXT
    resc_io: 54.73 resc_cpu: 16937261
    ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: TEST Alias: TEST
  Access Path: index (FFS)
    NL Join: Cost: 5470.51 Resp: 5470.51 Degree: 1
      Cost_io: 5425.00 Cost_cpu: 1678940157
      Resp_io: 5425.00 Resp_cpu: 1678940157
kkofmx: index filter:"TEST"."X"<100

  Access Path: index (AllEqJoinGuess)
    Index: IND_TEXT
    resc_io: 1.00 resc_cpu: 8171
    ix_sel: 0.000011 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 50.00
***** End Logdef Adjustment ******
  NL Join : Cost: 106.08 Resp: 106.08 Degree: 1
      Cost_io: 106.00 Cost_cpu: 2965253
      Resp_io: 106.00 Resp_cpu: 2965253

  Best NL cost: 106.08
          resc: 106.08 resc_io: 106.00 resc_cpu: 2965253
          resp: 106.08 resp_io: 106.00 resc_cpu: 2965253
Join Card: 98.011075 = outer (99.000000) * inner (99.001086) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
Outer table: TEST01 Alias: TEST01
    resc: 7.06 card 99.00 bytes: 17 deg: 1 resp: 7.06
Inner table: TEST Alias: TEST
    resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00
    using dmeth: 2 #groups: 1
    SORT ressource         Sort statistics
      Sort width:         631 Area size:     1048576 Max Area size:   110704640
      Degree:               1
      Blocks to Sort: 1 Row size:     29 Total Rows:             99
      Initial runs:   1 Merge passes: 0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 36918458
      Total Temp space used: 0
    SORT ressource         Sort statistics
      Sort width:         631 Area size:     1048576 Max Area size:   110704640
      Degree:               1
      Blocks to Sort: 1 Row size:     16 Total Rows:             99
      Initial runs:   1 Merge passes: 0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 36918458
      Total Temp space used: 0
SM join: Resc: 11.06 Resp: 11.06 [multiMatchCost=0.00]
说明：以上红色重点标出部分表明NL Join最小Cost为106.08。
SM Join
SM cost: 11.06
     resc: 11.06 resc_io: 9.00 resc_cpu: 76022490
     resp: 11.06 resp_io: 9.00 resp_cpu: 76022490
Outer table: TEST01 Alias: TEST01
    resc: 7.06 card 99.00 bytes: 17 deg: 1 resp: 7.06
Inner table: TEST Alias: TEST
    resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00
    using dmeth: 2 #groups: 1
    Cost per ptn: 0.02 #ptns: 1
    hash_area: 256 (max=27028) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 9.08 Resp: 9.08 [multiMatchCost=0.00]
HA Join
HA cost: 9.08
     resc: 9.08 resc_io: 9.00 resc_cpu: 2810323
     resp: 9.08 resp_io: 9.00 resp_cpu: 2810323
Best:: JoinMethod: Hash
       Cost: 9.08 Degree: 1 Resp: 9.08 Card: 98.01 Bytes: 22
***********************
Best so far: Table#: 0 cost: 7.0583 card: 99.0000 bytes: 1683
              Table#: 1 cost: 9.0762 card: 98.0111 bytes: 2156
小结：对比NL Join、SM Join、HA Join三种连接方式，HA Join代价最小.
3.5.3、TEST关联TEST01
注意：TEST01未建索引，也未搜集统计信息.
***********************
Join order[2]: TEST[TEST]#1 TEST01[TEST01]#0

***************
Now joining: TEST01[TEST01]#0
***************
NL Join
Outer table: Card: 99.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 5
Access path analysis for TEST01
Inner table: TEST01 Alias: TEST01
  Access Path: TableScan
    NL Join: Cost: 572.77 Resp: 572.77 Degree: 1
      Cost_io: 567.00 Cost_cpu: 213015937
      Resp_io: 567.00 Resp_cpu: 213015937

  Best NL cost: 572.77
          resc: 572.77 resc_io: 567.00 resc_cpu: 213015937
          resp: 572.77 resp_io: 567.00 resc_cpu: 213015937
Join Card: 98.011075 = outer (99.001086) * inner (99.000000) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
Outer table: TEST Alias: TEST
    resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00
Inner table: TEST01 Alias: TEST01
    resc: 7.06 card: 99.00 bytes: 17 deg: 1 resp: 7.06
    using dmeth: 2 #groups: 1
    SORT ressource         Sort statistics
      Sort width:         631 Area size:     1048576 Max Area size:   110704640
      Degree:               1
      Blocks to Sort: 1 Row size:     29 Total Rows:             99
      Initial runs:   1 Merge passes: 0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 36918458
      Total Temp space used: 0
SM join: Resc: 10.06 Resp: 10.06 [multiMatchCost=0.00]
SM Join
SM cost: 10.06
     resc: 10.06 resc_io: 9.00 resc_cpu: 39104032
     resp: 10.06 resp_io: 9.00 resp_cpu: 39104032
Outer table: TEST Alias: TEST
    resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00
Inner table: TEST01 Alias: TEST01
    resc: 7.06 card: 99.00 bytes: 17 deg: 1 resp: 7.06
    using dmeth: 2 #groups: 1
    Cost per ptn: 0.02 #ptns: 1
    hash_area: 256 (max=27028) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 9.08 Resp: 9.08 [multiMatchCost=0.00]
HA Join
HA cost: 9.08
     resc: 9.08 resc_io: 9.00 resc_cpu: 2810323
     resp: 9.08 resp_io: 9.00 resp_cpu: 2810323
Join order aborted: cost > best plan cost
小结：Join order[2]最小cost也是HA Join，为9.08，对比Join order[1]和Join order[2]，发现均是HA Join代价最小，且为相同的值，然而Oracle却放弃Join 2.(初步判断与TEST01没有索引有关）
3.5.4、名词解释
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)
4、实际执行计划
--oralce通过对比执行计划后，实际选择的过程如下：

说明：CBO先估量出TEST01和TEST最优扫描方式，从上面对表的分析可以看到对TEST01采取的是全表扫描，对TEST采取的是索引。然后再估量出这两个表使用何种关联方式最优，最终得到执行计划。经过一系列比较，Oracle最终选择如上执行计划作为SQL的最终执行计划。trc文件最后部分是参数和bug修复信息.
5、分析总结
通过分析10053事件的trace原文件，可发现CBO最终选择的是代价最低的数据访问路径作为SQL的执行计划。若觉得CBO做出的执行计划不是最优的，就应该去分析CBO选择的每一个代价最低的访问数据方式，提供给CBO的分析信息是否真实？抑或是代价高的数据访问方式的分析是否真实？
因为CBO只不过是一个数据模型，它只是机械的将搜集到的各种信息通过固定的方式进行计算，如果能够保证给CBO提供的各种信息是正确的，其通常就会计算出最优的执行计划。10053事件提供了一种深入CBO内部去查看其工作方式，不仅能看到ORACLE根据什么样的语句来得出最终的执行计划，同时也能人为的验证CBO使用的统计数据的准确性。
参考网址：https://www.cnblogs.com/youngerger/p/8746071.html

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

oracle 10053事件—执行计划的解析

1、理论知识

2、数据准备

2.1、建测试数据

2.2、生成trace文件

3、分析trace文件

3.1、英文简写

3.2、绑定变量

3.3、BASE STATISTICAL INFORMATION

3.4、访问单个对象代价

3.4.1、TEST表

3.4.1.1、相关参数

3.4.2、TEST01表

3.5、访问多个对象代价

3.5.1、相关理论

3.5.2、TEST01关联TEST

3.5.3、TEST关联TEST01

3.5.4、名词解释

4、实际执行计划

5、分析总结

评论