在多块读的情况下,db_file_multiblock_read_count的值指定了一次最多可读取的数据块数目。实际情况下一次IO很难达到该参数的值。
《性能诊断艺术》里面写着有三中情况会导致一次多块读的数目小于次参数
1、读段头时单块读
2、物理读不能跨越多个区
3、部分数据块已经在高速缓存中,除非是直接读,否则不会重新读取。
因为这个原因在系统统计信息中有MBRC(多块度的平均数)。
db_file_multiblock_read_count值越大,越倾向于多块读操作(FFS和INDEX_FFS)。
ys@BBK10G> create table t as select * from dba_objects;
Table created.
sys@BBK10G> show parameter db_file_m
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
sys@BBK10G> set autot trace exp
sys@BBK10G> select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44705 | 7727K| 157 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 44705 | 7727K| 157 (2)| 00:00:02 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
sys@BBK10G> alter session set db_file_multiblock_read_count = 32;
Session altered.
sys@BBK10G> select * from t;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44705 | 7727K| 139 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 44705 | 7727K| 139 (2)| 00:00:02 |
--------------------------------------------------------------------------
sys@BBK10G> alter session set db_file_multiblock_read_count = 128;
Session altered.
sys@BBK10G> select * from t;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44705 | 7727K| 125 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 44705 | 7727K| 125 (2)| 00:00:02 |
--------------------------------------------------------------------------
可以看出该参数越大,优化器认为全表扫描的代价(Cost)越小。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25361369/viewspace-712975/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25361369/viewspace-712975/