SQL优化-基于数据访问路径的CBO成本计算模型

数据访问路径主要分为全表扫描和索引扫描两种方式，索引扫描具体细分为索引唯一扫描、索引范围扫描、索引全扫描、索引快速全扫描和索引跳跃扫描。

 

下面分别对全表扫描和索引扫描的成本进行计算，在进行计算前引入成本估算的模式和相关的指标。

 

一、成本估算模式有非工作量模式和工作量模式。

1、非工作量模式：默认的成本估算模式，aux_stats$表中默认给出了cpuspeednw、ioseektim、iotfrspeed参数的值，并且mbrc参数值由隐含参数_db_file_optimizer_read_count指定，#cpucycles参数值由plan_table表cpu_cost字段给出，其他参数streadtim、mreadtim值是由上面的参数计算所得，具体如下:

名称

参数

单位

计算方式

CPU总量

#cpucycles

 

plan_table的cpu_cost，或者10053的cost_cpu

CPU主频

cpuspeednw

MHZ

Aux_stats$的cpuspeednw

I/O寻址时间

ioseektim

毫秒

Aux_stats$的ioseektim，默认是10

I/O传输速率

iotfrspeed

字节/毫秒

Aux_stats$的iotfrspeed，默认是4096

平均每次读出多少块数

mbrc

Block

隐含参数_db_file_optimizer_read_count的值

读取单个块的平均时间

sreadtim

毫秒

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

读取多个块的平均时间

mreadtim

毫秒

ioseektim+(_db_file_optimizer_read_count)*(db_block_size/iotftspeed)=10+8*8192/4096=26

2、工作量模式：如果收集过系统统计信息，将会采用工作量模式进行估算，cpuspeed、sreadtim、mreadtim、mbrc参数值直接由当时统计系统信息的负载所决定，具体如下：

名称

参数

单位

计算方式

CPU总量

#cpucycles

MHZ*微秒

plan_table的cpu_cost，或者10053的cost_cpu

CPU主频

cpuspeed

MHZ

Aux_stats$的cpuspeed

读取单个块的平均时间

sreadtim

毫秒

Aux_stats$的sreadtim

读取多个块的平均时间

mreadtim

毫秒

Aux_stats$的mreadtim

平均每次读出多少块数

mbrc

Block

Aux_stats$的mbrc

最大I/O吞吐量

maxthr

Block

Aux_stats$的maxthr

平均I/O吞吐量

slavethr

Block

Aux_stats$的slavethr

注：系统统计信息收集方法

executedbms_stats.gather_system_stats(‘start’);

executedbms_stats.gather_system_stats(‘stop’);

二、统计收集相关指标

类型

指标

作用

表

NUM_ROWS

表记录数

BLOCKS

高水位线下的块数

AVG_ROW_LEN

行的平均长度

字段

NUM_DISTINCT

不同值个数

NUM_NULLS

空值个数

LOW_VALUE

最小值

HIGH_VALUE

最大值

DENSITY

密度

HISTOGRAM

直方图

索引

BLEVEL

层级

LEAF_BLOCKS

叶子块数

DISTINCT_KEYS

不同值个数

CLUSTERING_FACTOR

集群因子

NUM_ROWS

索引记录数

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

三、全表扫描和索引扫描的成本计算模型

1、全表扫描：

COST = IO COST + CPUCOST

IO COST = ceil(blocks/mbrc)*mreadtim/sreadtim

CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim

IO COST 和CPU COST前半部分主要计算所花费的时间，最后除以单块访问的时间用于换算成单块读需要读取的块数。

 

 

2、索引唯一扫描：

COST = IO COST + CPUCOST

IO COST = blevel +1 + 1

CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim

IO COST 第一步+1是访问的叶子块，最后一步+1是统计回表的块，因为唯一索引回表块数也是1。

 

 

3、索引范围扫描：

COST = IO COST + CPUCOST

IO COST = blevel+(leaf_blocks*ix_sel)+(clustering_factor*ix_sel_with_filters)

CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim

ix_sel：是索引块的选择率，可通过1/DISTINCT_KEYS计算得到；

ix_sel_with_filters：是针对ix_sel选择后的索引块进一步通过该索引其他列进一步过滤索引块后得到需要回表的选择率；

默认情况下ix_sel和ix_sel_wiht_filters是相等，如果这个索引是复合索引，并且还存在另外一个谓词条件也是在这个索引上时，两者就不相等。

 

4、索引全扫描：

COST = IO COST + CPUCOST

IO COST = blevel+leaf_blocks+(clustering_factor*ix_sel_wiht_filters)

CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim

索引全扫描是单块顺序读，扫描的结果有序；

(clustering_factor*ix_sel_wiht_filter)表示回表的IO COST，如果不回表则不需要计算这一步，索引全扫描可以回表，但是索引快速全扫描不会回表；

ix_sel：因为索引全扫描是扫描所有的叶子块，不需要涉及到索引块的选择率，所以ix_sel是1；

ix_sel_with_filters:既然走索引全扫描，则该索引就不会存在过滤的可能，因为如果索引有过滤的可能，则直接走该索引的范围扫描，所以ix_sel_with_filters也是1。

 

 

5、索引快速全扫描：

COST = IO COST + CPUCOST

IO COST = ceil(leaf_blocks/ mbrc)*mreadtim/sreadtim

CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim

索引快速全扫描是多块读，扫描结果无序，不需要回表；

ix_sel、ix_sel_with_filters这2个参数在索引快速全扫描中无效，因为索引快速全扫描是多块读且不需要回表。

 

6、索引跳跃扫描：

COST = IO COST + CPUCOST

若叶子块数少于前导列的不同值数，IO COST以叶子块数来计算。

IO COST = blevel+ leaf_blocks+(CLUSTERING_FACTOR*ix_sel_filter)

若叶子块数多于前导列的不同值数，IO COST以前导列不同值数来计算。

IO COST = blevel+前导列不同值数*ceil(leaf_blocks/distinct_keys) +( CLUSTERING_FACTOR*ix_sel_with_filters)

CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim

ix_sel=ix_sel_with_filters：是where条件中走该索引谓词（一个/多个相乘）的选择率

跳跃扫描不仅仅出现在无第一个前导列时，如果复合索引包括三个字段，where条件中没有第二个字段的查询列，但有第一和第三的查询列时，

其实是可以走索引跳跃扫描，相当于跳过第二个字段，ix_sel和ix_sel_with_filters就是第一列和第三列的选择率相乘。而且也等于10053事件trace的ss sel这个参数；

前导列不同值数非常大时走索引跳跃扫描的成本会很高。