HASH_MULTIBLOCK_IO_COUNT specifies how many sequential blocks a hash join reads and writes in one I/O. The maximum value is operating system dependent. It is always less than the maximum I/O size of the operating system expressed as Oracle blocks (MAX_IO_SIZE / DB_BLOCK_SIZE).
You need not set or change the value of this parameter, because Oracle computes the value individually for every query. If you let Oracle do the automatic computation, the value of the parameter appears as 0 in the V$PARAMETER dynamic performance view.
Oracle Corporation does not recommend that you set or change the value of this parameter. If you must set it to investigate its effect on performance, make sure that the following formula remains true:
R / M <= Po2(M/C)
where:
R = number of bytes in the smaller relation to be joined. The number of bytes is the product of the size of each column in the smaller relation times the number of rows in that relation.
M = HASH_AREA_SIZE * 0.9
C = HASH_MULTIBLOCK_IO_COUNT * DB_BLOCK_SIZE
Po2(n) = a function that returns the largest power of 2 that is smaller than its argument.
Note: If you are using Oracle's multi-threaded server architecture, Oracle ignores any value you set for this parameter, and instead uses a value of 1.