Veritas InfoScale™ 8.0.2 Storage and Availability Management for Oracle Databases - AIX, Linux, Solaris

Setting Oracle block reads during sequential scans

The DB_FILE_MULTIBLOCK_READ_COUNT parameter specifies the maximum number of blocks Oracle reads in one I/O operation during a sequential scan. The /etc/system tunable parameter maxphys establishes the maximum physical I/O transfer size at the operating system level. To take advantage of the maximum transfer size, the Oracle init.ora parameter DB_FILE_MULTIBLOCK_READ_COUNT should be set to maxphys/DB_BLOCK_SIZE.

For example, if maxphys is set to 1048576 and DB_BLOCK_SIZE is set to 4096, then DB_FILE_MULTIBLOCK_READ_COUNT should be set to 256.

The DB_FILE_MULTIBLOCK_READ_COUNT parameter specifies the maximum number of blocks Oracle reads in one I/O operation during a sequential scan. A large value for the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter usually yields better I/O throughput. On AIX, this parameter ranges from 1 to 512, but using a value higher than 16 usually does not provide additional performance gain.

When the file system is created on a striped volume, set this parameter to a value that is a multiple of the full stripe size divided by DB_BLOCK_SIZE. Using a full stripe size allows the read operations to take advantage of the full bandwidth of the striped disks during sequential table scan.

Set the DB_FILE_MULTIBLOCK_READ_COUNT to a value that is a multiple of (read_pref_io*read_nstream)/DB_BLOCK_SIZE, but the value should not exceed the value of max_direct_iosz/DB_BLOCK_SIZE.

Use the vxtunefs command to display the value of read_pref_io, read_nstream, and max_direct_iosz, for example:

# vxtunefs /db01

The vxtunefs command displays output similar to the following:

Filesystem i/o parameters for /db01 

read_pref_io  = 65536
read_nstream  = 1
read_unit_io  = 65536 
write_pref_io  = 65536 
write_nstream  = 1
write_unit_io  = 65536 
pref_strength  = 10 
buf_breakup_size  = 262144 
discovered_direct_iosz  = 262144 
max_direct_iosz  = 1048576 
default_indir_size = 8192 

qio_cache_enable = 0

write_throttle = 0

max_diskq = 1048576

initial_extent_size = 8

max_seqio_extent_size = 2048

max_buf_data_size = 8192

hsm_write_prealloc = 0

read_ahead = 1

inode_aging_size = 0

inode_aging_count = 0

fcl_maxalloc = 130150400

fcl_keeptime = 0

fcl_winterval = 3600

oltp_load = 0

For a description of these parameters and the tuning instructions, refer to the vxtunefs(1M) manual page.

The DB_FILE_MULTIBLOCK_READ_COUNT parameter specifies the maximum number of blocks Oracle reads in one I/O operation during a sequential scan. The /etc/system tunable parameter maxphys establishes the maximum physical I/O transfer size at the operating system level. To take advantage of the maximum transfer size, the Oracle init.ora parameter DB_FILE_MULTIBLOCK_READ_COUNT should be set to maxphys/DB_BLOCK_SIZE.

For example, if maxphys is set to 1048576 and DB_BLOCK_SIZE is set to 4096, then DB_FILE_MULTIBLOCK_READ_COUNT should be set to 256.