Auto-Tuned DB_FILE_MULTIBLOCK_READ_COUNT Parameter

April 10, 2010

There were a couple of recent threads on the OTN forums about the behavior of the DB_FILE_MULTIBLOCK_READ_COUNT parameter’s auto-tuning capability which was introduced with Oracle Database 10.2.0.1.  The threads included this discussion about a problem where the DB_FILE_MULTIBLOCK_READ_COUNT parameter refused to remain at a value of 0, and this discussion that described a problem where Oracle Database was auto-tuning the parameter to a value of 36.

I found the second discussion to be more interesting than the first, but in the first discussion I provided the following quotes from 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.”

While the documentation did not explicitly state so, I suspected that the size of the buffer cache might also contribute to the value of the auto-tuned DB_FILE_MULTIBLOCK_READ_COUNT parameter’s value.  In the same discussion thread Jonathan Lewis indicated that the DB_CACHE_SIZE parameter was part of the calculation.  More or less the same answer, so my suspicion might have some merit.  I think that we need a test case to see if the size of the buffer cache actually has an impact on the auto-tuned parameter.

I built a new database in 64 bit Oracle Database 11.2.0.1 with an 8KB block size with all of the SGA parameters unspecified (the memory target parameters were also left unspecified), except for SGA_TARGET and SGA_MAX_SIZE.  The PGA_AGGREGATE_TARGET was set to 1800MB.  For the test case I altered the value of SGA_TARGET and SESSIONS (the value of which apparently rounds up to the nearest multiple of 8).  The results of the test follow (the value of Database Buffers also decreased for the 8000M tests as the number of sessions increased, but the changing values were not recorded):

SGA_TARGET=8000M
SESSIONS   DB_FILE_MULTI   Database Buffers
--------   -------------   ----------------
    6000             128
    6400             128      6,979,321,856
    6448             127      6,979,321,856
    6504             126
    7000             117
    8000             102
   16000              51
   32000              25
   64000              12

-

SGA_TARGET=4000M
SESSIONS   DB_FILE_MULTI   Database Buffers
--------   -------------   ----------------
    3000             128      3,338,665,984
    3056             128      3,338,665,984
    3080             127      3,338,665,984
    3104             126      3,338,665,984
    3128             125      3,338,665,984
    3152             125      3,338,665,984
    6304              62      3,338,665,984
    6400              61      3,338,665,984
   12608              31      3,137,339,392
   25216              15      2,432,696,320
   50432               7      1,056,964,608
   64000               6        251,658,240

-

SGA_TARGET=2000M
SESSIONS   DB_FILE_MULTI   Database Buffers
--------   -------------   ----------------
    1400             128      1,560,281,088
    1504             123      1,560,281,088
    2000              92      1,560,281,088
    4000              46      1,560,281,088
    8000              23      1,358,954,496
   16000              11        905,969,664

In general, as the number of sessions doubles, the value of the auto-tuned DB_FILE_MULTIBLOCK_READ_COUNT is 1/2 of its previous value.  With the 8000MB SGA_TARGET, the DB_FILE_MULTIBLOCK_READ_COUNT hit 12 at 64,000 sessions.  With the 4000MB SGA_TARGET the DB_FILE_MULTIBLOCK_READ_COUNT hit 6 at 64,000 sessions.  With the 2000MB SGA_TARGET, the DB_FILE_MULTIBLOCK_READ_COUNT hit 11 at 16,000 sessions, which is roughly the same value as when the SGA_TARGET was 4 times larger with a SESSIONS parameter configured 4 times larger.

Pop quiz time:

1. What is the mathematical formula that determines the value of DB_FILE_MULTIBLOCK_READ_COUNT?  There is a chance that the above results might be different for a different release of Oracle Database.

2. Is the SGA_TARGET parameter, the DB_CACHE_SIZE parameter, the __DB_CACHE_SIZE hidden parameter, or the sum of the various buffer pool parameters the determining factor of the DB_FILE_MULTIBLOCK_READ_COUNT value (in addition to the value of the SESSIONS parameter)?  More tests are probably needed.

3. Assuming that the original poster in the OTN thread was using an 8KB block size, how was an auto-tuned value of 36 achieved for the DB_FILE_MULTIBLOCK_READ_COUNT parameter?