解析10053事件（转）

最新推荐文章于 2022-03-09 17:18:25 发布

大辰儿

最新推荐文章于 2022-03-09 17:18:25 发布

阅读量350

点赞数

测试

LEO1@LEO1> drop table leo3 purge; 清理环境

Table dropped.

LEO1@LEO1> drop table leo4 purge;

Table dropped.

LEO1@LEO1> create table leo3 as select * from dba_objects; 创建leo3表

Table created.

LEO1@LEO1> create table leo4 as select * from leo3 where rownum<100; 创建leo4表

Table created.

LEO1@LEO1> select count(*) from leo4; 这是个小表

COUNT(*)

----------

LEO1@LEO1> create index idx_leo3 on leo3(object_id); 创建了索引

Index created.

LEO1@LEO1> create index idx_leo4 on leo4(object_id); 同上

Index created.

LEO1@LEO1> begin

dbms_stats.gather_table_stats( leo3表做统计分析

ownname=>'leo1',

tabname=>'leo3',

cascade=>true,

estimate_percent=>null,

method_opt=>'for all columns size 1');

end;

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

LEO1@LEO1> begin

dbms_stats.gather_table_stats( leo4表做统计分析

ownname=>'leo1',

tabname=>'leo4',

cascade=>true,

estimate_percent=>null,

method_opt=>'for all columns size 1');

end;

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

LEO1@LEO1> alter session set events '10053 trace name context forever,level 1'; 启动10053事件

10053事件有2个level，1和2，1级比2级内容要详细的多

Session altered.

LEO1@LEO1> select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id; 执行SQL

COUNT(*)

----------

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

Session altered.

LEO1@LEO1> select value from v$diag_info where name='Default Trace File'; 当前会话写入的trace

VALUE

-----------------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_22298.trc

下面我们来看看trace文件中相关信息

参数区包含初始化参数和隐含参数等

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

----- Current SQL Statement for this session (sql_id=fh7dku2xy52rc) ----- 这个会话的SQL_ID

select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id

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

Legend 下面这些缩写都是优化器使用的trace标识

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

…………….

Compilation Environment Dump

optimizer_mode_hinted = false

optimizer_features_hinted = 0.0.0

parallel_execution_enabled = true

parallel_query_forced_dop = 0

parallel_dml_forced_dop = 0

parallel_ddl_forced_degree = 0

这些都是参数的默认值

……………………………………

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

Column Usage Monitoring is ON: tracking level = 1 标识10053事件用的时level1级别

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

SQL区 SQL查询转换合并块计数统计

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

Query transformations (QT)

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

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

QUERY BLOCK TEXT 查询块文本，就是执行的哪个SQL语句

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

select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id

操作系统统计信息区

-----------------------------

SYSTEM STATISTICS INFORMATION

-----------------------------

Using NOWORKLOAD Stats 基于非工作量统计模式

CPUSPEEDNW: 2657 millions instructions/sec (default is 100) 非工作量统计模式下CPU主频

IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IO传输速率（字节/毫秒）

IOSEEKTIM: 10 milliseconds (default is 10) IO寻址时间（毫秒）

MBRC: -1 blocks (default is 8) 一次多块读可以读几个数据块

基本统计信息（对象级别统计信息） OLAP系统而言拥有对象级别统计信息就已经足够了

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

BASE STATISTICAL INFORMATION 这些统计信息都来自于视图

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

Table Stats:: 来自user_tables视图

Table: LEO4 Alias: LEO4

#Rows: 99 #Blks: 5 AvgRowLen: 75.00

行数块数平均行长

Index Stats:: 来自user_indexes视图

Index: IDX_LEO4 Col#: 4

LVLS: 0 #LB: 1 #DK: 99 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00

索引几层叶子块数多少个唯一键值每个键值有多少个叶块每个键值有多少个数据块聚簇因子

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

Table Stats::

Table: LEO3 Alias: LEO3

#Rows: 71969 #Blks: 1051 AvgRowLen: 97.00

行数块数平均行长

Index Stats::

Index: IDX_LEO3 Col#: 4

LVLS: 1 #LB: 159 #DK: 71969 LB/K: 1.00 DB/K: 1.00 CLUF: 1078.00

索引几层叶子块数多少个唯一键值每个键值有多少个叶块每个键值有多少个数据块聚簇因子

Access path analysis for LEO3 LEO3表访问路径的不同代价

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for LEO3[LEO3]

Table: LEO3 Alias: LEO3

Card: Original: 71969.000000 Rounded: 71969 Computed: 71969.00 Non Adjusted: 71969.00

原始行数近似值精确值非修正值

Access Path: TableScan 全表扫描代价

Cost: 286.71 Resp: 286.71 Degree: 0 总代价=286.71

Cost_io: 286.00 Cost_cpu: 22598123 总代价=IO代价+CPU代价

Resp_io: 286.00 Resp_cpu: 22598123 并行访问代价

Access Path: index (index (FFS)) 索引快速全扫描

Index: IDX_LEO3

resc_io: 45.00 resc_cpu: 9768589 串行访问代价=45（因为索引是串行存储的）

ix_sel: 0.000000 ix_sel_with_filters: 1.000000 ix_sel=1/DK=1/71969=0.000013 索引选择率

ix_sel_with_filters带过滤条件索引选择率

Access Path: index (FFS)

Cost: 45.31 Resp: 45.31 Degree: 1 索引并行访问代价=45.31>45(串行访问代价)

Cost_io: 45.00 Cost_cpu: 9768589 所以要选择串行访问

Resp_io: 45.00 Resp_cpu: 9768589 并行度=1

Access Path: index (FullScan) 索引全扫描

Index: IDX_LEO3

resc_io: 160.00 resc_cpu: 15533230 串行访问代价=160，这个比较高

ix_sel: 1.000000 ix_sel_with_filters: 1.000000

Cost: 160.49 Resp: 160.49 Degree: 1 并行度=1

Best:: AccessPath: IndexFFS

Index: IDX_LEO3

Cost: 45.31 Degree: 1 Resp: 45.31 Card: 71969.00 Bytes: 0

###############################################################################

Access path analysis for LEO4 LEO4表访问路径的不同代价

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for LEO4[LEO4]

Table: LEO4 Alias: LEO4

Card: Original: 99.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00

原始行数近似值精确值非修正值

Access Path: TableScan 全表扫描代价

Cost: 3.00 Resp: 3.00 Degree: 0 总代价=3

Cost_io: 3.00 Cost_cpu: 56397 IO代价+CPU代价

Resp_io: 3.00 Resp_cpu: 56397 并行访问代价

Access Path: index (index (FFS)) 索引快速全扫描

Index: IDX_LEO4

resc_io: 2.00 resc_cpu: 19001 串行访问代价=2

ix_sel: 0.000000 ix_sel_with_filters: 1.000000 ix_sel=1/DK=1/99=0.01 索引选择率

ix_sel_with_filters带过滤条件索引选择率

Access Path: index (FFS)

Cost: 2.00 Resp: 2.00 Degree: 1 索引并行访问代价=2，并行度=1

Cost_io: 2.00 Cost_cpu: 19001

Resp_io: 2.00 Resp_cpu: 19001

Access Path: index (FullScan) 索引全扫描

Index: IDX_LEO4

resc_io: 1.00 resc_cpu: 26921 串行访问代价=1，这个最低，就是它了

ix_sel: 1.000000 ix_sel_with_filters: 1.000000

Cost: 1.00 Resp: 1.00 Degree: 1

Best:: AccessPath: IndexRange

Index: IDX_LEO4

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

关联查询—驱动表的选择

OPTIMIZER STATISTICS AND COMPUTATIONS 优化器的统计和计算

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

GENERAL PLANS 选择执行计划

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

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]: LEO4[LEO4]#0 LEO3[LEO3]#1 关联的对象

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

Now joining: LEO3[LEO3]#1 现在要用leo4小表关联leo3大表，leo4做驱动表

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

NL Join嵌套循环关联 leo4表中有99条，小表为驱动表

驱动表 Outer table: Card: 99.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 3

Access path analysis for LEO3

Inner table: LEO3 Alias: LEO3

Access Path: TableScan 全表扫描-嵌套循环关联COST=28253.17

NL Join: Cost: 28253.17 Resp: 28253.17 Degree: 1

Cost_io: 28183.00 Cost_cpu: 2237241142

Resp_io: 28183.00 Resp_cpu: 2237241142 并行访问代价

Access Path: index (index (FFS)) 索引快速全扫描

Index: IDX_LEO3

resc_io: 43.08 resc_cpu: 9768589 串行访问代价

ix_sel: 0.000000 ix_sel_with_filters: 1.000000

Inner table: LEO3 Alias: LEO3

Access Path: index (FFS)

NL Join: Cost: 4296.33 Resp: 4296.33 Degree: 1 并行访问

Cost_io: 4266.00 Cost_cpu: 967117228

Resp_io: 4266.00 Resp_cpu: 967117228

Access Path: index (AllEqJoinGuess)

Index: IDX_LEO3

resc_io: 1.00 resc_cpu: 8171

ix_sel: 0.000014 ix_sel_with_filters: 0.000014

NL Join (ordered): Cost: 100.03 Resp: 100.03 Degree: 1

Cost_io: 100.00 Cost_cpu: 835894

Resp_io: 100.00 Resp_cpu: 835894

Best NL cost: 100.03 leo4为驱动表，小表为驱动表，最后代价100.03

resc: 100.03 resc_io: 100.00 resc_cpu: 835894 串行方式的代价 IO代价+CPU代价

resp: 100.03 resp_io: 100.00 resc_cpu: 835894 并行方式的代价

Outer table: LEO4 Alias: LEO4

SM Join 先排序后合并关联

SM cost: 268.06 代价268.06

resc: 268.06 resc_io: 265.00 resc_cpu: 97470464

resp: 268.06 resp_io: 265.00 resp_cpu: 97470464

HA Join 哈希关联

HA cost: 47.03 代价47.03，最好是哈希代价最小

resc: 47.03 resc_io: 46.00 resc_cpu: 32949334

resp: 47.03 resp_io: 46.00 resp_cpu: 32949334

Best:: JoinMethod: Hash 最后关联方法选择：哈希hash

Cost: 47.03 Degree: 1 Resp: 47.03 Card: 99.00 Bytes: 8 返回记录数+字节

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

Now joining: LEO4[LEO4]#0 现在要用leo3大表关联leo4小表，leo3做驱动表

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

NL Join 嵌套循环关联 leo3表中有71969条，大表为驱动表

Outer table: Card: 71969.00 Cost: 45.31 Resp: 45.31 Degree: 1 Bytes: 5

Access path analysis for LEO4

Inner table: LEO4 Alias: LEO4

Access Path: TableScan

NL Join: Cost: 97632.61 Resp: 97632.61 Degree: 1

Cost_io: 97505.00 Cost_cpu: 4068618676

Resp_io: 97505.00 Resp_cpu: 4068618676

Access Path: index (index (FFS))

Index: IDX_LEO4

resc_io: 0.27 resc_cpu: 19001

ix_sel: 0.000000 ix_sel_with_filters: 1.000000

Inner table: LEO4 Alias: LEO4

Access Path: index (FFS)

NL Join: Cost: 19581.20 Resp: 19581.20 Degree: 1

Cost_io: 19538.00 Cost_cpu: 1377283224

Resp_io: 19538.00 Resp_cpu: 1377283224

Access Path: index (AllEqJoinGuess)

Index: IDX_LEO4

resc_io: 0.00 resc_cpu: 1050

ix_sel: 0.010101 ix_sel_with_filters: 0.010101

NL Join (ordered): Cost: 47.68 Resp: 47.68 Degree: 1

Cost_io: 45.00 Cost_cpu: 85336039

Resp_io: 45.00 Resp_cpu: 85336039

Best NL cost: 47.68 嵌套循环关联最后代价47.68

resc: 47.68 resc_io: 45.00 resc_cpu: 85336039

resp: 47.68 resp_io: 45.00 resc_cpu: 85336039

SM Join 先排序后合并关联

SM cost: 269.06 代价269.06

resc: 269.06 resc_io: 265.00 resc_cpu: 129384180

resp: 269.06 resp_io: 265.00 resp_cpu: 129384180

Hash join: Resc: 106.17 Resp: 106.17 [multiMatchCost=0.00] 哈希关联，代价=106.17

Final cost for query block SEL$1 (#0) - All Rows Plan:

Best join order: 1 最终代价选择47.0334，用leo4小表驱动表

Cost: 47.0334 Degree: 1 Card: 99.0000 Bytes: 792

Resc: 47.0334 Resc_io: 46.0000 Resc_cpu: 32949334

Resp: 47.0334 Resp_io: 46.0000 Resc_cpu: 32949334

SQL执行计划的选择

============

Plan Table

============

------------------------------------------+-----------------------------------+

------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 47 | |

| 1 | SORT AGGREGATE | | 1 | 8 | | |

| 2 | HASH JOIN | | 99 | 792 | 47 | 00:00:01 |

| 3 | INDEX FULL SCAN | IDX_LEO4| 99 | 297 | 1 | 00:00:01 |

| 4 | INDEX FAST FULL SCAN | IDX_LEO3| 70K | 351K | 45 | 00:00:01 |

------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - access("LEO3"."OBJECT_ID"="LEO4"."OBJECT_ID")

选择的执行计划和上面分析结果是相匹配的

来看看我们真实的执行计划的样子

LEO1@LEO1> set autotrace trace exp

LEO1@LEO1> select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id;

Execution Plan

----------------------------------------------------------

Plan hash value: 172281424

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 8 | 47 (3)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 8 | | |

|* 2 | HASH JOIN | | 99 | 792 | 47 (3)| 00:00:01 |

| 3 | INDEX FULL SCAN | IDX_LEO4 | 99 | 297 | 1 (0)| 00:00:01 |

| 4 | INDEX FAST FULL SCAN | IDX_LEO3 | 71969 | 351K| 45 (0)| 00:00:01 |

-----------------------------------------------------------------------------------

小结：一模一样对吧，这说明我们的优化器在对比完不同代价后选择的执行计划是最优的，如果我们在实际工作中，遇到了执行计划选择错误的情景，我们可以通过10053事件来做详细的分析。

原文地址 http://www.itpub.net/thread-1766506-1-1.html

大辰儿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
解析10053事件（转）

测试LEO1@LEO1> drop table leo3 purge; 清理环境Table dropped.LEO1@LEO1> drop table leo4 purge;Table dropped.LEO1@LEO1> create table leo3 as select * from dba_objec
复制链接

扫一扫