CNDBA社区
SQL优化与调优技术是一个复杂的主题，CBO的核心算法COST数学代价模型的正确建立，受到很多因素的影响。例如数据库支持的算法种类（nest loop,hash join,sort merge,hash unique sort,parallel,bloom filter...),假如某个数据库不支持某种算法，那么执行计划就不会去计算这种执行路径啦；还有代价模型是否考虑数据缓存，如果考虑缓存，实际产生的IO量=表实际大小 * （1-缓存率）；还有每次IO的寻址时间与传输时间，是使用系统默认的无负载情况下的数值，还是使用有负载情况下收集到的统计值；还有CPU运行指令数估算与时间计量的统一......
要建立这么一个最大限度地接近实际情况的代价模型，实在是太难了，从数据建模的思想来看，由简入繁，我们先不去考虑这么多的维度，仅仅考虑其中一个维度对cost计算的影响，后面再逐个加入其它维度。本案例建立一个数学模型，来模拟单表索引访问这种场景下的COST计算，仅仅考虑IO对COST的影响，最简单化代价模型。
一. 建立模型
创建测试数据表 create table t as select * from dba_objects; create index idx_t on t(object_id); 案例场景 select * from t where object_id<1000;
我们来看看上面这条单表索引访问的SQL，在仅仅考虑IO影响的情况下，估算出COST是多少。先来回顾一下，单表索引访问的SQL，整个执行流程应该是这样的：
1）访问索引，从根节点开始，一层层找到对应的枝节点，然后再找到起始叶节点，顺序遍历所有符合条件的叶节点，获取所有符合条件叶节点记录的数据行rowid
2）根据获取到的rowid,逐条去访问数据表的数据块。
因此，整个过程产生的单块读IO次数为
Cost = 读取根节点&枝节点产生的IO + 顺序遍历符合条件的叶节点产生的IO + 通过rowid去访问表数据块所产生的IO
读取根节点&枝节点产生的IO = 索引树的深度（BLEVEL）
顺序遍历符合条件的叶节点产生的IO = 总的叶节点数 * 数据命中比率 ( LEAF_BLOCKS * DATA SELECTIVITY )
通过rowid去访问表数据块所产生的IO = 集群因子 * 数据命中比率 ( CLUSTERING_FACTOR * DATA SELECTIVITY )
集群因子，是在创建索引时候，分析相邻索引行指向表数据物理位置是否一致的一个计数。反映了表中存储的数据的离散程度。也就是说，如果数据的物理顺序是严格升序或者降序的，集群因子应该是和表的数据块数是一致的；如果数据的物理顺序完全是无序的，集群因子等于数据表的非空数据行数。
因此
Cost = BLEVEL + ( LEAF_BLOCKS * DATA SELECTIVITY ) + ( CLUSTERING_FACTOR * DATA SELECTIVITY )
数据命中比率（DATA SELECTIVITY）在案例的场景中(select * from t where object_id<1000) 是表示符合 object_id<1000的记录数占表所有非空记录数的百分比。 假设数据分布是均匀的，那么：
DATA SELECTIVITY = （current_value - low_value)/ (high_value - low_value)
low_value 表示object_id的最小值，high_value 表示 object_id 的最大值， current_value = 1000;low_value 与 high_value 的数据都来源于收集到的统计信息
因此
Cost = BLEVEL + ( LEAF_BLOCKS * （current_value - low_value)/ (high_value - low_value) ) + ( CLUSTERING_FACTOR * （1000 - low_value)/ (high_value - low_value))
实际计算的时候，要对每一步向上取整
Cost = BLEVEL + ceil(LEAF_BLOCKS*(current_value-low_value)/(high_value-low_value)) + ceil(CLUSTERING_FACTOR*(current_value-low_value)/(high_value-low_value))
二.查看统计到的信息
先收集一下统计信息
BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'RM_SHARE_V2', tabname => 'T', estimate_percent => 100, method_opt => 'for columns object_id size 1', degree => DBMS_STATS.AUTO_DEGREE, cascade => TRUE); END;
--estimate_percent 采样比率。直方图的绘制是根据采样数据绘制的，大表的采样一般控制在30左右
--degree = DBMS_STATS.AUTO_DEGREE 表示根据表的大小和数据库初始参数的设置，ORACLE自动设置合适的并行度
--casade = true 表示同时收集columns和indexes的统计信息
--method_opt 1不收集; size 2~ 255 收集; size auto oracle根据数据库本身配置自动判断;size skewonly列倾斜的时候则收集。
举例：for columns object_id size 1 表示对object_id 列强制不收集直方图信息; for all columns size auto 则表示对表所有列收集直方图，桶数由oracle判读。
索引的统计信息
select leaf_blocks,blevel,clustering_factor from dba_indexes where index_name='IDX_T'; LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR 137 1 1537
表的统计信息
select b.num_rows, a.num_distinct, a.num_nulls, utl_raw.cast_to_number(high_value) high_value, utl_raw.cast_to_number(low_value) low_value, (b.num_rows - a.num_nulls) "NUM_ROWS-NUM_NULLS", utl_raw.cast_to_number(high_value) - utl_raw.cast_to_number(low_value) "HIGH_VALUE-LOW_VALUE" from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner and a.table_name = b.table_name and a.owner = 'RM_SHARE_V2' and a.table_name = 'T' and a.column_name = 'OBJECT_ID';
NUM_ROWS 61854--总行数
NUM_DISTINCT 61834--去重后的总行数
NUM_NULLS 20--空行数
HIGH_VALUE 436581--最大值
LOW_VALUE 2 --列最小值
NUM_ROWS-NUM_NULLS 61834 --非空值总数
HIGH_VALUE-LOW_VALUE 436579--高度差
三.代入模型计算Cost 并与实际执行计划估算到的Cost 比较
模型Cost:
BLEVEL + ceil(LEAF_BLOCKS*(current_value-low_value)/(high_value-low_value)) + ceil(CLUSTERING_FACTOR*(current_value-low_value)/(high_value-low_value))
=1 + ceil(137*(1000-2)/(436581-2)) + ceil(1537*(1000-2)/(436581-2))
= 6
执行计划估算值：
-------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 141 | 13254 | 6 (0)| 00:00 | 1 | TABLE ACCESS BY INDEX ROWID| T | 141 | 13254 | 6 (0)| 00:00 |* 2 | INDEX RANGE SCAN | IDX_T | 141 | | 2 (0)| 00:00 --------------------------------------------------------------------------------
可见，该模型计算到的Cost与Oracle实际执行计划估算到的Cost值是一致的
四.该算法的缺点
该算法是基于数据分布比较均匀的假设下才成立的，假如数据分布倾斜比较严重的情况下，该算法得到的结果会严重失准。分析模型建立的整个过程，比较容易失真的地方就是对数据命中比率的估算：DATA SELECTIVITY = （current_value - low_value)/ (high_value - low_value)，该估算值比较接近真实情况的前提是object_id的数据分布均匀：
只有数据分布平均的情况下，才能保证算法计算到的 [low_value,current_value) 区间的记录数量与表非空行总数的比例是等同于区间 [low_value,current_value) 与 [ low_value, high_value] 的比例。如果换做下面这种分布情况，那就不能用上面的模型来计算了，这种数据分布有倾斜的解决方案，Oracle用的是直方图：
版权声明：本文为博主原创文章，未经博主允许不得转载。
SQL优化 Cost
