Index created.
INDEX FAST FULL SCAN只需要扫描叶子块，并且采用多块读，所以查询LEAF_BLOCKS
SQL> select leaf_blocks from user_indexes where index_name='I_TEST_OWNER';
LEAF_BLOCKS
-----------
22
SQL> explain plan for select count(owner) from test;
Explained.
SQL> select cpu_cost from plan_table;
CPU_COST
----------
1356672
SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEED 2500
CPUSPEEDNW 2696.05568
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC 12
MREADTIM 30
SLAVETHR
SREADTIM 5
9 rows selected.
因为MBRC不为空，所以CBO会采用工作量模式计算Cost
INDEX FAST FULL SCAN 成本计算公式如下：
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
CPUCycles / cpuspeed /1000
) / sreadtime
#SRds - number of single block reads
#MRds - number of multi block reads
#CPUCyles - number of CPU cycles
sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second
Cost = (
#SRds * sreadtim + ---SRds=0
#MRds * mreadtim + ---MRds=Leaf_Blocks/MBCR=22/12, mreadtim=30
CPUCycles / cpuspeed / 1000 ---CPUCycles=PLAN_TABLE.CPU_COST,cpuspeed=2500
) / sreadtime
所以人工计算的成本等于：
SQL> select ceil(22/12*30/5)+ceil(1356672/2500/5/1000)+1 from dual; ---+1是因为 _table_scan_cost_plus_one设置为true
CEIL(22/12*30/5)+CEIL(1356672/2500/5/1000)+1
--------------------------------------------
13
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 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST_OWNER | 10000 | 50000 | 13 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
现在采用非工作量统计来计算Cost
SQL> begin
dbms_stats.set_system_stats('CPUSPEED',0);
dbms_stats.set_system_stats('SREADTIM',0);
dbms_stats.set_system_stats('MREADTIM',0);
dbms_stats.set_system_stats('MBRC',0);
end;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(select value
from v$parameter
where name = 'db_file_multiblock_read_count') *
2 3 4 5 (select value from v$parameter where name = 'db_block_size') /
6 (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"
7 from dual;
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一样，没有变化。