昨天测试了非工作量模式下Oracle11gR2全表扫描的成本计算，现在测试一下在工作量模式下Oracle11gR2全表扫描的成本计算
首先讲表blocks增加到10003个
SQL> select owner,blocks from dba_tables where table_name='TEST' and owner='TEST';
OWNER BLOCKS

TEST 10003
然后人工设置工作量的CPUSPEED=2500，单块读等于5，多块读等于30，MBRC等于12
SQL> begin
dbms_stats.set_system_stats('CPUSPEED',2500);
dbms_stats.set_system_stats('SREADTIM',5);
dbms_stats.set_system_stats('MREADTIM',30);
dbms_stats.set_system_stats('MBRC',12);
end;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
利用explain plan得到CPU_COST---这里等于 72735764
SQL> explain plan for select count(*) from test;
Explained.
SQL> select cpu_cost from plan_table;

CPU_COST

72735764
成本计算公式如下：
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=BLOCKS/MBCR=10003/12, mreadtim=30

CPUCycles / cpuspeed / 1000 ---CPUCycles=PLAN_TABLE.CPU_COST,cpuspeed=2500
) / sreadtime
所以人工计算的成本等于：
SQL> select ceil(10003/12*30/5)+ceil(72735764/2500/5/1000)+1 from dual;

CEIL(10003/12*30/5)+CEIL(72735764/2500/5/1000)+1

5009
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 | 5009 (1)| 00:00:26 |
| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 10000 | 5009 (1)| 00:00:26 |

人工计算的cost正好等于Oracle计算的Cost 这里也说明Oracle11gR2 在工作量模式下，全表扫描的成本计算方法依然同Oracle9i,Oracle10g
工作量模式下，从全表扫描的成本可以看出，参数db_file_multiblock_read_count 的更改对全表扫描成本计算没有影响，有影响的是MBRC，举个例子：
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE

db_file_multiblock_read_count integer 16
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 | 5009 (1)| 00:00:26 |
| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 10000 | 5009 (1)| 00:00:26 |

SQL> alter session set db_file_multiblock_read_count=32;
Session altered.
SQL> select count(*) from test;

Execution Plan

Plan hash value: 1950795681

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 5009 (1)| 00:00:26 |
| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 10000 | 5009 (1)| 00:00:26 |

可以看到更改db_file_multiblock_read_count对于成本没有任何影响，因为工作量模式下的COST只跟MBRC有关。