Oracle 11g R2 INDEX FAST FULL SCAN 成本计算(二)

mreadtim
----------
42
SQL> 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; 2 3 4

sreadtim
----------
12

SQL> select cpu_cost from plan_table;

CPU_COST
----------
1356672

根据成本计算公式

Cost = (
#SRds * sreadtim + ---SRds=0
#MRds * mreadtim + ---MRds=Leaf_Blocks/db_file_multiblock_read_count=22/16, mreadtim=42
CPUCycles / cpuspeed / 1000 ---CPUCycles=PLAN_TABLE.CPU_COST,cpuspeed=2696.05568
) / sreadtime

那么手工计算的Cost等于：

SQL> select ceil(22/16*42/12)+ceil(1356672/2696.05568/12/1000)+1 from dual;

CEIL(22/16*42/12)+CEIL(1356672/2696.05568/12/1000)+1
----------------------------------------------------
7
SQL> set autot trace
SQL> select count(owner) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1992658997

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST_OWNER | 10000 | 50000 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

从实验中可以看到，INDEX FAST FULL SCAN 在11gR2中的成本算法依然和9i,10g一样，没有变化。