1.准备
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 't_group1');
end;
set timing on;

2.db_file_multiblock_read_count=1
SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

Elapsed: 00:00:00.02
SQL> select /*+no_index(t)*/ count(*)
2 from t_group1 t;

COUNT(*)
----------
4326720

Elapsed: 00:00:03.85

Execution Plan
----------------------------------------------------------
Plan hash value: 1284689789

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28210 (1)| 00:05:39 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_GROUP1 | 4326K| 28210 (1)| 00:05:39 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27957 consistent gets
27945 physical reads
0 redo size
414 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
【小结】cost:28210，cpu:1%，实际用时3.85秒。

3.db_file_multiblock_read_count=32
SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

Elapsed: 00:00:00.01
SQL> select /*+no_index(t)*/ count(*)
2 from t_group1 t;

COUNT(*)
----------
4326720

Elapsed: 00:00:00.76

Execution Plan
----------------------------------------------------------
Plan hash value: 1284689789

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5535 (3)| 00:01:07 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_GROUP1 | 4326K| 5535 (3)| 00:01:07 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27957 consistent gets
27945 physical reads
0 redo size
414 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
【小结】
1.db_file_multiblock_read_count参数由1改为32后，consistent gets没有改变。
2.CPU的使用率由1%提高到3%
3.cost由28210降至5535，成本降低了80%
4.SQL的实际执行时间由3.85降至0.76，执行时间降低了80%。由此调整db_file_multiblock_read_count能大幅提高全表扫描的效率。

【附】
在init.ora中可以设置的参数DB_FILE_MULTIBLOCK_READ_COUNT将有助于提高读取的时间。DB_FILE_MULTIBLOCK_READ_COUNT参数控制在全表扫描时，一次I/O中读入的数据块的数量。
这将减少扫描一张表所需的I/O数量，从而提高了全表扫描的性能。但是，设置DB_FILE_MULTIBLOCK_READ_COUNT参数的结果是优化器可能会执行更多的全表扫描，所以您也需要将
OPTIMIZER_INDEX_COST_ADJ设置为一个数值，例如10，来消除这个问题并驱动索引的使用。

【实验续】继续调整db_file_multiblock_read_count的测试
1.db_file_multiblock_read_count=64
SQL> alter session set db_file_multiblock_read_count=64;

Session altered.

Elapsed: 00:00:00.01
SQL> select /*+no_index(t)*/ count(*)
2 from t_group1 t;

COUNT(*)
----------
4326720

Elapsed: 00:00:00.56

Execution Plan
----------------------------------------------------------
Plan hash value: 1284689789

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5170 (3)| 00:01:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_GROUP1 | 4326K| 5170 (3)| 00:01:03 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27957 consistent gets
27945 physical reads
0 redo size
414 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

2.db_file_multiblock_read_count=128
SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

Elapsed: 00:00:00.00
SQL> select /*+no_index(t)*/ count(*)
2 from t_group1 t;

COUNT(*)
----------
4326720

Elapsed: 00:00:00.55

Execution Plan
----------------------------------------------------------
Plan hash value: 1284689789

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4987 (3)| 00:01:00 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_GROUP1 | 4326K| 4987 (3)| 00:01:00 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27957 consistent gets
27882 physical reads
0 redo size
414 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

3.db_file_multiblock_read_count=8
SQL> alter session set db_file_multiblock_read_count=8;

Session altered.

Elapsed: 00:00:00.01
SQL> select /*+no_index(t)*/ count(*)
2 from t_group1 t;

COUNT(*)
----------
4326720

Elapsed: 00:00:00.61

Execution Plan
----------------------------------------------------------
Plan hash value: 1284689789

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7730 (2)| 00:01:33 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_GROUP1 | 4326K| 7730 (2)| 00:01:33 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27957 consistent gets
27767 physical reads
0 redo size
414 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

4.db_file_multiblock_read_count=16
SQL> alter session set db_file_multiblock_read_count=16;

Session altered.

Elapsed: 00:00:00.03
SQL> select /*+no_index(t)*/ count(*)
2 from t_group1 t;

COUNT(*)
----------
4326720

Elapsed: 00:00:00.47

Execution Plan
----------------------------------------------------------
Plan hash value: 1284689789

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6267 (2)| 00:01:16 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_GROUP1 | 4326K| 6267 (2)| 00:01:16 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27957 consistent gets
27775 physical reads
0 redo size
414 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

【小结】
1.db_file_multiblock_read_count不同值的比较
值 COST SQL实际执行时间
1 28210 3.85秒
8 7730 0.61秒
16 6267 0.47秒
32 5535 0.76秒
64 5170 0.56秒
128 4987 0.55秒
2.上述结果显示，调整db_file_multiblock_read_count能够改善全表扫描的性能。但是不能一味地提高db_file_multiblock_read_count的值，这个值应该设置的恰到好处。
3.为了得到最佳的效率，应该将db_file_multiblock_read_count设置为多少？可以通过测试来得到答案。
4.db_file_multiblock_read_count值的高低受到哪些条件的影响？是磁盘的I/O效率？是CPU的处理效率？还是其他的原因？