DB_FILE_MULTIBLOCK_READ_COUNT 与性能有关的参数

一、环境
Oracle 11g RAC

二、测试过程

SQL> show parameter db_fileNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_file_multiblock_read_count        integer     128db_file_name_convert                 string      /oracle/oradata/, +DATADGdb_files                             integer     200SQL> set timing onSQL> select count(*) from tt;  COUNT(*)----------   5524288Elapsed: 00:00:11.50SQL> alter system flush buffer_cache;System altered.Elapsed: 00:00:00.20SQL> alter system flush shared_pool;System altered.Elapsed: 00:00:00.39SQL> alter session set db_file_multiblock_read_count=16;Session altered.Elapsed: 00:00:00.00SQL> select count(*) from tt;  COUNT(*)----------   5524288Elapsed: 00:00:08.91SQL> alter system flush buffer_cache; System altered.Elapsed: 00:00:00.12SQL> alter system flush shared_pool;System altered.Elapsed: 00:00:00.06SQL> alter session set db_file_multiblock_read_count=32;Session altered.Elapsed: 00:00:00.00SQL> select count(*) from tt;  COUNT(*)----------   5524288Elapsed: 00:00:07.87SQL> alter system flush buffer_cache; System altered.Elapsed: 00:00:00.14SQL> alter system flush shared_pool;System altered.Elapsed: 00:00:00.06SQL> alter session set db_file_multiblock_read_count=64;Session altered.Elapsed: 00:00:00.00SQL> select count(*) from tt;  COUNT(*)----------   5524288Elapsed: 00:00:07.05SQL> alter system flush buffer_cache;System altered.Elapsed: 00:00:00.15SQL> alter system flush shared_pool;System altered.Elapsed: 00:00:00.06SQL> alter session set db_file_multiblock_read_count=128;Session altered.Elapsed: 00:00:00.00SQL> select count(*) from tt;  COUNT(*)----------   5524288Elapsed: 00:00:06.62SQL> SQL> 

三、小结
1、对于全表扫描来说多块读，增加每次读取的块数，可以提高性能。
2、在OLTP的系统中建议此参数设置为8、16、32
3、在OLAP的系统中建议此参数设置为128最大值