本实验基于 Oracle11.2.0.1 Scott 账户。

select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

我们先创建一个表，名为 t_fullscan_cost（注意，只需要表结构，不要数据）。

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

Table created.

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

alter table t_fullscan_cost pctfree 99 pctused 1;

Table altered.

这里只插入一行数据。

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

1 row created.

我们确保表中一个块只存一行数据。

alter table t_fullscan_cost minimize records_per_block;

Table altered.

我们再插入 999 行数据。

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

999 rows created.

接下来提交数据。

commit;

Commit complete.

我们收集表的统计信息。

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

PL/SQL procedure successfully completed.

我们查看表的块数。

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

OWNER               BLOCKS
--------------- ----------
SCOTT                 1000

这里设置多块读参数为 16。

alter session set db_file_multiblock_read_count=16;

Session altered.

我们查看下面 SQL 语句执行计划。

set autot trace
select count(*) from t_fullscan_cost;

Execution Plan
----------------------------------------------------------
Plan hash value: 387824861
------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |     1 |   220   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |                 |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_FULLSCAN_COST |  1000 |   220   (0)| 00:00:03 |
------------------------------------------------------------------------------

执行计划中 T_FULLSCAN_COST 走的是全表扫描，Cost 为 220。那么这 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 一次单块读耗时，单位毫秒
• 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';

PNAME                PVAL1
--------------- ----------
CPUSPEED
CPUSPEEDNW      1683.65129    ---cpuspeed
IOSEEKTIM               10    ---I/O 寻道寻址耗时 
IOTFRSPEED            4096    ---I/O 传输速度 
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

因为 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') "sreadtim"
   from dual;

  sreadtim
----------
        12

我们根据单块读耗时算法，查询到单块读耗时需要 12 毫秒。

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

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;

  mreadtim
----------
        42

我们根据多块读耗时算法，查询到多块读耗时需要 42 毫秒。

CPUCycles 等于 PLAN_TABL/V$SQL_PLAN 里面的 CPU_COST。

explain plan for select count(*) from t_fullscan_cost；

Explained.

select cpu_cost from plan_table where rownum<=1;

  CPU_COST
----------
   7271440

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

select (0 * 12 + 1000 / 16 * 42 / 12 + 7271440 / (1683.65129 * 1000) / 12) cost
    from dual;

     COST
----------
219.109904

手动计算出来的 COST 值为 219，和我们看到的 220 相差 1。这是由隐含参数_tablescan_cost_plus_one 造成的（请用 sys 运行下面的 SQL）。

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

NAME                      VALUE           DESCRIB
------------------------- --------------- ------------------------------
_table_scan_cost_plus_one TRUE            bump estimated full table scan
                                           and index ffs cost by one

该参数表示在 TABLE FULL SCAN 或者在 INDEX FAST FULL SCAN 的时候将 Cost 加 1。

到此，我们终于人工计算出全表扫描成本。

全表扫描成本计算公式究竟是什么含义呢？我们再来看一下全表扫描成本计算公式。

Cost = (
       #SRds * sreadtim +
       #MRds * mreadtim +
       CPUCycles / cpuspeed
       ) / sreadtime

因为全表扫描没有单块读，所以#SRds=0，CPU 耗费的成本基本上可以忽略不计，所以我们将全表扫描公式变换如下。

Cost = (
       #MRds * mreadtim 
       ) / sreadtime

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

全表扫描成本计算公式是在 Oracle9i（2000 年左右）开始引入的，当时的 I/O 设备性能远远落后于现在的 I/O 设备（磁盘阵列），随着 SSD 的出现，寻道寻址时间已经可以忽略不计，磁盘阵列的性能已经有较大提升，因此认为在现代的 I/O 设备（磁盘阵列）中，单块读与多块读耗时几乎可以认为是一样的，全表扫描成本计算公式本质含义就是多块读物理 I/O 次数。