Oracle 性能调优 10053事件

思维导图

这里写图片描述

10053事件概述

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

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

同10046事件一样，10053事件依然无法在官网上找到相关的信息。

10053事件为我们真正的揭开蒙在CBO身上的面纱。

如何做10053事件分析

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as zmc@xgj

SQL> create table t as select rownum x from dba_objects;

Table created

SQL> create index index_t on t(x);

Index created

##同时对表和索引进行分析 
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);

PL/SQL procedure successfully completed


SQL> create table t1 as select x , 'T1'  name  from t where x < 10000;

Table created

##开启10053事件分析 
SQL> alter session set events '10053 trace name context forever,level 1';

Session altered

##执行计划
SQL> explain plan for select t1.* from t ,t1 where t.x<100 and t.x=t1.x;

Explained

##关闭10053事件
SQL> alter session set events '10053 trace name context off';

Session altered

我们可以发现，10053事件的使用方法和10046是一样的，首先需要个事件设置一个级别level 1 ,然后运行SQL（或者直接使用explain plan的方式产生执行计划），最后终止事件。

获取10053生成的trace文件

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

如何获取，查看10046事件获取trace文件的方法步骤，不再赘述了

oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$ls *4348*
cc_ora_4348.trc  cc_ora_4348.trm

分析10053生成的trace文件

查看cc_ora_4348.trc原文件，第一部分都是trace文件通用的，包含操作系统，数据库和会话的信息等等…

这里写图片描述

10053事件从 Predicate Move-Around (PM) 开始进入 10053事件的trace信息部分，这一部分CBO的主要工作是对SQL语句的谓词进行分析、重写，把它改写成最符合逻辑的SQL语句.

这里写图片描述

接下来的部分：

这里写图片描述

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

这里写图片描述

从逻辑上看 “T”.”X”<100 AND “T”.”X”=”T1”.”X” 和 “T”.”X”<100 AND “T”.”X”=”T1”.”X” AND “T1”.”X”<100 这两个谓词条件上是等价的，CBO把它改写成这样，主要是为了方便统计每一步的成本和估算Cardinality(基数)。

下面是绑定变量的描述

Peeked values of the binds in SQL statement

如果SQL中有变量绑定，并且SQL语句执行了bind peeking,在这一项中会有相应的信息。

这里写图片描述

接下来就是

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

这里写图片描述

可以看到这部分的信息一共列出了3个对象信息： t表 t1表 INDEX_T。

这里写图片描述

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

我们看到，这里列出的字段是x字段。因为它既是两表关联的字段，同时自身也是一个谓词条件。

这里写图片描述
字段部分：X列的信息包含了它的类型、平均长度、非重复的值、空值、密度及列的最大最小值，这些信息在CBO做执行计划的计算上都要作为输入的值。

这里写图片描述

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

值得一提的是 CLUF索引的聚合因子。它表示索引中的键值和原表上的数据分部的一种关系，当索引键值和表中数据的排列顺序大致相同时，它以为着索引键值指向的数据块越集中，CLUF 因子越小，越有利于索引的使用。反之，CLUF的值越大，越不利于索引的使用。

了解这个指标对我们分析SQL执行计划很有用处，比如当我们发现SQL执行计划异常，可是从Cardinality上无法解释时，也许应该考虑下是否是Clustering Factor的影响导致的。

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

SINGLE TABLE ACCESS PATH

T表：

这里写图片描述

这里面有2个指标对对于我们分析执行计划比较重要

这里写图片描述
Card: Original: 35252.000000

原始记录数,也就是操作数据源的输入记录数，在这里就是表的实际记录数35252

SQL> select count(1) from t ;

  COUNT(1)
----------
     35252

Rounded: 99

输出的记录数，CBO计算出通过这些过滤条件，预计得到的记录数。可知符合条件的记录99条，CBO估算出99条。（有些时候可能不是一样，有可能是比较接近实际值）

SQL>  select count(1)  from t ,t1 where t.x<100 and t.x=t1.x;

  COUNT(1)
----------
        99

这里写图片描述

T1表：

T1忘记做表分析了…
这里写图片描述

T1表，忘记建索引了，只能全表扫描….
这里写图片描述

至此，CBO 计算出了每个表单独访问数据代价的最小方式，为下一步多表关联查询提供代价计算的数据依据。

下面的部分CBO会列出 T、 T1表所有的关联方式，并计算出每一种关联方式的代价，最终选择出代价最小的关联方式作为SQL的执行计划：

这里面会有六种情况：
这里写图片描述

OPTIMIZER STATISTICS AND COMPUTATIONS

这里写图片描述

Join order[1]: T1[T1]#0 T[T]#1 （T1关联 T）

Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  T1[T1]#0  T[T]#1

***************
Now joining: T[T]#1
***************
NL Join
  Outer table: Card: 99.00  Cost: 7.08  Resp: 7.08  Degree: 1  Bytes: 17
Access path analysis for T
  Inner table: T  Alias: T
  Access Path: TableScan
    NL Join:  Cost: 1643.96  Resp: 1643.96  Degree: 1
      Cost_io: 1618.00  Cost_cpu: 742442284
      Resp_io: 1618.00  Resp_cpu: 742442284
  Access Path: index (index (FFS))
    Index: INDEX_T
    resc_io: 21.14  resc_cpu: 6548312
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Inner table: T  Alias: T
  Access Path: index (FFS)
    NL Join:  Cost: 2122.74  Resp: 2122.74  Degree: 1
      Cost_io: 2100.00  Cost_cpu: 650434250
      Resp_io: 2100.00  Resp_cpu: 650434250
kkofmx: index filter:"T"."X"<100

  Access Path: index (AllEqJoinGuess)
    Index: INDEX_T
    resc_io: 1.00  resc_cpu: 8171
    ix_sel: 0.000028  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.10  Resp: 106.10  Degree: 1
      Cost_io: 106.00  Cost_cpu: 2965253
      Resp_io: 106.00  Resp_cpu: 2965253

  Best NL cost: 106.10
          resc: 106.10  resc_io: 106.00  resc_cpu: 2965253
          resp: 106.10  resp_io: 106.00  resc_cpu: 2965253
Join Card:  98.012780 = outer (99.000000) * inner (99.002808) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
  Outer table:  T1  Alias: T1
    resc: 7.08  card 99.00  bytes: 17  deg: 1  resp: 7.08
  Inner table:  T  Alias: T
    resc: 2.00  card: 99.00  bytes: 5  deg: 1  resp: 2.00
    using dmeth: 2  #groups: 1
    SORT ressource         Sort statistics
      Sort width:        1227 Area size:     1048576 Max Area size:   214743040
      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: 28632647
      Total Temp space used: 0
    SORT ressource         Sort statistics
      Sort width:        1227 Area size:     1048576 Max Area size:   214743040
      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: 28632647
      Total Temp space used: 0
  SM join: Resc: 11.08  Resp: 11.08  [multiMatchCost=0.00]
SM Join
  SM cost: 11.08 
     resc: 11.08 resc_io: 9.00 resc_cpu: 59450866
     resp: 11.08 resp_io: 9.00 resp_cpu: 59450866
  Outer table:  T1  Alias: T1
    resc: 7.08  card 99.00  bytes: 17  deg: 1  resp: 7.08
  Inner table:  T  Alias: T
    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=52428) buildfrag: 1  probefrag: 1  ppasses: 1
  Hash join: Resc: 9.10  Resp: 9.10  [multiMatchCost=0.00]
HA Join
  HA cost: 9.10  
     resc: 9.10 resc_io: 9.00 resc_cpu: 2810323
     resp: 9.10 resp_io: 9.00 resp_cpu: 2810323
Best:: JoinMethod: Hash
       Cost: 9.10  Degree: 1  Resp: 9.10  Card: 98.01 Bytes: 22
***********************
Best so far:  Table#: 0  cost: 7.0752  card: 99.0000  bytes: 1683
              Table#: 1  cost: 9.0983  card: 98.0128  bytes: 2156
***********************

Join order[2]: T[T]#1 T1[T1]#0（T关联 T1）

Join order[2]:  T[T]#1  T1[T1]#0

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

  Best NL cost: 574.45
          resc: 574.45  resc_io: 567.00  resc_cpu: 213015937
          resp: 574.45  resp_io: 567.00  resc_cpu: 213015937
Join Card:  98.012780 = outer (99.002808) * inner (99.000000) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
  Outer table:  T  Alias: T
    resc: 2.00  card 99.00  bytes: 5  deg: 1  resp: 2.00
  Inner table:  T1  Alias: T1
    resc: 7.08  card: 99.00  bytes: 17  deg: 1  resp: 7.08
    using dmeth: 2  #groups: 1
    SORT ressource         Sort statistics
      Sort width:        1227 Area size:     1048576 Max Area size:   214743040
      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: 28632647
      Total Temp space used: 0
  SM join: Resc: 10.08  Resp: 10.08  [multiMatchCost=0.00]
SM Join
  SM cost: 10.08 
     resc: 10.08 resc_io: 9.00 resc_cpu: 30818220
     resp: 10.08 resp_io: 9.00 resp_cpu: 30818220
  Outer table:  T  Alias: T
    resc: 2.00  card 99.00  bytes: 5  deg: 1  resp: 2.00
  Inner table:  T1  Alias: T1
    resc: 7.08  card: 99.00  bytes: 17  deg: 1  resp: 7.08
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.02  #ptns: 1
    hash_area: 256 (max=52428) buildfrag: 1  probefrag: 1  ppasses: 1
  Hash join: Resc: 9.10  Resp: 9.10  [multiMatchCost=0.00]
HA Join
  HA cost: 9.10  
     resc: 9.10 resc_io: 9.00 resc_cpu: 2810323
     resp: 9.10 resp_io: 9.00 resp_cpu: 2810323
Join order aborted: cost > best plan cost
***********************

名词解释：

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)

从trace文件开头部分可以查看

Join order[1]: T1[T1]#0 T[T]#1
T1关联 T 可以提取到的信息：

NL Join

 Access Path: TableScan
    NL Join:  Cost: 1643.96  Resp: 1643.96  Degree: 1
    Cost_io: 1618.00  Cost_cpu: 742442284
    Resp_io: 1618.00  Resp_cpu: 742442284

 Access Path: index (FFS)
    NL Join:  Cost: 2122.74  Resp: 2122.74  Degree: 1
    Cost_io: 2100.00  Cost_cpu: 650434250
    Resp_io: 2100.00  Resp_cpu: 650434250

 Access Path: index (AllEqJoinGuess)
    Index: INDEX_T
    NL Join : Cost: 106.10  Resp: 106.10  Degree: 1
    Cost_io: 106.00  Cost_cpu: 2965253
    Resp_io: 106.00  Resp_cpu: 2965253


结论：（NL Join 的最小Cost是1-6.10 ）
Best NL(Nested loop)   
        NL Join : Cost: 106.10  Resp: 106.10  Degree: 1

SM Join

   SM cost: 11.08 
 resc: 11.08 resc_io: 9.00 resc_cpu: 59450866
 resp: 11.08 resp_io: 9.00 resp_cpu: 59450866

HA Join

  HA cost: 9.10  
 resc: 9.10 resc_io: 9.00 resc_cpu: 2810323
 resp: 9.10 resp_io: 9.00 resp_cpu: 2810323

NL Join SM Join HAJoin 再对比得出最小cost

Best JoinMethod: Hash
Cost: 9.10 Degree: 1 Resp: 9.10 Card: 98.01 Bytes: 22

Best so far: Table#: 0 cost: 7.0752 card: 99.0000 bytes: 1683
Table#: 1 cost: 9.0983 card: 98.0128 bytes: 2156

Join order[2]: T[T]#1 T1[T1]#0
T关联 T1 可以提取到的信息：

(T1没有建索引)
NL Join

Access Path: TableScan
NL Join:  Cost: 574.45  Resp: 574.45  Degree: 1
  Cost_io: 567.00  Cost_cpu: 213015937
  Resp_io: 567.00  Resp_cpu: 213015937

Best NL cost: 574.45
  resc: 574.45  resc_io: 567.00  resc_cpu: 213015937
  resp: 574.45  resp_io: 567.00  resc_cpu: 213015937

SM Join

SM cost: 10.08 
 resc: 10.08 resc_io: 9.00 resc_cpu: 30818220
 resp: 10.08 resp_io: 9.00 resp_cpu: 30818220

HA Join

 HA cost: 9.10  
 resc: 9.10 resc_io: 9.00 resc_cpu: 2810323
 resp: 9.10 resp_io: 9.00 resp_cpu: 2810323

NL Join SM Join HAJoin 再对比得出最小cost

可知道，Join order[2] 最小cost也是Ha

对比下 Join order[1] 和 Join order[2] 的Ha Cost

感觉应该是 1 和 2 中最小的cost 对比，这里都是HA最小

看trace里1 和 2 的HA 是一样的…..

然而Oracle确放弃了这个（不知道跟T1没有索引有没有关系），是个疑问

总之Oracle的选择是：

Join order aborted: cost > best plan cost

Number of join permutations tried: 2

oralce通过对比之后实际选择的关联顺序

这里写图片描述

CBO先估量出 T1 和 T 使用什么方式扫描最优，从上面对表的分析我们也可以看到对T1 才去的是全表扫描，对T采取的是索引。
然后再估量出这两个表使用何种关联方式最优，最终得到一个执行计划。

经过一些列的比较，Oracle最终选择了如上的执行计划作为SQL的最终执行计划。

接下来trace文件最后一部分依然是参数和bug修复信息，忽略…

总结

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

如果我们觉得CBO做出的执行计划不是最优的，就应该去分析，比如对于CBO选择的每一个代价最低的访问数据方式，我们提供给CBO的分析信息是否真实？抑或是代价高的数据访问方式的分析是否真实？

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

10053事件给我们提供了一种深入CBO的内部去查看CBO如何工作的方式，不仅能看到ORACLE是根据什么样的一句来得出最终的执行计划，同时我们也能人为的去验证CBO使用的一些统计数据的准确性。

posted @ 2018-04-08 17:06 杨哥哥阅读(1676) 评论(0) 编辑收藏举报

刷新页面返回顶部

杨哥哥

Oracle 性能调优 10053事件

思维导图

10053事件概述

如何做10053事件分析

获取10053生成的trace文件

分析10053生成的trace文件

总结

公告