7 Replies Latest reply: Apr 8, 2010 8:16 AM by Jonathan Lewis RSS

    DB_FILE_MULTIBLOCK_READ_COUNT

    Mohammed Mehraj Hussain
      Hi all,

      Currently we are in 10204 on windows 2003 server , and we are using DB_FILE_MULTIBLOCK_READ_COUNT=32 in our database .

      We are planning to move from 10204 to 11201 on windows 2008 server .
      so as per this i commented this DB_FILE_MULTIBLOCK_READ_COUNT in our init.ora. And created the new database , i noticed my the default value i got for this is 36.

      can anybody comment on this.


      i have read this article , But cant able to come to a conclusion
      http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams053.htm#REFRN10037

      Regards,
      Mohammed
        • 1. Re: DB_FILE_MULTIBLOCK_READ_COUNT
          Aman....
          You may want to read this thread and the other threads linked in it by Charles.
          Re: db_file_multiblock_read_count=0

          HTH
          Aman....
          • 2. Re: DB_FILE_MULTIBLOCK_READ_COUNT
            Pavan Kumar
            Hi,

            AFAIK, when we don't specify any values for some specify parameters which influence the Optimizer (as input), will be calculated by ORACLE internally based on OS specific values or depends on hardware. As you referred the doc
            the default value is " maximum I/O size that can be efficiently performed and is platform-dependent". As per the hard and OS, the maximum I/O size is 32 OS Blocks. That is rate of fetch of single OS block ,else you can set it manually that depends on the expert level of your skills and check the benchmarking.

            HTH

            - Pavan Kumar N
            - ORACLE 9i/10g - OCP
            http://www.oracleinternals.blogspot.com
            • 3. Re: DB_FILE_MULTIBLOCK_READ_COUNT
              Mohammed Mehraj Hussain
              But how come my value is set to 36
              • 4. Re: DB_FILE_MULTIBLOCK_READ_COUNT
                Aman....
                Its not mandatory that the value would be always 32 only. The value is picked up by oracle based on what the o/s can supply to you. So in your case, its 36 blocks.

                HTH
                Aman....
                • 5. Re: DB_FILE_MULTIBLOCK_READ_COUNT
                  Pavan Kumar
                  Hi,

                  I don't have any idea how the algorithm works out. Definitly ORACLE Support can answer your question and Master's of ORACLE can answer it. It's the algorithm and based some calcualtions it sets the value else you can waits for replies from "jonathan lewis" or "Tanel Poder" or "Charles"

                  - Pavan Kumar N
                  - ORACLE 9i/10g - OCP
                  http://www.oracleinternals.blogspot.com
                  • 6. Re: DB_FILE_MULTIBLOCK_READ_COUNT
                    Charles Hooper
                    Mohd Mehraj Hussain wrote:
                    But how come my value is set to 36
                    I might be misunderstanding Pavan's comment about the maximum I/O size being 32 OS Blocks - I am not aware of that limit on Windows. The OS block size is typically smaller than the Oracle block size - I thought that the OS block size (probably should say cluster size) is 512 bytes, but it looks like my information is several years out of date:
                    http://support.microsoft.com/kb/314878
                    http://support.microsoft.com/kb/140365
                    It looks like the default cluster size is 4KB for NTFS formatted volumes up to 16TB.

                    With that said, Oracle is still able to auto-set DB_FILE_MULTIBLOCK_READ_COUNT to 128 when an 8KB block size is used on the Windows platform.

                    Try this command, and then bounce the database:
                    ALTER SYSTEM RESET DB_FILE_MULTIBLOCK_READ_COUNT SCOPE=SPFILE SID='*';
                    If you do not receive an error when executing the above command, that means that Oracle is not using your init.ora file, but instead an spfile. The above command will unset (remove) the DB_FILE_MULTIBLOCK_READ_COUNT parameter from the spfile, allowing Oracle to automatically set the parameter.

                    We might need to see the rest of your parameters from V$PARAMETER. The documentation suggests that a value smaller than 128 (assuming an 8KB block size) will be used if the SESSIONS parameter is set to a high value (and probably the buffer cache size also factors into the equation).

                    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.
                    • 7. Re: DB_FILE_MULTIBLOCK_READ_COUNT
                      Jonathan Lewis
                      Mohd Mehraj Hussain wrote:

                      We are planning to move from 10204 to 11201 on windows 2008 server .
                      so as per this i commented this DB_FILE_MULTIBLOCK_READ_COUNT in our init.ora. And created the new database , i noticed my the default value i got for this is 36.

                      can anybody comment on this.
                      One of the internal limits on db_file_multiblock_read_count is derived from db_cache_size / processes. Since your value comes to 128 that's an indication that Oracle "thinks" your cache allocation is too small for the number of processes you have declared or, putting it another way, the number of processes you want to support is too large for the memory you have allocated to the cache.

                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      http://www.jlcomp.demon.co.uk

                      To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                      {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                      fixed format
                      .
                      
                      There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
                      
                      +"Science is more than a body of knowledge; it is a way of thinking"+
                      +Carl Sagan+