Automatic tuning of db_file_multiblock_read_count

db_file_multiblock_read_count曾经是一个经过热烈讨论的初始化参数。该参数只有在对表或者索引进行Full Scan的时候才起作用。


我们知道如下的公式,其中max I/O chunk size跟操作系统有关,但是Oracle文档中也指出大多数操作系统上该值为1M。

db_file_multiblock_read_count = max I/O chunk size / db_block_size

在Oracle10gR2之后的版本(10gR2和11g)中,Oracle数据库已经可以根据系统的IO能力以及Buffer Cache的大小来动态调整该参数值,Oracle建议不要显式设置该参数值。但是根据Oracle官方文档对于此参数的解释:

Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.
Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.


3 Comments Add yours

  1. ynhoo says:


  2. kamus says:

    ynhoo on February 21, 2009 at 9:22 pm said:


    跟设置的buffer cache大小有关系,我的测试环境11.1.0.7中是76

  3. Pingback: db | DDLanG

Leave a Reply

Your email address will not be published. Required fields are marked *