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