如何计算全表扫描的Cost成本

Oracle COST成本计算知识梳理

SQL> select sname,pname,pval1 from aux_stats$ where pname in ('CPUSPEEDNW','IOSEEKTIM','IOTFRSPEED') ;

SNAME                       PNAME                     PVAL1

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

SYSSTATS_MAIN        CPUSPEEDNW            3308.9701   <<<<  speed in millions of operations per second 每秒的百万操作数

SYSSTATS_MAIN        IOSEEKTIM                    10              <<<<  disk seek time in milliseconds 毫秒 千分之一秒

SYSSTATS_MAIN        IOTFRSPEED                 4096          <<<<  disk transfer time in bytes per millisecond 每毫秒磁盘传输的字节数

下面是10053跟踪中的成本信息

Using dictionary system stats.

  Using NOWORKLOAD Stats

  CPUSPEEDNW: 3309 millions instructions/sec (default is 100)

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM:  10 milliseconds (default is 10)

  MBRC:       NO VALUE blocks (default is 8)

注意这里是无负载统计，根据单块读和多块读的计算公式计算本服务器环境下的具体值如下

MBRC >>>>>NO VALUE blocks (default is 8) 这里默认是8个块

sreadtime = IOSEEKTIM + db_block_size/iotfrspeed = 10 + 8192/4096 = 12毫秒

mreadtim = IOSEEKTIM + MBRC*db_block_size/iotfrspeed = 10 + 8*8192/4096 = 26毫秒

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

SYSTEM STATISTICS INFORMATION

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

Using dictionary system stats.

  Using NOWORKLOAD Stats

  CPUSPEEDNW: 3309 millions instructions/sec (default is 100)

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM:  10 milliseconds (default is 10)

  MBRC:       NO VALUE blocks (default is 8)

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST  Alias: TEST

  #Rows: 89733  SSZ: 0  LGR: 0  #Blks:  1543  AvgRowLen:  115.00  NEB: 0  ChainCnt:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 1

  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000

Index Stats::

  Index: IDX_TEST_ID  Col#: 4

  LVLS: 1  #LB: 199  #DK: 89733  LB/K: 1.00  DB/K: 1.00  CLUF: 2464.00  NRW: 89733.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1

  KKEISFLG: 1 

try to generate single-table filter predicates from ORs for query block SEL$1 (#0)

=======================================

SPD: BEGIN context at query block level

=======================================

Query Block SEL$1 (#0)

Return code in qosdSetupDirCtx4QB: NOCTX

=====================================

SPD: END context at query block level

=====================================

Access path analysis for TEST

***************************************

SINGLE TABLE ACCESS PATH 

  Single Table Cardinality Estimation for TEST[TEST] 

  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE

  Table: TEST  Alias: TEST

    Card: Original: 89733.000000  Rounded: 89733  Computed: 89733.000000  Non Adjusted: 89733.000000

  Scan IO  Cost (Disk) =   420.000000

  Scan CPU Cost (Disk) =   54957551.920000

  Total Scan IO  Cost  =   420.000000 (scan (Disk))

                       =   420.000000

  Total Scan CPU  Cost =   54957551.920000 (scan (Disk))

                       =   54957551.920000

  Access Path: TableScan

    Cost:  421.384055  Resp: 421.384055  Degree: 0

      Cost_io: 420.000000  Cost_cpu: 54957552

      Resp_io: 420.000000  Resp_cpu: 54957552

  Best:: AccessPath: TableScan

         Cost: 421.384055  Degree: 1  Resp: 421.384055  Card: 89733.000000  Bytes: 0.000000

***************************************

这里全表扫描使用多块读

根据公式计算

Cost = (

#SRds +

#MRds * mreadtim  /   sreadtime +

#CPUCycles/(cpuspeed * sreadtim)

)

CPU成本如下，注意54957551.920000/(3308.9701的单位是秒，而12是毫秒

SQL> select 54957551.920000/(3308.9701*12*1000) from dual;

54957551.920000/(3308.9701*12*1000)

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

                         1.38405481

此时的CPU成本四舍五入是1.384055

IO成本，此时只有多块读

#MRds * mreadtim  /   sreadtime = 

SQL> select 1543/8*(26/12) from dual;

1543/8*(26/12)

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

    417.895833

我们发现此时的计算结果跟10053跟踪计算结果有差异，差2，我们计算结果大概是418，而10053跟踪是420，目前数据库参数db_file_multiblock_read_count为128

而10053跟踪结果看Oracle在无负载模式下使用默认多块读为8个块，IO成本多了2，这里计算差异需要分析。

SQL> show parameter db_file_multiblock_read_count ;

NAME                                 TYPE                   VALUE

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

db_file_multiblock_read_count        integer                128

所以 

Cost = (

#SRds +

#MRds * mreadtim  /   sreadtime +

#CPUCycles/(cpuspeed * sreadtim)

) = 0 + 418 + 1.384055 = 419.384055

后来在Jonathan Lewis的书中解释如果隐藏参数_table_scan_cost_plus_one为true则Oracle在全表扫描或者索引ffs时会默认+1 。  

  1* SELECT   ksppinm, ksppstvl, ksppdesc FROM   x$ksppi x, x$ksppcv y   WHERE   x.indx = y.indx AND  ksppinm ='_table_scan_cost_plus_one'

KSPPINM                        KSPPSTVL   KSPPDESC

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

_table_scan_cost_plus_one      TRUE       bump estimated full table scan and index ffs cost by one

如果加1此时的Cost为420.384055 与10053跟踪结果还差1 ，基本接近了。但是目前还是差1，这个目前无从知晓Oracle哪里做了调整。