AI 助理
备案控制台
开发者社区 数据库 文章 正文

Oracle优化12-10053事件

2023-05-23 98
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： Oracle优化12-10053事件

思维导图


20161218123017442.png


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文件通用的，包含操作系统，数据库和会话的信息等等…


20161218132604091.png


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


20161218133336753.png


接下来的部分：


20161218133929350.png

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


20161218133727802.png


从逻辑上看 “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,在这一项中会有相应的信息。


20161218133423597.png


接下来就是

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

20161218134733837.png


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

20161218140515078.png

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

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


20161218140451702.png


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

20161218140546422.png


索引部分中列出了 索引的高度、索引页数块（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表：


image.png


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

20161218143836842.png


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

20161218144556695.png

T1表：

T1忘记做表分析了…

20161218144913607.png


T1表，忘记建索引了，只能全表扫描….

20161218145224114.png


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

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

这里面会有六种情况：


20161218164003256.png

OPTIMIZER STATISTICS AND COMPUTATIONS

20161218161137640.png

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通过对比之后实际选择的关联顺序


20161218161434988.png

20161218171913367.png



CBO先估量出 T1 和 T 使用什么方式 扫描 最优，从上面对表的分析我们也可以看到 对T1 才去的是全表扫描，对T采取的是索引 。

然后再估量出 这两个表使用何种关联方式最优，最终得到一个执行计划。


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


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


总结


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


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


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


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

文章标签：
SQL
关系型数据库
索引
Oracle
数据库
关键词：
Oracle数据库一键上云优化
Oracle数据库一键上云事件
Oracle数据库一键上云优化事件
小小工匠
目录
相关文章
sunrr
|
18天前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
sunrr
26 7
sunrr
|
18天前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
sunrr
17 5
土木林森
|
3月前
|
监控 Oracle 关系型数据库
"深度剖析：Oracle SGA大小调整策略——从组件解析到动态优化，打造高效数据库性能"
【8月更文挑战第9天】在Oracle数据库性能优化中，系统全局区（SGA）的大小调整至关重要。SGA作为一组共享内存区域，直接影响数据库处理能力和响应速度。本文通过问答形式介绍SGA调整策略：包括SGA的组成（如数据缓冲区、共享池等），如何根据负载与物理内存确定初始大小，手动调整SGA的方法（如使用`ALTER SYSTEM`命令），以及利用自动内存管理（AMM）特性实现智能调整。调整过程中需注意监控与测试，确保稳定性和性能。
土木林森
293 2
泥腿子架构师
|
5月前
|
SQL Oracle 关系型数据库
Oracle游标的使用和优化技巧
Oracle游标的使用和优化技巧
泥腿子架构师
66 8
weixin_836869520
|
4月前
|
SQL Oracle 关系型数据库
Oracle游标的使用和优化技巧
Oracle游标的使用和优化技巧
weixin_836869520
132 1
聚娃科技开发者团队
|
4月前
|
SQL 存储 Oracle
Oracle数据库中游标的工作原理与优化方法
Oracle数据库中游标的工作原理与优化方法
聚娃科技开发者团队
103 0
1941623231718325
|
6月前
|
存储 Oracle 数据管理
Oracle 12c的自动数据优化（ADO）与热图：数据管理的“瘦身”与“透视”艺术
【4月更文挑战第19天】Oracle 12c的ADO和热图技术革新数据管理。ADO智能清理无用数据，优化存储，提升查询速度，实现数据&quot;瘦身&quot;；热图则以直观的视觉表示展示数据分布和状态，助力识别性能瓶颈，犹如数据的&quot;透视&quot;工具。这两项技术结合，强化数据管理，为企业业务发展保驾护航。
1941623231718325
88 0
讓丄帝愛伱
|
存储 SQL Oracle
Oracle优化避免索引失效
Oracle优化避免索引失效
讓丄帝愛伱
362 0
讓丄帝愛伱
|
SQL Oracle 关系型数据库
Oracle优化问题
Oracle优化问题
讓丄帝愛伱
40 0
GTS玉圭
|
6月前
|
数据采集 消息中间件 Oracle
通过流计算与消息中间件实现Oracle到ADB的实时数据同步链路优化实践
介绍基于流计算与消息中间件链路（Oracle->OGG->DataHub->Flink/Blink->ADB）实现的Oracle到ADB的数据实时同步架构潜在问题及处理方案。
GTS玉圭
226 0
通过流计算与消息中间件实现Oracle到ADB的实时数据同步链路优化实践