首页 > 数据库 >oracle 10053事件—执行计划的解析

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

时间:2023-06-22 11:37:05浏览次数:37  
标签:TEST01 Join 10053 CBO TEST orcl test oracle 解析

文档课题: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、访问代价对比

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

3.4.2、TEST01表

--TEST01表未做表分析.

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

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

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

小结:至此CBO计算出每个表单独访问时的最小代价方式,为下一步多表关联查询提供代价计算的数据依据。
3.5、访问多个对象代价
3.5.1、相关理论
以下CBO会列出TEST、TEST01表的所有关联方式,并计算出每一种关联方式的代价,最终选择出代价最小的关联方式作为SQL的执行计划,通常会有如下六种情况: 

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

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通过对比执行计划后,实际选择的过程如下:

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

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

说明: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





标签:TEST01,Join,10053,CBO,TEST,orcl,test,oracle,解析
From: https://blog.51cto.com/u_12991611/6534467

相关文章

  • 史上最全Android性能优化方案解析
    Android中的性能优分为以下几个方面:布局优化网络优化安装包优化内存优化卡顿优化启动优化……一.布局优化布局优化的本质就是减少View的层级。常见的布局优化方案如下:在LinearLayout和RelativeLayout都可以完成布局的情况下优先选择LinearLayout,可以减少View的层级,但是注意相同组......
  • 字节跳动总监封神之作《Android11.0最新Framework解析》,1595页,限时免费下载高清PDF文
    Framework始终穿插在App整个研发生命周期中,不管是从0到1的建立阶段,还是从1到N打磨阶段,都离不开Framework。成为一名AndroidFramework高手,就会成为招聘中非常稀缺的人才,可以成为你的敲门砖。很多同学都表示在面试时必问Framework相关问题。因为目前大公司的app开发都要基......
  • 字节总监用了半个月整理出的1595页《Android11.0 最新Framework解析》高清PDF开发下载
    作为过来人,发现很多学习者和实践者都在AndroidFramework上面临着很多的困扰,比如:工作场景中遇到难题,往往只能靠盲猜和感觉,用临时性的补救措施去掩盖,看似解决了问题,但下次同样的问题又会发作,原因则是缺乏方法论、思路的指引以及工具支持;能力修炼中,缺乏互联网项目这一实践环境,对Fram......
  • 牛掰,阿里P7程序员花了半个月,编成这份1880页的《Android百大框架源码解析》,快来收藏
    为什么要深入了解源码?只要是程序员,不管是Java还是Android,如果不去阅读源码,只看API文档,那就只是浮于表象,这对我们的知识体系的建立和完备以及实战技术的提升都是不利的。真正最能锻炼能力的便是直接去阅读源码,不仅限于阅读Android系统源码,还包括各种优秀的开源库。一方面,这些作品都......
  • 一文全解析KMP算法
    假设现在我们面临这样一个问题:有一个文本串S,和一个模式串P,现在要查找P在S中的位置,怎么查找呢?如果用暴力匹配的思路,并假设现在文本串S匹配到i位置,模式串P匹配到j位置,则有:如果当前字符匹配成功(即S[i]==P[j]),则i++,j++,继续匹配下一个字符;如果失配(即S[i]!=P[j]),令i=i-(j......
  • Oracle19C PDB中普通用户可以通过sqlplus scott/tiger连接吗
     Oracle19CPDB中普通用户可以通过sqlplusscott/tiger连接吗 先说结论,目前我还没找到方式。研究了挺久,通过百度,通过mos上搜索,也想通过触发器来实现,发现都不行。 先说sys用户,是可以的。通过设置ORACLE_PDB_SID这个环境变量,可以实现sys直接登录后连接到指定的PDB上。[o......
  • Oracle 19c新特性介绍(仅包含RAC、DG和备份)
    本文参考:OracleDatabaseDatabaseNewFeaturesGuide,19c,目前版本为2023年03月。摘抄RAC、DG和备份这三块的新特性介绍。1RAC新特性1.1Grid零停机补丁升级1.1.1切换Grid主目录原文摘抄:Usethe-switchGridHomeoptiontoswitchfromthesourceOracleGridInfrastruct......
  • Oracle的监听和白名单
    Oracle的监听和白名单目录Oracle的监听和白名单监听的基本概念和配置:oracle的常见的几种连接方式客户端连接服务器端常见问题排除方法1、ORA-12541:TNS:没有监听器2、ORA-12500:3、ORA-12535:TNS:操作超时4、ORA-12154:TNS:无法处理服务名5、ORA-12514:TNS:监听进程......
  • oracle执行计划的分析
    oracle执行计划的分析目录oracle执行计划的分析查看执行计划方法一、explainplanfor方法二、setautotraceon;执行计划中返回的统计信息的概念解释方法三、使用dbms_xplan包(DBMS_XPLAN.DISPLAY_CURSOR)查看执行计划:方法四通过查看历史sql方法五Events10046方法六修改statis......
  • oracle的开机自启
    ORACLE开机自启配置linux下配置单机开机自启2,redhat操作系统下面,用dbstart的方式2.1,测试[oracle@localhost~]$/oracle/app/oracle/product/11.2.0/dbhome_1/bin/dbstartORACLE_HOME_LISTNERisnotSET,unabletoauto-startOracleNetListenerUsage:/oracle/app/o......