全表掃描的COST計算

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

Table created.

SQL> alter table test pctfree 99 pctused 1;

Table altered.

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

1 row created.

SQL> alter table test minimize records_per_block;

Table altered.

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

999 rows created.

commit;

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;
 /

PL/SQL procedure successfully completed.

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

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

SQL> alter system set db_file_multiblock_read_count=16;

System altered.

SQL> set autot trace
SQL> select count(*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Cost (%CPU)| Time   |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 1 |   220   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |   | 1 |        |   |
|   2 |   TABLE ACCESS FULL| TEST |  1000 |   220   (0)| 00:00:01 |
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
  38  recursive calls
   0  db block gets
       1043  consistent gets
   0  physical reads
   0  redo size
 542  bytes sent via SQL*Net to client
 552  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   5  sorts (memory)
   0  sorts (disk)
   1  rows processed

全表掃描cost：

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秒

單塊讀的時間 = 尋道尋址+讀一個塊到內存的時間

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

PNAME        PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW   3308.9701
IOSEEKTIM          10
IOTFRSPEED        4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

9 rows selected.

單塊讀的時間：

sreadtim=ioseektim+db_block_size/iotfrspeed=10+9=8192byte/4096=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;

多塊讀：10 + 16*8k/4k=42

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;

cpuspeed - CPU cycles per second CPU頻率(單位MHZ)

#CPUCyles - number of CPU cycles CPU時鐘周期數

#CPUCyles - number of CPU cycles CPU時鐘周期數

explain plan for select count(*) from test;

SQL> select cpu_cost from plan_table;

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

   7271440

cost值：

SQL> select ceil((1000/16*42+7271440/3308.9701/1000)/12) from dual;

CEIL((1000/16*42+7271440/3308.9701/1000)/12)
--------------------------------------------
      219