在CBO優化模式下,也是可以調整全表掃面的成本的，這個小技術需要用到一個Oracle的一個重要的參數db_file_multiblock_read_count，這個參數控制著Oracle在進行全表掃描時單次I/O讀取的塊數。因此這個參數值越大，對應的全掃描的成本就越低。
下面通過這個實驗簡單展示一下這個參數的使用效果。我的系統中有一個70萬行的表。
SQL> select count(*) from order_detail$;
COUNT(*)
-------------------
708437
1、查一下系統中db_file_multiblock_read_count參數的默認值
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
2、使用autotrace功能查看一下執行計劃，得到的cost是1540
SQL> set autotrace traceonly explain
SQL> select * from order_detail$;
Execution Plan
----------------------------------------------------------
Plan hash value: 979479613
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
| 0 | SELECT STATEMENT | | 703K| 42M| 1540 (5)| 00:00:1 9 |
| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1540 (5)| 00:00:19 |
---------------------------------------------------------------------------------------------------------------------------------
3、修改 db_file_multiblock_read_count為32，得到的cost是1364
SQL> alter session set db_file_multiblock_read_count=32;
Session altered.
SQL> select * from order_detail$;
Execution Plan
----------------------------------------------------------
Plan hash value: 979479613
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| 0 | SELECT STATEMENT | | 703K| 42M| 1364 (5)| 00:00:17|
| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1364 (5)| 00:00:17 |
---------------------------------------------------------------------------------------------------------------------------------
4、修改 db_file_multiblock_read_count為64，得到的cost是1276
SQL> alter session set db_file_multiblock_read_count=64;
Session altered.
SQL> select * from order_detail$;
Execution Plan
---------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 979479613
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 703K| 42M| 1276 (6)| 00:00:16|
| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1276 (6)| 00:00:16 |
---------------------------------------------------------------------------------------------------------------------------------
5、修改 db_file_multiblock_read_count為128，得到的cost是1233
SQL> alter session set db_file_multiblock_read_count=128;
Session altered.
SQL> select * from order_detail$;
Execution Plan
----------------------------------------------------------
Plan hash value: 979479613
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 703K| 42M| 1233 (6)| 00:00:15|
| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1233 (6)| 00:00:15 |
---------------------------------------------------------------------------------------------------------------------------------
6、修改 db_file_multiblock_read_count為256，得到的cost是1233
SQL> alter session set db_file_multiblock_read_count=256;
Session altered.
SQL> select * from order_detail$;
Execution Plan
-----------------------------------------------------------------------------------------------
Plan hash value: 979479613
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 703K| 42M| 1233 (6)| 00:00:15|
| 1 | TABLE ACCESS FULL| ORDER_DETAIL$ | 703K| 42M| 1233 (6)| 00:00:15 |
---------------------------------------------------------------------------------------------------------------------------------
小結
Oracle一次I/O所讀的數據庫個數，除了跟db_file_multiblock_read_count參數的有關，還跟段中的每個區大小有關，已經操作系統及硬件的I/O能力有關；單從Oracle層面來講，實驗中該表的每個區大小是1M，所有最大I/O塊個數是128，再增加db_file_multiblock_read_count參數，也不能提供一次I/O的塊個數，所有cost不會下降。
在Oracle10g中，db_file_multiblock_read_count參數的默認值是16，在oracle11g中，db_file_multiblock_read_count參數的默認值是128，雖然修改參數對於區大小不是很大的段來講，效果變化不大，但在某些數據倉庫系統中，可能會選擇較大的區單位，那麼增大該參數，對減少全表掃描的I/O次數是非常有效的。
