ORACLE 深入解析10053事件

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

weixin_34072159

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

阅读量421

点赞数

文章标签：操作系统数据库运维

原文链接：http://blog.51cto.com/19880614/1283138

版权

ORACLE深入解析10053事件

本次我们主要讲解oracle 10053事件和实验，好多朋友可能对这个事件不是很熟悉，因为在日常运维中用到的不是很多。Oracle 10046和10053都是非官方trace sql的方法，在官方文档上是找不到相关资料的，但在MOS上可以找到。sql_trace是官方推荐的trace sql的方法，在官方文档上是可以查询出来的。

10053事件：用来描述oracle如何选择执行计划的过程，然后输出到trace文件里，共我们参考，因为我们经常看执行计划怎么执行的消耗了哪些资源，而不是常看执行计划怎么选择出来了的。

10053场景：当SQL语句执行时走的是错误的执行计划，而又找不到原因时，这时请用10053来分析一下原因。

10053特点：

（1）只可以了解oracle执行计划的选择过程

（2）无法获知代价的计算公式，因为这是oracle内部的商业机密，而且每个oracle版本的优化器计算公式都不相同差距还是蛮大的，不同版本的同一个语句的代价也不一样，优化器现在还不是很成熟，还有待完善。

（3）在这个里面我们重点要了解的是“代价”是如何计算出来的，然后我们才能了解执行计划是如何选择的。

（4）在10053中可以了解哪些因素影响sql的执行代价

（5）oracle 8i cost等价IO资源消耗 9i以后cost等价IO+CPU+网络+等待事件+其他代价

一般IO资源的权重比较大CPU权重较小

10053内容：

参数区：初始化参数，隐含参数，这些参数可以左右oracle工作方式

SQL区：执行的SQL语句，是否使用绑定变量，是否进行了转换操作

系统信息区：操作系统统计信息 cpu主频CPU执行时间IO寻址时间单块读时间多块读时间

对象统计信息区：

数据访问方式：访问方式不一样计算代价的方法也不一样，全表扫描走索引多表关联代价都不同

关联查询：把每张表都作为驱动表去组合，择优选择“代价”最小的关联方式，与哪个表在前无关系

代价的最后修正：oracle会对选择出来的代价再进行最后的修正，使其更准确一些，更合理一些

选择出最终执行计划：这个过程是非常快速的，毫秒级就搞定啦

实验环境

LEO1@LEO1> select * from v$version; 这是我的oracle edition

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

1.验证全表扫描的成本计算公式，贴出执行计划和计算公式。

LEO1@LEO1> col sname for a20

LEO1@LEO1> col pname for a20

LEO1@LEO1> col pual1 for a30

LEO1@LEO1> col pual2 for a30

LEO1@LEO1> select * from sys.aux_stats$; 查看操作系统统计信息

SNAME PNAME PVAL1 PVAL2

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

SYSSTATS_INFO STATUS COMPLETED

SYSSTATS_INFO DSTART 08-15-2009 00:49

SYSSTATS_INFO DSTOP 08-15-2009 00:49

SYSSTATS_INFO FLAGS 1

SYSSTATS_MAIN CPUSPEEDNW 2657.0122

SYSSTATS_MAIN IOSEEKTIM 10

SYSSTATS_MAIN IOTFRSPEED 4096

SYSSTATS_MAIN SREADTIM

SYSSTATS_MAIN MREADTIM

SYSSTATS_MAIN CPUSPEED

SYSSTATS_MAIN MBRC

SYSSTATS_MAIN MAXTHR

SYSSTATS_MAIN SLAVETHR

说明

aux_stats$是sys管理员用户下的一个基表后缀为$，必须写schema才能查询到，所谓的基表就是给动态性能视图提供数据的原始表，由于基表非常重要，oracle规定不允许直接访问和修改基表，如果你比较了解这些那么另说了。这个表中记录了“操作系统统计信息”。Oracle会利用操作系统统计信息来修正执行计划的代价，也就是说这些信息是影响代价计算的因素之一。

注意：如果oracle收集了操作系统统计信息，那么CBO采用工作量统计模式计算代价

如果oracle没有收集操作系统统计信息，那么CBO采用非工作量统计模式计算代价，看上面MBRC没有参数值就说明还没有收集操作系统统计信息

这两个模式计算代价的公式是不同的。

SNAME：是指操作系统统计信息

PNAME：parameter name参数名

PVAL1：参数值

PVAL2：参数值

参数解释

FLAGS：标志

CPUSPEEDNW：非工作量统计模式下CPU主频，直接来自硬件

IOSEEKTIM：IO寻址时间（毫秒），直接来自硬件

IOTFRSPEED：IO传输速率（字节/毫秒）

SREADTIM：读取单个数据块的平均时间

MREADTIM：读取多个数据块的平均时间

CPUSPEED：工作量统计模式下CPU主频，根据当前工作量评估出一个合理值

MBRC：oracle收集完统计信息后评估出的一次多块读可以读几个数据块db_file_multiblock_read_count

MAXTHR：最大IO吞吐量（字节/秒）

SLAVETHR：平均IO吞吐量（字节/秒）

后面这6个参数是在oracle收集完统计信息后才能得出的参数值，有什么用呢？我来解释一下下

CBO在计算SQL语句的代价时，需要使用数据库对象例如表索引等对象统计数据，还要使用操作系统统计数据例如CPU周期IO速度数据块读时间等，选择花费时间最少的执行计划为最佳执行计划。

Oracle使用dbms_stats.gather_system_stats存储过程来收集操作系统统计信息，收集来的数据存放在sys.aux_stats$表中，如果我们做了收集操作那么会有统计数据，如果没有做就没有统计数据，这两种计算代价的方法是不同的，后续会讲。

dbms_stats.gather_system_stats语法

execute dbms_stats.gather_system_stats（

gathering_mode varchar2 default ‘noworkload’

interval integer default null,

stattab varchar2 default null,

statid varchar2 default null,

statown varchar2 default null);

解释

gathering_mode参数，默认值“noworkload”，还可以设置为“workload”含义

noworkload：非工作量统计模式，收集上来的数据都是来自硬件

workload：工作量统计模式，收集上来的数据需要在特定的数据库负载间隔内统计出来的，这样的数据才能真实反映出数据库的操作系统参数（需要执行sql测评出来）

interval：可以指定收集统计信息的时间间隔，例如5收集5分钟的统计信息

命令：execute dbms_stats.gather_system_stats(‘noworkload’,5);

START和STOP关键字自己决定何时开始何时结束收集统计信息

命令：execute dbms_stats.gather_system_stats(‘start’);

上下两条指令间隔3分钟执行，然后把这3分钟的统计信息写入到sys.aux_stats$表里面

execute dbms_stats.gather_system_stats(‘stop’);

注意：上面有个MBRC参数我想多聊一下，它是初始化参数db_file_multiblock_read_count的简写中文翻译“一次读多少个数据块or一次多块读可以读几个数据块”，如果收集了统计信息那么CBO会用MBRC计算代价，如果没有收集统计信息CBO会用这个初始化参数db_file_multiblock_read_count计算代价。

LEO1@LEO1> show parameter db_file_multiblock_read_count 这是我机器上参数默认值

NAME TYPE VALUE

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

db_file_multiblock_read_count integer 79

LEO1@LEO1> show parameter db_block_size 我们的一个块大小为8k

NAME TYPE VALUE

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

db_block_size integer 8192

这个参数值并不是无限大的，大多数平台下的oracle都是128。一般oracle block size =8k

128*8=1M，也就是说1M是大多数操作系统一次最大IO的限制，如果还有其他限制要从这1M里面扣除，初始化参数db_file_multiblock_read_count的最大值之所以定为128，也是为了保守策略。

79*8k=632K

测试

LEO1@LEO1> drop table leo1 purge; 清空环境

Table dropped.

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

Table created.

LEO1@LEO1> begin

dbms_stats.gather_table_stats( 收集表的统计信息

wnname=>'leo1', 用户名

tabname=>'leo1', 表名

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> show parameter db_file_multiblock_read_count

NAME TYPE VALUE

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

db_file_multiblock_read_count integer 79

LEO1@LEO1> alter session set db_file_multiblock_read_count=16; 把多块读参数修改成16方便计算

Session altered.

LEO1@LEO1> show parameter db_file_multiblock_read_count

NAME TYPE VALUE

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

db_file_multiblock_read_count integer 16

LEO1@LEO1> select * from sys.aux_stats$; 没有收集操作系统统计信息

SNAME PNAME PVAL1 PVAL2

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

SYSSTATS_INFO STATUS COMPLETED

SYSSTATS_INFO DSTART 08-15-2009 00:49

SYSSTATS_INFO DSTOP 08-15-2009 00:49

SYSSTATS_INFO FLAGS 1

SYSSTATS_MAIN CPUSPEEDNW 2657.0122

SYSSTATS_MAIN IOSEEKTIM 10

SYSSTATS_MAIN IOTFRSPEED 4096

SYSSTATS_MAIN SREADTIM

SYSSTATS_MAIN MREADTIM

SYSSTATS_MAIN CPUSPEED

SYSSTATS_MAIN MBRC

SYSSTATS_MAIN MAXTHR

SYSSTATS_MAIN SLAVETHR

我们没有收集操作系统统计信息，所以CBO采用了非工作量统计模式(noworkload)来计算代价

LEO1@LEO1> select blocks from user_tables where table_name='LEO1'; LEO1表总数据块为1051

BLOCKS

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

1051

LEO1@LEO1> set autotrace trace explain

LEO1@LEO1> select * from leo1;

Execution Plan

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

Plan hash value: 2716644435

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

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

| 0 | SELECT STATEMENT | | 71968 | 6817K| 233 (1)| 00:00:03 |

| 1 | TABLE ACCESS FULL| LEO1 | 71968 | 6817K| 233 (1)| 00:00:03 |

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

全表扫描的成本等于233，其中CPU代价占整个权重百分比的1%

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

成本的计算公式如下：

Cost = (

#SRds * sreadtim +

#MRds * mreadtim +

CPUCycles / cpuspeed

) / sreadtime

#SRds - number of single block reads 单块读的次数

#MRds - number of multi block reads 多块读的次数

#CPUCyles - number of CPU cycles 一个CPU周期

sreadtim - single block read time 读取单个数据块的平均时间

mreadtim - multi block read time 读取多个数据块的平均时间

cpuspeed - CPU cycles per second CPU周期/秒

注意：如果oracle收集了操作系统统计信息，那么CBO采用工作量统计模式计算代价

如果oracle没有收集操作系统统计信息，那么CBO采用非工作量统计模式计算代价我们现在处于“非工作量统计模式”

#SRds=0,因为是全表扫描，单块读为0，全都使用的是多块读

#MRds=表的块数/多块读参数=1051/16=65.6875

mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+16*8192/4096=42

sreadtim=ioseektim+db_block_size/iotfrspeed=10+8192/4096=12

CPUCycles等于PLAN_TABLE里面的CPU_COST

LEO1@LEO1> explain plan for select * from leo1;

Explained.

LEO1@LEO1> select cpu_cost from plan_table;

CPU_COST

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

38430873

cpuspeed等于CPUSPEEDNW= 2657.0122

COST=65.6875*42/12+38430873/2657.0122/12/1000(毫秒换算成秒)=229.90625+1.20532=231.11157

229.90625是IO代价

1.20532 是CPU代价

手工计算出来的COST用四舍五入等于232，和我们看到的233有差别，这是由于隐含参数_table_scan_cost_plus_one参数造成的

LEO1@LEO1> conn / as sysdba 切换到sys用户才能查看隐含参数

SYS@LEO1> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y

WHERE x.inst_id = USERENV ('Instance')

AND y.inst_id = USERENV ('Instance')

AND x.indx = y.indx

AND x.ksppinm LIKE '%_table_scan_cost_plus_one%'; 2 3 4 5

NAME VALUE DESCRIB

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

_table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost by one

根据该参数的描述，在table full scan和index fast full scan的时候会将cost+1即232+1=233

我们把_table_scan_cost_plus_one参数禁用看看cost变化

SYS@LEO1> alter session set "_table_scan_cost_plus_one"=false; 禁用

Session altered.

SYS@LEO1> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y

WHERE x.inst_id = USERENV ('Instance')

AND y.inst_id = USERENV ('Instance')

AND x.indx = y.indx

AND x.ksppinm LIKE '%_table_scan_cost_plus_one%'; 2 3 4 5 生效

NAME VALUE DESCRIB

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

_table_scan_cost_plus_one FALSE bump estimated full table scan and index ffs cost by one

SYS@LEO1> select * from leo1.leo1;

Execution Plan

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

Plan hash value: 2716644435

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

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

| 0 | SELECT STATEMENT | | 71968 | 6817K| 232 (1)| 00:00:03 |

| 1 | TABLE ACCESS FULL| LEO1 | 71968 | 6817K| 232 (1)| 00:00:03 |

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

这次得到的COST等于232，与计算值正好匹配，这是禁用隐含参数的结果

SYS@LEO1> alter session set db_file_multiblock_read_count=32; 我们修改一下多块读参数

Session altered.

SYS@LEO1> select * from leo1.leo1;

Execution Plan

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

Plan hash value: 2716644435

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

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

| 0 | SELECT STATEMENT | | 71968 | 6817K| 204 (1)| 00:00:03 |

| 1 | TABLE ACCESS FULL| LEO1 | 71968 | 6817K| 204 (1)| 00:00:03 |

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

#SRds=0,因为是全表扫描，单块读为0，全都使用的是多块读

#MRds=表的块数/多块读参数=1051/32=32.84375

mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+32*8192/4096=74

sreadtim=ioseektim+db_block_size/iotfrspeed=10+8192/4096=12

CPUCycles=38430873

cpuspeed等于CPUSPEEDNW= 2657.0122

COST=32.84375*74/12+38430873/2657.0122/12/1000(毫秒换算成秒)= 202.53645+1.20532=203.74177

四舍五入等于204，与执行计划中COST=204相一致

小结：从实验中可以得出，oracle 11gR2中，全表扫描非工作量统计模式下COST计算公式依然和9i/10g一样，没有变化。同时我们也看到了IO成本占整个代价权重的极大部分，是影响SQL效率的主要因素，需要我们多关注。

2.给出B-tree索引Unique scan的成本计算公式，贴出执行计划和计算公式。

CBO各种类型成本计算公式如下：

全表扫描

Full table scan cost= HWM/dbf_mbrc

索引唯一扫描

Unique scan cost = blevel +1

索引快速全扫描

Fast Full Scan cost=leaf_blocks/adj_mbrc

只访问索引，不访问原表扫描

Index-only cost = Blevel + effective index selectivity * leaf_blocks

索引范围扫描

Range Cost = Blevel + effectivity index selectivity* leaf_blocks

+ effective table selectivity * clustering_factor

嵌套循环关联

nested loop join cost =outer access cost + (inner access cost * outer cardinality)

排序合并关联

sort merge join cost = outer access cost + inner access cost + sort costs

哈希关联

hash join cost = (outer access cost * # of hash partitions) + inner access cost

实验

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

Table dropped.

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

Table created.

LEO1@LEO1> create index idx_leo2 on leo2(object_id); 创建idx_leo2

Index created.

LEO1@LEO1> begin

dbms_stats.gather_table_stats( 收集表的统计信息

wnname=>'leo1', 用户名

tabname=>'leo2', 表名

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> select index_name,blevel,leaf_blocks,clustering_factor,num_rows,distinct_keys from dba_indexes where index_name='IDX_LEO2';

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS DISTINCT_KEYS

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

IDX_LEO2 1 159 1076 71968 71968

BLEVEL：索引层数 1表示就1层

LEAF_BLOCKS：索引树的叶子块数 159

CLUSTERING_FACTOR：索引聚簇因子

NUM_ROWS：有索引的行数 71968和数据行数相匹配

DISTINCT_KEYS：不同的索引键值 71968

LEO1@LEO1> select count(*) from leo2;

COUNT(*)

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

71968

LEO1@LEO1> select * from leo2 where object_id=10000;

Execution Plan

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

Plan hash value: 2495991774

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

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

| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | LEO2 | 1 | 97 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | IDX_LEO2 | 1 | | 1 (0)| 00:00:01 |

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

COST=2，其中CPU代价=0，等值查询与索引的条数无关，消耗CPU资源可以忽略不计

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=10000)

公式

Unique scan cost = blevel +1

INDEX UNIQUE SCAN的COST=1 就是blevel，CBO看看需要递归几层索引，与统计信息中的blevel一致

TABLE ACCESS BY INDEX ROWID的COST=1 通过索引rowid访问表产生的代价

因此最终COST=1+1=2

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(*)

----------

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表做统计分析

wnname=>'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表做统计分析

wnname=>'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事件来做详细的分析。

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进行表分析

wnname=>'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

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

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

| 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

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

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

| 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

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

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

| 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(

wnname=>'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

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

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

| 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 选择执行计划

oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html

转载于:https://blog.51cto.com/19880614/1283138

weixin_34072159

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
ORACLE 深入解析10053事件

ORACLE深入解析10053事件本次我们主要讲解oracle 10053事件和实验，好多朋友可能对这个事件不是很熟悉，因为在日常运维中用到的不是很多。Oracle 10046和10053都是非官方trace sql的方法，在官方文档上是找不到相关资料的，但在MOS上可以找到。sql_trace是官方推荐的trace sql的方法，在官方文档上是可以查询出来的。10053事件...
复制链接

扫一扫