SSTIOMAX is an internal parameter/constant used by oracle, which limits the maximum amount of data transfer in a single IO of a read or write operation.
This parameter is fixed and cannot be tuned/changed.
Relationship between SSTIOMAX and db_file_multiblock_read_count (MBRC)
More often than not, DBAs try to increase the db_file_multiblock_read_count parameter (which can be set in the init.ora), in an attempt to optimize the IO performance of the read and write operations.
Normally, with a higher value of MBRC, the IO performance is expected to be better. So, users tend to increase this parameter to a higher value, in case they find it beneficial. But, there is a limitation on this.
The limitation is, the product of db_block_size and MBRC cannot exceed the SSTIOMAX. For example:
db_block_size * db_file_multiblock_read_count <= SSTIOMAX
(which is predefined for a particular version of oracle)
If the value of the product exceeds this, then the value of db_file_multiblock_read_count set in the init.ora is ignored and it is set as follows:
db_file_multiblock_read_count = SSTIOMAX/db_block_size (rounded)
For example:
If the SSTIOMAX is 128K, and the db_block_size is 8k ,then the maximum value to which the MBRC can be raised is 16. If it is set to a higher value (say 20), then the value set in init.ora (20 in this case) is ignored and MBRC is
assigned a value of 16.
You can see the actual value of MBRC by querying the V$PARAMETER view or by issuing 'show parameter db_file_multiblock_read_count' in the svrmgrl.
If the limitation is violated, then the value that is displayed might be different from the value that is set in the init.ora
Value of SSTIOMAX in oracle 7 and oracle 8
The value of SSTIOMAX has been raised in newer versions of oracle.
In Oracle 7.3 --> 128K
(Patch exists for 7.3.2.2 and 7.3.2.3 and 7.3.3.4 in which the limit has been raised to 1 MB)
From Oracle 8.0.5 --> 1 MB
Examples of possible combinations of SSTIOMAX and MBRC
For a db_block_size of 2K:
In oracle 7.3, maximum value for MBRC = 128/2 = 64
In oracle 8.0.5, maximum value for MBRC = 1024/2 = 512
0 comments:
Post a Comment