This discussion is archived
5 Replies Latest reply: Apr 7, 2010 3:54 AM by CharlesHooper RSS

db_file_multiblock_read_count=0

540353 Newbie
Currently Being Moderated
Hi,

I am using oracle 11.1.0.6 in HP-UX. in that the db_file_multiblock_read_count was 128 then i changed to 0 by using

alter system set db_file_multiblock_read_count=0 scope=both; but if i checked using show parameter db_file_multiblock_read_count its showing as 128.

i need to set the db_file_multiblock_read_count parameter to 0 without restarting the DB. my db is in spfile.

Rgds
  • 1. Re: db_file_multiblock_read_count=0
    sb92075 Guru
    Currently Being Moderated
    SQL> select value from v$parameter where name = 'db_file_multiblock_read_count';
    
    VALUE
    --------------------------------------------------------------------------------
    16
    
    SQL> alter system set db_file_multiblock_read_count=0 scope=both;
    
    System altered.
    
    SQL> select value from v$parameter where name = 'db_file_multiblock_read_count';
    
    VALUE
    --------------------------------------------------------------------------------
    1
  • 2. Re: db_file_multiblock_read_count=0
    540353 Newbie
    Currently Being Moderated
    SQL> select value from v$parameter where name = 'db_file_multiblock_read_count';

    VALUE
    --------------------------------------------------------------------------------
    128

    SQL> alter system set db_file_multiblock_read_count=0 scope=both;

    System altered.

    SQL> select value from v$parameter where name = 'db_file_multiblock_read_count';

    VALUE
    --------------------------------------------------------------------------------
    128

    SQL>

    for me its showing as its please help
  • 3. Re: db_file_multiblock_read_count=0
    CharlesHooper Expert
    Currently Being Moderated
    user537350 wrote:
    Hi,

    I am using oracle 11.1.0.6 in HP-UX. in that the db_file_multiblock_read_count was 128 then i changed to 0 by using

    alter system set db_file_multiblock_read_count=0 scope=both; but if i checked using show parameter db_file_multiblock_read_count its showing as 128.

    i need to set the db_file_multiblock_read_count parameter to 0 without restarting the DB. my db is in spfile.

    Rgds
    It appears that sb92075 is running Oracle Database 10.2.0.1, 10.2.0.2, or 10.2.0.3 (or possibly an older release). Prior to 10.2.0.4 setting DB_FILE_MULTIBLOCK_READ_COUNT TO 0 actually caused Oracle to set the parameter to 1, thus causing all multi-block read requests to become single block read requests. Translated, if DB_FILE_MULTIBLOCK_READ_COUNT was 128 before, up to 128 blocks could be read in a single read request; and after the change reading 128 blocks would require 128 read requests of 1 block each. Starting with Oracle 10.2.0.4, setting the parameter to 0 has the same effect as unsetting the parameter, allowing Oracle to auto-set the parameter to allow 1MB read requests.

    user537350, why do you want to set this parameter to 0, and what do you expect to see when that happens?

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 4. Re: db_file_multiblock_read_count=0
    540353 Newbie
    Currently Being Moderated
    As per oracle recommandation i am using the parameter value to 0.


    Rgds,
  • 5. Re: db_file_multiblock_read_count=0
    CharlesHooper Expert
    Currently Being Moderated
    user537350 wrote:
    As per oracle recommandation i am using the parameter value to 0.
    -----
    For those running Oracle 10g R2, see the following OTN thread summary (under the heading Item 2: Tests highlighting bugs) and PDF file. I included a test case in that PDF file that showed what happened when DB_FILE_MULTIBLOCK_READ_COUNT was set to 0:
    http://jonathanlewis.wordpress.com/2008/07/19/block-sizes/
    -----

    I think that it might be a good idea to quickly double-check the Oracle documentation:
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams053.htm
    "As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms. Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. 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."

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/iodesign.htm#g35578
    "DB_FILE_MULTIBLOCK_READ_COUNT: The maximum I/O size for full table scans is computed by multiplying this parameter with DB_BLOCK_SIZE. (the upper value is subject to operating system limits). If this value is not set explicitly (or is set to 0), the default value corresponds to the maximum I/O size that can be efficiently performed and is platform-dependent."

    When you unset the DB_FILE_MULTIBLOCK_READ_COUNT parameter, or set it to 0 (starting with Oracle 10.2.0.4) the value of the parameter does not remain unset or set to 0. Instead, Oracle sets it to a value that typically achieves 1MB multiblock read requests. With an 8KB default block size the DB_FILE_MULTIBLOCK_READ_COUNT parameter will likely be set to 128, although the documentation states that the value could be reduced if there are a large number of sessions.

    If the above does not make sense, please provide a link (or tell me where you found the suggestion) to the Oracle recommendation which states that the value of DB_FILE_MULTIBLOCK_READ_COUNT should be set to 0.

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points