SQL优化核心

1.SQL优化不能看COST

我们在做SQL优化的时候都会去看Cost。经常会发现为什么Cost很小，但是SQL就是跑很久不出结果呢？在这里告诉大家，做SQL优化的时候根本不需要去看Cost，因为Cost是根据统计信息、根据一些数学公式计算出来的。正是因为Cost是基于统计信息、基于数学公式计算出来的，那么一旦统计信息有误差，数学公式有缺陷，Cost就算错了。而一旦Cost计算错误，执行计划也就错了。当SQL需要优化的时候，Cost往往是错 误的，既然是错误的Cost，我们为什么还要去看Cost呢？下面介绍手动计算全表扫描和索引扫描成本。

2.全表扫描成本计算

查看测试数据库版本

select * from v$version where rownum=1;

我们创建一张全表扫描的表

create table t_fullscan_cost as select * from dba_objects where 1=0;

我们设置表的pctfree为99%，让表的一个块（8k）只能存储82byte数据。并插入一行数据

alter table t_fullscan_cost pctfree 99 pctused 1;
insert into t_fullscan_cost select * from dba_objects where rownum<2;

确保表中一个块只有一行数据

alter table t_fullscan_cost minimize records_per_block;

我们在插入999行数据

insert into t_fullscan_cost select * from dba_objects where rownum<1000;

现在我们收集统计信息

BEGIN
 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SYS',
 tabname => 'T_FULLSCAN_COST',
 estimate_percent => 100,
 method_opt => 'for all columns size 1',
 degree => 1,
 cascade => TRUE);
 END;

然后我们查看表的块数，可以看到1000行数据有1000个块。

select owner, blocks from dba_tables
where owner = 'SYS' and table_name = 'T_FULLSCAN_COST';

现在我们设置多块读的参数为16，表示一次多块读可以读16个块对本表就是16行数据。

alter session set db_file_multiblock_read_count=16;

现在我们查询表数据并看执行计划

select count(*) from t_fullscan_cost;

执行计划中T_FULLSCAN_COST走的是全表扫描，Cost为272。那么这272是怎么算出来的呢？我们先来看一下全表扫描成本计算公式

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时钟周期数

如果没有收集过系统统计信息（系统的CPU速度，磁盘I/O速度等），那么Oracle采用非工作量方式来计算成本。如果收集了系统统计信息，那么Oracle采用工作量统计方式来计算成本。一般我们是不会收集系统的统计信息的。所以默认情况下都是采用非工作量（noworkload）方式来计算成本。

现在我们来看一下系统的CPU和I/O情况。

select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

更具查询我们可以知道：I/O传输速度为4096，I/O寻道寻址耗时为10，每秒CPU时钟周期数为3201

因为MBRC为NULL，所以CBO采用了非工作量来计算成本。在全表扫描成本计算公式中，#SRds=0，因为是全表扫描一般都是多块读，#MRds=表的块数/多块读参数=1000/16，sreadtim=ioseektim+db_block_size/iotfrspeed，单块读耗时=I/O寻道寻址耗时+块大 小/I/O传输速度，所以单块读耗时为12毫秒。

select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED')
from dual;

根据公式单块读耗时为12毫秒

mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed

多块读耗时= I/O寻道寻址耗时+多块读参数*块大小/I/O传输速度 42毫秒

select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(select value
from v$parameter
where name = 'db_file_multiblock_read_count') *
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') 
from dual;

我们查询CPU_COST

explain plan for select count(*) from t_fullscan_cost;
 select cpu_cost from plan_table where rownum<=1;

根据以上信息，我们现在来计算全表扫描成本。

我看手工计算的成本为218.939295232358 看手动计算的成本比我们直接查询的成本是有差异的其中一个差异为：隐含参数 _tablescan_cost_plus_one造成的

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%';

该参数表示在TABLE FULL SCAN或者在INDEX FAST FULL SCAN的时候将Cost加1。我们通过计算的成本为219.9约等于220，与我们查询的测试计划的272差异52这个是ORACLE 12C之后版本额外开销，后续在介绍。 通过以上我们可以知道多块读的开销为：

Cost = (
#MRds * mreadtim
) / sreadtime

#MRds表示多块读I/O次数，那么现在我们得到一个结论：全表扫描成本公式的本质含义就是多块读的物理I/O次数乘以多块读耗时与单块读耗时的比值。全表扫描成本计算公式本质含义就是多块读物理I/O次数

3.索引范围扫描成本计算

首先我们创建一个表名为t_indexscan_cost的表，并加上索引。

create table t_indexscan_cost as select * from dba_objects;
create index idx_cost on t_indexscan_cost(object_id);

然后收集统计信息

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SYS',
tabname => 'T_INDEXSCAN_COST',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => 1,
cascade => TRUE);
END;

我们查看表总行数、object_id最大值、object_id最小值以及null值个数。

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,
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 = 'SYS'
 and a.table_name = ('T_INDEXSCAN_COST')
 and a.column_name = 'OBJECT_ID';

我们查看执行计划

select owner from t_indexscan_cost where object_id<1000;

执行计划中，T_INDEXSCAN_COST表走的是索引范围扫描。Cost为17。那么这Cost是怎么算出来的呢？我们先来看一下索引范围扫描的成本计算公式。

cost =
blevel +
celiling(leaf_blocks *effective index selectivity) +
celiling(clustering_factor * effective table selectivity)

索引扫描成本计算公式中，blevel、leaf_blocks、clustering_factor都可以通过下面查询得到。

select leaf_blocks, blevel, clustering_factor
from dba_indexes
where owner = 'SYS'
and index_name = 'IDX_COST';

blevel表示索引的二元高度，blevel等于索引高度−1，leaf_blocks表示索引的叶子块个数，clustering_factor表示索引的集群因子，effective index selectivity表示索引有效选择性，effective table selectivity表示表的有效选择性。 <的有效选择性为：(limit-low_value)/(high_value-low_value)(where限制条件−最低值)/(最高值−最低值) 那么这里有效选择性=(1000−2)/( 76239−2)。 执行计划中，CBO估算返回的Rows为769，这769是怎么算出来的呢？ CBO预估的基数=有效选择性*(总行数−NULL数)。

select ceil((1000-2)/(118503-2)*(91303-0)) from dual;

选择性一般来说都是小于1的分数，当where 条件变多变复杂之后，CBO估算的Rows=小于1的分数小于1的分数小于1的分数*…* 表的总行数，这种情况下Rows当然会越算越小.

select 1+ceil(202*998/118501)+ceil(1661*998/118501) from dual;

手动计算出来的成本为17，正好与执行计划中的Cost相同。

在Oracle数据库中，Btree索引是树形结构，索引范围扫描需要从根扫描到分支， 再扫描到叶子。叶子与叶子之间是双向指向的。blevel等于索引高度−1，正好是索引 根块到分支块的距离。leaf_blocks *effective index selectivity表示可能需要扫描多少叶子块。clustering_factor * effective table selectivity表示回表可能需要耗费多少I/O。

索引范围扫描是单块读，回表也是单块读，因此，我们得到如下结论：索引扫描 成本计算公式其本质就是单块读物理I/O次数。

为什么全表扫描成本计算公式要除以单块读耗时呢？

上文提到，全表扫描COST= 多块读物理I/O次数*多块读耗时/单块读耗时，索引范围扫描COST=单块读物理I/O次 数。

现在我们对全表扫描COST以及索引范围扫描COST都乘以单块读耗时： 全表扫描COST单块读耗时=多块读物理I/O次数多块读耗时=全表扫描总耗时

索引范围扫描COST单块读耗时=单块读物理I/O次数单块读耗时=索引扫描总耗 时

4.总结

现在的IT系统中，CPU的发展日新月异，内存技术的更新也越来越频繁，只有磁盘技术发展最为迟缓，磁盘（I/O）已经成为整个IT系统的瓶颈。我们提到全表扫描的成本其本质含义就是多块读的物理I/O次数，索引范围扫描的成本其本质含义就是单块读的物理I/O次数。我们在判断究竟应该走全表扫描还是索引扫描的时候，往往会根据两种不同的扫 描方式所耗费的物理I/O次数来做出选择，哪种扫描方式耗费的物理I/O次数少，就选择哪种扫描方式。在进行SQL优化的时候，我们也是根据哪种执行计划所耗费的物理I/O次数最少而选择哪种执行计划。SQL优化的核心思想就是不管是单块读次数还是多块读次数归根结底都是设法减少SQL的物理I/O次数。