A higher DB_FILE_MULTIBLOCK_READ_COUNT (MBRC) number can influence the Oracle optimizer to lean toward full scans.The right number for the database depends on the application (DSS or OLTP). Batch processes can benefit from a higher MBRC as it allows full scan operations to complete faster. If the database serves both batch and OLTP processes, you must find a balance. The default value of 8 is rather conservative.
If full scans are the best way to go, you want SQL statements to scan the objects with the maximum value supported by your system. Why waste time with a smaller value? You should find out what the maximum value is and dynamically apply this value to processes that perform large full scans.
There is a limit on MBRCs. It depends on several factors, including sstiomax, DB_BLOCK_SIZE, and DB_BLOCK_BUFFERS. The sstiomax is an Oracle internal limit, which limits the amount of data that can be transferred in a single I/O of a read or write operation. The value is internally set in the Oracle code and varies with the Oracle version. The limit is 128K in earlier versions of Oracle and 1 MB beginning in version 8. The product of DB_BLOCK_SIZE and MBRC cannot exceed the port-specific definition for sstiomax. MBRC must also be smaller than DB_ BLOCK_BUFFERS/4.
Furthermore, MBRC is subject to hardware limits such as the Solaris maxphys and file system maxcontig values. Does this sound like too much to you? It is! The good news is there is a shortcut to finding the limit for your platform. You can set the MBRC to a ridiculously high number for your session, as shown next, and let Oracle figure out what the system can handle. You then simply run a query that scans a table and monitor the progress from another session by querying the V$SESSION_WAIT view in quick successions. The maximum P3 value of the db file scattered read events that belong to the first session is the MBRC limit for your platform. Alternatively, you can monitor the full table scans with trace event 10046. This maximum value is not meant to be set at the database level; rather, it may be applied at the session level to speed up full scans when that is the best way to go.
Leave a Reply