【Cost】全表扫描时cost的计算（非工作量模式下-noworkload）

1.     当sreadtim、mreadtim或mbrc不可用时
2.     当mreadtim小于或等于sreadtim时

1.版本信息

 SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

2. 构造环境

创建mssm的表空间

SQL> create tablespace ballontt datafile ‘/u01/app/oracle/oradata/BALLONTT/ballontt01.dbf‘ size 30m autoextend on maxsize 100m uniform size 1m segment space management manual blocksize 8k;

建表及设置属性

SQL> create table t tablespace ballontt as select * from dba_objects where 1=0;

Table created.

SQL> alter table t pctfree 99 pctused 1;

Table altered.

插入值，并确保每个数据块上只有一行值

SQL> insert into t select * from dba_objects where rownum<2;

1 row created.

SQL> alter table t minimize records_per_block;

Table altered.

SQL>  insert into t select * from dba_objects where rownum<1000;

999 rows created.

SQL> commit;

Commit complete.

收集表的统计信息

SQL> begin

  2  DBMS_STATS.GATHER_TABLE_STATS(

  3  ownname => ‘SYS‘,

  4  tabname => ‘T‘,

  5  estimate_percent => 100,

  6  method_opt => ‘for all columns size 1‘,

  7  degree => DBMS_STATS.AUTO_DEGREE,

  8  cascade=>TRUE

  9  );

 10  end;

 11  /

PL/SQL procedure successfully completed.

SQL> select owner,table_name,blocks from dba_tables where table_name=‘T‘ and owner=‘SYS‘;

OWNER                          TABLE_NAME                         BLOCKS

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

SYS                            T                                    1000

相关参数查看

SQL> show parameter db_file_multiblock_read_count;

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count        integer     16

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE

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

db_block_size                        integer     8192

3. 收集非工作量统计信息

SQL> begin

  2  DBMS_STATS.GATHER_SYSTEM_STATS (

  3  ‘NOWORKLOAD‘);

  4  end;

  5  /

PL/SQL procedure successfully completed.

4. 查看cost

SQL> set autot on

SQL> select count(*) from t;

  COUNT(*)

----------

      1000

Execution Plan

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

Plan hash value: 2966233522

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |   171   (0)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| T    |  1000 |   171   (0)| 00:00:04 |

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

5. 查看aud_stats$

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

PNAME                               PVAL1

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

CPUSPEED

CPUSPEEDNW                       1740.872

IOSEEKTIM                          15.526

IOTFRSPEED                           4096

MAXTHR

MBRC

MREADTIM

SLAVETHR

SREADTIM

只有cpuspeednw,ioseektim,iotfrspeed三个字段有值，据此判断只能使用非工作量的统计信息。

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

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

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

6. 开始计算：

cost=io_cost+ cpu_cost/（cpuspeednw*sreadtim*1000）

   = [#SRds + (#MRds*mreadtim) / sreadtim]+ cpu_cost/（cpuspeednw*sreadtim*1000）

其中#SRds=0

       #MRds=1000/16=62.5

        mreadtim= ioseektim+（db_file_multiblock_read_count *db_lock_size）/iotfrspeed

                     =15.256+(16*8192)/4096

                     =47.256

       Sreadtim= ioseektim+db_block_size/iotfrspeed

              =15.526+8192/4096

              =17.526

       Cpu_cost=SQL> explain plan for select count(*) from t;  

                          Explained.

                          SQL> select cpu_cost from plan_table;

                          CPU_COST

                           ----------

                       = 7271440

       Cpuspeednw=1740.872

最终COST=[0+(62.5*47.256)/ 17.526]+ 7271440/(1740.872*17.526*1000)= 168.75938≈ 169

169和统计出的cost近似相等（其实还是有点问题，四舍五入后应为170就准确了），据此我们知道知道了全表扫描时（使用非工作量统计信息）的cost的计算方法。同时，在一、中也给出了使用工作量统计时cost的算法，可以按照此实验思路进行验证。

Note:为什么我说四舍五入后应该是170的？170和统计出的171还差1，因为oracle使用了隐含参数_table_scan_cost_plus_one，该参数使得在table full scan和index fast full scan的时候会将cost+1。

ballontt
2014/03/06