首页 > 数据库 >Oracle 性能调优 10053事件

Oracle 性能调优 10053事件

时间：2024-06-15 17:43:28浏览次数：26  
标签：Join 10053 T1 调优 io Oracle resc cpu Cost

思维导图

这里写图片描述

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)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

从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使用的一些统计数据的准确性。

https://www.cnblogs.com/youngerger/p/8746071.html

 

 

标签：Join,10053,T1,调优,io,Oracle,resc,cpu,Cost
From： https://www.cnblogs.com/seasonzone/p/18249547

相关文章

  • 生产环境使用10053分析Oracle的执行计划
    【问题现象】在SQL出现性能问题后，通过分析统计信息、直方图、SQLAWR、查看执行计划等，仍然找不出为什么SQL要选择差的执行计划，就需要通过10053查看这个SQL的执行计划的更详细的信息。【操作方法】通过10053事件来查看执行计划和详细的SQL解析过程，10053的trace文件提供了Oracle......
  • JVM 调优和深入了解
    调优的原则1、大多数的java应用不需要GC调优2、大部分需要GC调优的的，不是参数问题，是代码问题3、在实际使用中，分析GC情况优化代码比优化GC参数要多得多；4、GC调优是最后的手目的GC的时间够小GC的次数够少发生FullGC的周期足够的长，时间合理，最好是不发生......
  • jvm调优工具和调优策略
    JDK为我们提供的jvm工具命令行工具jps、jstat、jinfo、jmap、jhat、jstack可视化工具JMX、Jconsole、visualvm生产服务器推荐开启-XX:-HeapDumpOnOutOfMemoryError默认关闭，建议开启，在java.lang.OutOfMemoryError异常出现时，输出一个dump.core文件，记录当时的堆内存快......
  • oracle中如何查询特定日期？
    1.selectlast_day(to_date('20230101','YYYYMMDD'))fromdual; selectlast_day(to_date(V_END_DATE,'YYYYMMDD'))fromdual; --查询任意一天 当月的最后一天2.selectto_char(to_date('20230101','YYYYMMDD')-1,'YYYYM......
  • 成为MySQL DBA后，再看ORACLE数据库（九、用户与权限）
    数据库的用户和权限的管理是DBA日常运维工作中的重要组成部分，不同数据库的用户权限体系及管理方式也会有差别，本文将对ORACLE的用户权限体系和常用操作进行一个总结。一、用户管理ORACLE中创建用户语句和mysql一样，不同的是mysql可以在用户后面加ip以实现ip层面的访问控制。在ORAC......
  • Python遗传算法GA对长短期记忆LSTM深度学习模型超参数调优分析司机数据
    全文链接：https://tecdat.cn/?p=36004原文出处：拓端数据部落公众号随着大数据时代的来临，深度学习技术在各个领域中得到了广泛的应用。长短期记忆（LSTM）网络作为深度学习领域中的一种重要模型，因其对序列数据的强大处理能力，在自然语言处理、时间序列预测等领域中取得了显著的成果。然......
  • java oracle easypoi 百万数据导出
    privatestaticfinalIntegerpageSize=100000;/***zcc*@paramfixmedinsCode*@paramtitle*@paramsheetName*/publicvoidexportAudtMorethanVo(StringfixmedinsCode,Stringtitle,StringsheetName){StringfilePa......
  • 实战分析Java的异步编程，并通过CompletableFuture进行高效调优
    一、写在开头在我们一开始讲多线程的时候，提到过异步与同步的概念，这里面我们再回顾一下：同步：调用方在调用某个方法后，等待被调用方返回结果；调用方在取得被调用方的返回值后，再继续运行。调用方顺序执行，同步等待被调用方的返回值，这就是阻塞式调用；异步：调用方在调用某个方法后，直接返......
  • Oracle 是否扼杀了开源 MySQL
    Oracle是否无意中扼杀了开源MySQLPeterZaitsev是一位俄罗斯软件工程师和企业家，曾在MySQL公司担任性能工程师。大约15年前，当甲骨文收购Sun公司并随后收购MySQL时，有很多关于甲骨文何时“杀死MySQL”的讨论。他曾为甲骨文进行辩护，反驳了这些不友好的说法。然而，如今Zaitsev......
  • Oracle报错：“Error in invoking target ‘agent nmhs’ of makefile...”
    Oracle报错：“Errorininvokingtarget‘agentnmhs’ofmakefile...”  前言：Oracle在安装过程中的报错一定要重视，这决定你后续是否能完成安装以及是否能使用。我这边会陆续汇总一些报错现象以及解决方案共享。##InstallProduct86%报错信息：“Errorininvokingtarget'......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99