17 全表扫描成本计算--优化主题系列

看执行计划中 COST到底是怎么算出来的下面探讨下成本计算

 

很多人在做SQL优化的时候都会去看cost，很多人经常问，为什么cost很小，但是SQL就是跑很久不出结果呢？我在这里告诉大家，做SQL优化的时候不要去看cost，因为这些cost是根据统计信息，根据一些数学算法计算出来的，正是因为cost是基于统计信息，基于数学算法计算出来的，那么一旦统计信息有误差，数学算法有缺陷，那么cost就是错误的，而一旦cost计算错误，那么执行计划很可能就错了。所以我们在做SQL优化的时候不要去看cost。

这个章节我们也来手工计算cost。为什么我要让大家也来学习计算cost，因为只有你懂得了怎么计算cost，你在做SQL优化的时候才不会被它迷惑。

 

再强调一下做SQL优化本来SQL就跑的慢也就是说COST本来就算错了你去看错误的COST 不是误导自己吗

 

全表扫描成本计算(请自己在11gR2上做实验)：

select * from v$version where rownum=1;  --11.2.0.1.0

 

创建手动管理的表空间，blockssize8k:

create tablespace test datafile

'/u01/app/oracle/oradata/PROD1/datafile/test.dbf' size 50mautoextend on maxsize 200m

uniform size 1m segment space management manual blocksize 8k;

 

创建测试用户test,默认表空间 test:

create user test identified by oracle default tablespace test;

 

为了简便，授权DBA给test:

grant dba to test;

 

创建测试表test:

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

 

设置pctfree99:

alter table test pctfree 99 pctused 1;

insert into test select * from dba_objects where rownum<2;

 

确保一行一个block:

alter table test minimize records_per_block;

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

commit;

 

收集表统计信息:

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',

tabname => 'TEST',

estimate_percent => 100,

method_opt => 'for all columns size 1',

degree => DBMS_STATS.AUTO_DEGREE,

cascade=>TRUE

);

END;

/

 

select owner,blocks from dba_tables where owner='TEST' andtable_name='TEST';--1000

 

多块读参数设置为16 别乱设置:

show parameter db_file_multiblock_read_count --16

alter session set db_file_multiblock_read_count=16;

 

全表扫描的成本等于220:

成本的计算方式如下：

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    单块读耗时(单位milliseconds 毫秒，1000毫秒等于1秒
mreadtim - multi block read time     多块读耗时(单位milliseconds 毫秒，1000毫秒等于1秒)
cpuspeed - CPU cycles per second     CPU频率(单位MHZ)

 

全表扫描单块读个数是不是=0？？

 

注意：如果没有收集过系统统计信息，那么Oracle采用非工作量统计，如果收集了，Oracle采用工作量统计的计算方法

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

我这里因为MBRC 为0，所以CBO采用了非工作量(noworkload)来计算成本

 

#SRds=0，因为是全表扫描，单块读个数为0

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

 

多块读耗时:mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed

ioseektim 是磁盘寻道寻址的时间

多块读耗时 = 磁盘寻道寻址的时间 + 一次搬运多少数据量耗时

 

select (select pval1 from sys.aux_stats$ where pname ='IOSEEKTIM') +

       (select value

          fromv$parameter

         where name ='db_file_multiblock_read_count') *

       (select value fromv$parameter where name = 'db_block_size') /

       (select pval1 fromsys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"

  from dual;

单块读耗时:sreadtim=ioseektim+db_block_size/iotfrspeed 

select (select pval1 from sys.aux_stats$ where pname ='IOSEEKTIM') +

       (select value fromv$parameter where name = 'db_block_size') /

       (select pval1 fromsys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"

  from dual;

CPUCycles 等于PLAN_TABLE里面的CPU_COST--这个ORACLE未解密，无法知道怎么计算的

cpuspeed 等于CPUSPEEDNW=2696.05568

那么COST=1000/16*42/12+7271440/2696.05568/12/1000

select ceil(1000/16*42/12+7271440/2696.05568/12/1000) from dual;

算出来等于219 的  执行计划中的COST是不是等于220

 

手工计算出来的COST用四舍五入等于219，和我们看到的220有差别
这是由于隐含参数_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
那么我把改参数禁止了试一试

alter session set "_table_scan_cost_plus_one"=false;

禁止这个参数后上面算的COST值跟执行计划中的一模一样

提问 COST单位是什么？？COST没有单位

 

多块读参数改一下从16 改到128 再试试

改了后再去看COST的值变小了生产上多块读参数不要乱改也许本来走索引改了后走全表了

生产上多块读参数OLAP设置为最大即128 OLTP默认即可

oracle一次I/O最多能扫描多少数据？？1M对吧？？8K*128=1MB