文章转载自：http://www.itpub.net/thread-1766506-1-1.html
3.通过10053事件分析一个SQL执行计划的产生过程，需要贴出trace中的相关信息和必要的文字说明。
测试
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(*)
----------
99
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(*)
----------
99
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
============
------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------+-----------------------------------+
| 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
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 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事件来做详细的分析。
Leonarding
2013.2.24
天津&winter
分享技术~成就梦想
Blog：www.leonarding.com
作者: leonarding 时间: 2013-2-24 15:55
4.当统计信息不准确时，CBO可能产生错误的执行计划，请给出这样的一个例子，在10053 trace中找到CBO出错的位置，并给出必要的文字说明。
LEO1@LEO1> drop table leo5 purge; 清空环境
Table dropped.
LEO1@LEO1> create table leo5 as select * from dba_objects; 创建leo5表
Table created.
LEO1@LEO1> create index idx_leo5 on leo5(object_id); 创建B-tree索引
Index created.
为了让CBO产生错误的执行计划，我把leo5数据分布变的倾斜一些
LEO1@LEO1> select count(*) from leo5; 总记录数是72010
COUNT(*)
----------
72010
LEO1@LEO1> update leo5 set object_id=1 where object_id<70000; 我们更改了68840行，现在object_id=1 占 96%
68840 rows updated.
LEO1@LEO1> commit; 提交
LEO1@LEO1> update leo5 set object_id=2 where object_id>1;
3170 rows updated.
LEO1@LEO1> select count(*) from leo5 where object_id=1; object_id等于1的有68840
COUNT(*)
----------
68840
LEO1@LEO1> select count(*) from leo5 where object_id=2; object_id等于2的有3170
COUNT(*)
----------
3170
LEO1@LEO1> begin
dbms_stats.gather_table_stats( 对leo5进行表分析
ownname=>'leo1',
tabname=>'leo5',
cascade=>true,
estimate_percent=>null,
method_opt=>'for all columns size 254');
end;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
LEO1@LEO1> select count(object_name) from leo5 where object_id=1; 查看执行计划信息
Execution Plan
----------------------------------------------------------
Plan hash value: 2750404108
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 287 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
|* 2 | TABLE ACCESS FULL| LEO5 | 68840 | 1882K| 287 (1)| 00:00:04 |
---------------------------------------------------------------------------
全表扫描68840，还是比较准确的，说明表分析生效了
LEO1@LEO1> select count(object_name) from leo5 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2542459021
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 57 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| LEO5 | 3170 | 88760 | 57 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_LEO5 | 3170 | | 11 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
对于比较少的行走索引也是正确的
LEO1@LEO1> update leo5 set object_id=3 where rownum<60000; 修改了一下object_id分布
59999 rows updated.
LEO1@LEO1> select count(*) from leo5 where object_id=1; object_id的值从68840变成了8857
COUNT(*)
----------
8857
LEO1@LEO1> commit; 提交
Commit complete.
LEO1@LEO1> select count(object_name) from leo5 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2750404108
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 287 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
|* 2 | TABLE ACCESS FULL| LEO5 | 68840 | 1882K| 287 (1)| 00:00:04 |
---------------------------------------------------------------------------
此时CBO依然选择走全表扫描，我们从记录数的变化上就可以知道应该走索引效率更高些，就像object_id=2的执行计划一样INDEX RANGE SCAN代价更小些，为什么CBO会选择了错误的执行计划呢？这是因为我们虽然修改了记录值但没有及时更新leo5表的对象统计信息，CBO还是使用了当初最早的统计信息，所以在计算COST的时候还是认为走全表扫描的代价最优。下面我们再把对象统计信息重新统计一下，得出最新的代价列表进行筛选。
LEO1@LEO1> begin
dbms_stats.gather_table_stats(
ownname=>'leo1',
tabname=>'leo5',
cascade=>true,
estimate_percent=>null,
method_opt=>'for all columns size 254');
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事件
Session altered.
LEO1@LEO1> select count(object_name) from leo5 where object_id=1; 执行SQL语句
COUNT(OBJECT_NAME)
------------------
8857
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
[oracle@leonarding1 trace]$ vim LEO1_ora_22298.trc 查看生成的trace文件内容
Table Stats::
Table: LEO5 Alias: LEO5
#Rows: 72010 #Blks: 1051 AvgRowLen: 75.00
Index Stats::
Index: IDX_ LEO5 Col#: 1
LVLS: 0 #LB: 1 #DK: 3 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00
Access path analysis for LEO5
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for LEO5[LEO5]
Table: LEO5 Alias: LEO5
Card: Original: 72010.000000 Rounded: 72010 Computed: 72010.00 Non Adjusted: 72010.00
Access Path: TableScan
Cost: 287.55 Resp: 287.55 Degree: 0
Cost_io: 287.00 Cost_cpu: 22598123
Resp_io: 287.00 Resp_cpu: 22598123
Access Path: index (AllEqRange)
Index: IDX_LEO5
resc_io: 31.00 resc_cpu: 12862199
ix_sel: 0.333333 ix_sel_with_filters: 0.333333
Cost: 31.33 Resp: 31.33 Degree: 1
LEO1@LEO1> select count(object_name) from leo5 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2542459021
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 158 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| LEO5 | 8857 | 242K| 158 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IDX_LEO5 | 8857 | | 31 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
小结：经过对比CBO最终选择了索引，当我们更新完统计信息，CBO选择了正确的执行计划
10053 cost 执行计划 CBO 计算公式 10046 选择执行计划
4089
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
