阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle SQL性能分析之10053事件

180次阅读
没有评论

共计 5877 个字符,预计需要花费 15 分钟才能阅读完成。

优化器生成正确执行计划的前提条件是要有正确的统计信息,不准确的统计信息往往会导致错误的执行计划。当通过 SQL 和基数推断出的执行计划和实际执行计划不同时,就可以借助 10053 事件。10053 事件是用来诊断优化器如何估算成本和选择执行计划的,用它产生的 trace 文件提供了 Oracle 如何选择执行计划,为什么会得到这样的执行计划信息。和 10046 事件类似,它主要用于特殊情况下的分析和诊断。

1、测试环境:
SQL> select * from v$version;
BANNER
———————————————————————-
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

2、建立测试对象
SQL> create table tabtemp as select * from dba_objects where object_id is not null;
Table created.

SQL> select count(object_id) from tabtemp;
COUNT(OBJECT_ID)
—————-
          72764

测试表 object_id 列的数值分布:
SQL> select count(distinct object_id) from tabtemp;
COUNT(DISTINCTOBJECT_ID)
————————
                  72764
建立索引:
SQL> create index idx_tabtemp_id on tabtemp(object_id);

3、生成 10053 事件
统计表及索引信息:
SQL> exec dbms_stats.gather_table_stats(user,’TABTEMP’,cascade=>true);

查看执行计划:
SQL> alter session set tracefile_identifier=’plan’;
SQL> set autotrace trace exp;
SQL> alter session set events ‘10053 trace name context forever,level 1’;

SQL> select * from tabtemp where object_id=3;
Execution Plan
———————————————————-
Plan hash value: 2221486709
——————————————————————————————–
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time    |
——————————————————————————————–
|  0 | SELECT STATEMENT            |                |    1 |    97 |    2  (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| TABTEMP        |    1 |    97 |    2  (0)| 00:00:01 |
|*  2 |  INDEX RANGE SCAN          | IDX_TABTEMP_ID |    1 |      |    1  (0)| 00:00:01 |
——————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
  2 – access(“OBJECT_ID”=3)
由执行计划可知,查询走索引,这是非常高效的查询方式。

更新测试表,将 object_id 列数值全部设置为 3:
SQL> update tabtemp set object_id=3 where object_id!=3;
72763 rows updated.

SQL> commit;
SQL> select count(distinct object_id) from tabtemp;
COUNT(DISTINCTOBJECT_ID)
————————
                      1

不收集统计数据,查看执行计划:
SQL> set autotrace trace exp;
SQL> select * from tabtemp where object_id=3;
Execution Plan
———————————————————-
Plan hash value: 2221486709
——————————————————————————————-
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time    |
——————————————————————————————-
|  0 | SELECT STATEMENT            |                |    1 |    97 |    2  (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| TABTEMP        |    1 |    97 |    2  (0)| 00:00:01 |
|*  2 |  INDEX RANGE SCAN          | IDX_TABTEMP_ID |    1 |      |    1  (0)| 00:00:01 |
——————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
  2 – access(“OBJECT_ID”=3)
由输出结果可知,本次查询沿用原来的执行计划,是错误的执行计划。

重新对更新后的测试对象进行数据分析:
SQL> set autotrace off;
SQL> exec dbms_stats.gather_table_stats(user,’TABTEMP’,cascade=>true);

查看收集统计数据后的执行计划:
SQL> set autotrace trace exp;
SQL> select * from tabtemp where object_id=3;
Execution Plan
———————————————————-
Plan hash value: 3955501171
—————————————————————————–
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
—————————————————————————–
|  0 | SELECT STATEMENT  |        | 72757 |  6749K|  293  (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TABTEMP | 72757 |  6749K|  293  (2)| 00:00:04 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
  1 – filter(“OBJECT_ID”=3)

由输出可知,本次查询使用了正确的执行计划。所以,要注意在实际生产环境中对表、索引等进行及时有效的统计数据收集工作,避免因此带来性能问题。

SQL> alter session set events ‘10053 trace name context off’;
SQL> select value from v$diag_info where name=’Default Trace File’;
VALUE
——————————————————————————
c:\app\administrator\diag\rdbms\orcl11g\orcl11g\trace\orcl11g_ora_5952_plan.trc

4、分析 10053 事件 trace 文件中 CBO 出错的位置
#more  orcl11g_ora_5952_plan.trc
在前面模拟中有如下操作:
SQL> update tabtemp set object_id=3 where object_id!=3;
72763 rows updated.
SQL> commit;
SQL> select count(distinct object_id) from tabtemp;
COUNT(DISTINCTOBJECT_ID)
————————
                      1
SQL> select * from tabtemp where object_id=3;  此处没有重新进行统计信息收集,直接发起查询。

查看 10053trace 文件中相对应的内容:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TABTEMP  Alias: TABTEMP

    #Rows: 72764  #Blks:  1062  AvgRowLen:  97.00

Index Stats::

  Index: IDX_TABTEMP_ID  Col#: 4

LVLS: 1  #LB: 161  #DK: 72764 LB/K: 1.00  DB/K: 1.00  CLUF: 1102.00

 

Access path analysis for TABTEMP

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for TABTEMP[TABTEMP]

  Table: TABTEMP  Alias: TABTEMP

    Card: Original: 72764.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00

  Access Path: TableScan

    Cost:  291.18  Resp: 291.18  Degree: 0

      Cost_io: 289.00  Cost_cpu: 26481829

      Resp_io: 289.00  Resp_cpu: 26481829

  Access Path: index (AllEqRange)

    Index: IDX_TABTEMP_ID

    resc_io: 2.00  resc_cpu: 15723

    ix_sel: 0.000014  ix_sel_with_filters: 0.000014

    Cost: 2.00  Resp: 2.00  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IDX_TABTEMP_ID

        Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0

***************************************

 

如上述输出 trace 文件中加粗所示:

#DK: 表示索引中不同的键值数量。此处数值 72764 错误,在对表进行更新后,索引中只有 1 个 key。

LB/K:表示每个键值对应多少个 leaf blocks。此处数值为 1 错误,应为 leaf blocks 即 #LB 的数值。

DB/K:表示每个 key 对应多少个数据块。此处数值为 1 错误,应为 #Blks 的数值。

Rounded: 表示关联后将产生多少条数据。此处数值为 1 错误,应该是测试表的总行数 72764。

ix_sel_with_filters 是带有过滤条件的索引选择率,即过滤因子 FF,ix_sel_with_filters =1/DK,本例中 DK 数值为 1,所以 ix_sel_with_filters 数值近似为 1。

Card:即 Cardinality,10gr2 以后 cardinality 用 rows 表示,是 oracle 自己估算的数值。本例中应为测试表的行数。

本例中 Index range scan 访问方式 cost 计算公式为:

cost=blevel + FF*leaf_blocks + FF*clustering_factor,由于 FF(ix_sel_with_filters)数值出现的巨大差异(错误的数值为 0.000014,正确数值近似等于 1),导致 Index range scan 访问方式 cost 数值出现严重偏差,最终生成了错误的执行计划。

更多 Oracle 相关信息见Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计5877字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中