全表扫描的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