create index i_test_owner on test(owner);
SQL> create index i_test_owner on test(owner);
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