DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Db_file_multiblock_read_count

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    Db_file_multiblock_read_count

    All,

    My understanding is, DB_FILE_MULTI_BLOCK_READ_COUNT is used when optimizer use the full table scan.

    How do we set the value for db_file_multiblock_read_count.

    Is this the right formula to determine the DB_FILE_MULTIBLOCK_READ_COUNT? If so, how do we determine max I/O chunk size value?

    max I/O chunk size
    db_file_multiblock_read_count = -------------------
    db_block_size

  2. #2
    Join Date
    Jun 2006
    Posts
    259
    Yes, that is what the DB_FILE_MULTIBLOCK_READ_COUNT does.

    After Reading the documentation what would you set it to and why?

    What type of system? Is it a DW/OLTP/DSS ? How many sessions.
    The documentations recomendation from 8 - 16 has worked well for me in the past on OLTP systems.
    YMMV

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    Hi, Thanks for the reply. can you please send me the link(documentation) to read about this?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2005
    Posts
    195
    Hello, thanks for the link. Now i understand better about this parameter now. The maximum value is always less than the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE).


    How would i determine the max I/O size . What is max I/O size?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by pranavgovind
    The maximum value is always less than the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE).
    Nope, the value should be the same as the system's maximum i/o size divided by the system tablespace block size.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by slimdave
    Nope, the value should be the same as the system's maximum i/o size divided by the system tablespace block size.
    I respectfully disagree.

    This parameter greatly dependes upon the type of system you are setting up. If OLTP/DSS then a smaller number in the 4-16 is probably a very good choice.

    But if its say a DW, then large number up to the maximim I/O size would be a better choice.

    Also, your block size should be greater than or equal to the OS I/O size.

    Regards,
    Ken

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    How would i determine the max I/O size . What is max I/O size?
    Max I/O Size depends upon many factors such as volume manager (Veritas or LVM), stripe size , OS limitation etc.
    Today, the effective max I/O size can be set to 1MB.

    If you are using Veritas volume manager, then vol_maxio parameter tells you the size.

    If you donot use LVM, then the best way to determine is from the extended trace data file (using 10046 events) at level 8.

    Ex:
    You change the db_file_multiblock_read_count = 512.
    set events 10046 at level 8.
    full table scan a big table.
    Look for P3 value in the trace file.

    Tamil

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by ixion
    I respectfully disagree.

    This parameter greatly dependes upon the type of system you are setting up. If OLTP/DSS then a smaller number in the 4-16 is probably a very good choice.

    But if its say a DW, then large number up to the maximim I/O size would be a better choice.

    Also, your block size should be greater than or equal to the OS I/O size.

    Regards,
    Ken
    If you're reducing the MBRC to tilt the optimizer away from table scans then you ought instead to consider either gathering system statistics (to benchmark the relative performance of single and multiblock reads) or set optimizer_index_cost_adj to give roughly the same info.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Jun 2006
    Posts
    259
    I see where your coming from, but the original post was what value to set it as... How about not setting it at all. Seems that Optimizer is not affected if it is not set, being left to its default value of the MaxIO/block size.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width