oracle 11.2.0.3全表扫描COST计算(非工作量模式)

drop table test purge;

 

create table test as select * from dba_objects;

 

--搜集统计信息,不搜集直方图信息

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',

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='SCOTT' and table_name='TEST';

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

 

Cost= (
       #SRds * sreadtim+                           ---SRds=0  (全表扫描没有单块读)
       #MRds * mreadtim+                         ---MRds=BLOCKS/MBCR=1105/128, mreadtim=266
       CPUCycles / cpuspeed /1000        ---CPUCycles=PLAN_TABLE.CPU_COST,cpuspeed=2500
       ) / 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)
mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
sreadtim=ioseektim+db_block_size/iotfrspeed
CPUCycles 等于PLAN_TABLE里面的CPU_COST—这个ORACLE未解密，无法知道怎么计算的

Cost= (
       #SRds * sreadtim+                           ---SRds=0
       #MRds * mreadtim+                         ---MRds=BLOCKS/MBCR=10003/12, mreadtim=30
       CPUCycles / cpuspeed /1000        ---CPUCycles=PLAN_TABLE.CPU_COST,cpuspeed=19215491
       ) / sreadtime

 

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

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

#MRds=表的块数/多块读参数=1105/128

mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed

 

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')"mreadtim"

from dual;

 

sreadtim=ioseektim+db_block_size/iotfrspeed

 

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')"sreadtim"

  fromdual;

 

CPUCycles 等于 PLAN_TABLE里面的CPU_COST

 

explain plan for select count(*) from test;

select cpu_cost from plan_table;

cpuspeed 等于 CPUSPEEDNW= 1194

 

那么整个COST公式代入值计算为:

select ceil((1105/128*266+19215491/1194/1000)/12) from dual;

select count(*) from test;

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

 

Set pagesize 500 linesize 500

Col name for a30

Col value for a30

Col describ for a60

SELECT x.ksppinm NAME, y.ksppstvl VALUE,x.ksppdesc describ

 FROMx$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

那么我把改参数禁止了试一试

这次得到的Cost等于193，与计算值正好匹配，现在更改db_file_multiblock_read_count参数

 

select ceil(1105/16*42/12+19215491/1194/12/1000) from dual;

和实际中执行计划的COST值一致

 

总结:在非工作量模式下,db_file_multiblock_read_count会影响全表扫描表的COST值,在11gR2中，全表扫描计算Cost的方式依然和9i/10g一样，没有变化。

 

BEGIN 

DBMS_STATS.GATHER_TABLE_STATS(ownname =>'TEST', 

tabname => 'TEST', 

estimate_percent => 100, 

method_opt => 'for all columns size1', 

degree => DBMS_STATS.AUTO_DEGREE, 

cascade=>TRUE 

); 

END; 

 

SQL> select owner,blocks from dba_tables where owner='TEST' and table_name='TEST'; 

 

OWNER                              BLOCKS 

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

TEST                                32910 

 

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from test; 

 

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) 

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

                                             23563 

 

 

 

SQL> show parameter multi 

 

NAME                                 TYPE        VALUE 

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

db_file_multiblock_read_count        integer     16 

parallel_adaptive_multi_user         boolean     TRUE 

 

 

SQL> explain plan for select * from test where owner='SYS'; 

 

已解释。 

 

SQL> select * from table(dbms_xplan.display()); 

 

PLAN_TABLE_OUTPUT 

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

 

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

Plan hash value: 1357081020 

 

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

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     | 

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

|   0| SELECT STATEMENT  |      | 77444 | 7336K|  7223   (1)| 00:01:27 | 

|*  1|  TABLE ACCESS FULL| TEST | 77444 |  7336K| 7223   (1)| 00:01:27 | 

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

 

Predicate Information (identified byoperation id): 

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

 

   1- filter("OWNER"='SYS') 

 

已选择13行 

 

 

COST=7223 

 

 

 select count(*) from test where owner='SYS'; 

 --986880 

 

 

 

全表扫描成本计算公式: 

 

成本的计算方式如下： 

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)--每秒钟CPU做多少个轮训 

 

 

全表扫描多块读那么#SRds=0 

 

#MRds=16,每次I/O读16个块 

 

mreadtim =32910/16 

 

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

 

PNAME                   PVAL1 

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

CPUSPEED 

CPUSPEEDNW             2696.05568 

IOSEEKTIM                  10 

IOTFRSPEED               4096 

MAXTHR 

MBRC 

MREADTIM 

SLAVETHR 

SREADTIM 

 

9 rows selected. 

 

这里因为MBRC 为0，所以CBO采用了非工作量(noworkload)来计算成本，所有的系统全是用的 非工作量。 

 

计算mreadtim - multi block read time    一次多块读耗时(单位milliseconds 毫秒，1000毫秒等于1秒) 

 

mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed 

 

ioseektim:寻道寻址的时间 

 

db_file_multiblock_count*db_block_size=16*8K=128K---一次I/O的数据量 

 

 

db_file_multiblock_count*db_block_size/iotftspeed=多块读耗时时间 

 

 

iotftspeed:I/O传输速度 

 

mreadtim(多块读耗时)=寻道寻址的时间+多块读耗时 

SQL> 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')"mreadtim" 

 from dual;  2    3   4    5    6   7   

 

 mreadtim 

---------- 

   42 

 

多块读的耗时有42毫秒 

 

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

 

SQL> select (select pval1 fromsys.aux_stats$ where pname = 'IOSEEKTIM') + 

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

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

 from dual;  2    3   4   

 

 sreadtim 

---------- 

   12 

 

单块读耗时12毫秒 

 

 

操作系统CPU 和磁盘信息Oracle都可以获取到 

 

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

 

 

 

SQL> explain plan for select  * from test where owner='SYS'; 

 

已解释。 

 

SQL> select cpu_cost fromplan_table;           

 

 CPU_COST 

---------- 

 720716510 

 720716510 

 

cpuspeed 等于 CPUSPEEDNW=2696.05568 

SQL> select pname, pval1 fromsys.aux_stats$ where sname='SYSSTATS_MAIN'; 

 

PNAME                   PVAL1 

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

CPUSPEED 

CPUSPEEDNW             2696.05568 

IOSEEKTIM                  10 

IOTFRSPEED               4096 

MAXTHR 

MBRC 

MREADTIM 

SLAVETHR 

SREADTIM 

 

9 rows selected. 

 

 

 

成本的计算方式如下： 

Cost = ( 

      #SRds * sreadtim + 

      #MRds * mreadtim + 

      CPUCycles / cpuspeed 

      ) / sreadtime 

 

#SRds * sreadtim =0 单块读次数为0 

 

#SRds * sreadtim=(number of single blockreads 单块读次数) * 12 

 

 

#MRds * mreadtim =(number of multi blockreads) * 42=32910/16 * 42=86388.75 

 

 

sreadtime=12 

 

 

SQL> select ceil(32910/16*42/12 +  720716510/2696.05568/1000/12) from dual; 

 

CEIL(32910/16*42/12+720716510/2696.05568/1000/12) 

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

                                            7222 

 

 

Cost = ( 

            #MRds * mreadtim + 

      CPUCycles / cpuspeed 

      ) / sreadtime 

 

 

Cost =  

            #MRds  +（ 

      CPUCycles / cpuspeed 

      ) / sreadtime 

 

 

最终的成本计算公式=Cost = #MRds (忽略CPU的情况下,就是多块读的次数=1000/16) 

 

说明减少物理 io扫描次数,是SQL优化的核心思想, 

 

Cost =       #MRds * mreadtim/ sreadtime 

 

#MRds - number of multi block reads  多块读I/O次数