Db_file_multiblock_read_count

[pranavgovind]

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

[ixion]

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

[pranavgovind]

Hi, Thanks for the reply. can you please send me the link(documentation) to read about this?

[slimdave]

http://docs.oracle.com

[pranavgovind]

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?

[slimdave]

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.

[ixion]

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

[tamilselvan]

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

[slimdave]

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.

[ixion]

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.