Oracle Performance Tuning Tips

Finding the maximum effective multiblock read count

As explained here you should use the maximum effective multiblock read size when using unbuffered I/O. Unfortunately, finding and using the maximum effective multiblock read count possible for your database is not always easy. The first trap to be aware of is that Oracle does not complain about attempts to set the multiblock read count to values that are larger than the maximum value allowed under the operating system. It just silently sets the parameter to the maximum allowed value (or db_block_buffers/4, if your cache size less than 4 times the maximum allowed value).

The second trap to be aware of is that even if you set the multiblock read count to the maximum allowed value for your operating system, Oracle might not respect that setting anyway. This has to do with which system call, read() or readv(), is being used, whether the datafile is raw or file system based, and the overhead of I/O system calls under that operating system. The details are sufficiently complex that it is best to simply establish the maximum effective multiblock read count with a test. On Unix you can use the APT script multiblock_read_test.sql to do this. It simply performs a full table scan with event 10046 set at level 8, and then looks at parameter p3 of the db file scattered read waits to find the size of the multiblock reads actually being performed.

Prior to Oracle9i, it is important that you not set the db_file_multiblock_read_count parameter to a value greater than this maximum effective value. This is because the optimizer is influenced in its choice of execution plans by the value of this parameter, and if it is allowed to think that full scans are any cheaper than they in fact are, then it will sometimes choose full table scans rather than index based access when in fact an index based access path would be faster. Oracle9i, has a new multiblock I/O costing model that can cost full scans more accurately based on statistics about the typical multiblock read size and response time. These statistics can be gathered with DBMS_STATS.GATHER_SYSTEM_STATS. Of course, in the absence of such statistics, the old costing model will be used.

If you are using the maximum effective multiblock read size and find that the optimizer is choosing inappropriate full table scans, you may be tempted to reduce the multiblock read size to convince the optimizer to choose index based access paths. However, in such cases it is better to understate the cost of index based access by tuning the optimizer_index_cost_adj parameter, rather than to reduce the multiblock I/O size, because that would also sacrifice I/O efficiency.

Ixora Pty Ltd.   All rights reserved.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home