oracle dbfilemultiblockreadcount,DB_FILE_MULTIBLOCK_READ_COUNT 与性能有关的参数

二、测试过程

SQL> show parameter db_file

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_multiblock_read_count integer 128

db_file_name_convert string /oracle/oradata/, +DATADG

db_files integer 200

SQL> set timing on

SQL> select count(*) from tt;

COUNT(*)

----------

5524288

Elapsed: 00:00:11.50

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.20

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.39

SQL> alter session set db_file_multiblock_read_count=16;

Session altered.

Elapsed: 00:00:00.00

SQL> select count(*) from tt;

COUNT(*)

----------

5524288

Elapsed: 00:00:08.91

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.12

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.06

SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

Elapsed: 00:00:00.00

SQL> select count(*) from tt;

COUNT(*)

----------

5524288

Elapsed: 00:00:07.87

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.14

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.06

SQL> alter session set db_file_multiblock_read_count=64;

Session altered.

Elapsed: 00:00:00.00

SQL> select count(*) from tt;

COUNT(*)

----------

5524288

Elapsed: 00:00:07.05

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.15

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.06

SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

Elapsed: 00:00:00.00

SQL> select count(*) from tt;

COUNT(*)

----------

5524288

Elapsed: 00:00:06.62

SQL>

SQL>

三、小结

1、对于全表扫描来说多块读，增加每次读取的块数，可以提高性能。

2、在OLTP的系统中建议此参数设置为8、16、32

3、在OLAP的系统中建议此参数设置为128最大值

原文：http://blog.51cto.com/roidba/2103792