Whenever we have faced performance issue, few questions suddenly
came to our mind like …
Why my index is not being used?
Why my query is going to full table scan instead of index scan?
Is there any fragmentation on segments?
Is there any stale statistics?
Any volume change?
Is there any recent parameter change in database level?
Why I/O performance is very slow?
Yesterday I have found few good articles about MBRC. I just want
summarize and share to you all.
DB_MULTIBLOCK_READ_COUNT
Oracle database improves the performance of
table scans by increasing the number of blocks read in a single I/O
Operation. If the SQL statement is accessing all the records in a table
(full table scan) , it returns many blocks in Single I/O read is better.
Prior to Oracle 10gR2, MBRC parameter
decides, how many blocks can oracle fetch from disk to buffer cache in single
I/O read?
Oracle 10gR2 and 11g - If we are not
explicitly set MBRC parameter; oracle automatically decides the value for MBRC parameter
depending on the operating system optimal I/O size and buffer cache size.
If we set MBRC value is HIGH, what will be
happen?
MBRC value is too high, it will affect the query
access path selection (Query execution plan - oracle optimizer will use the
full table scan instead of index scan. oracle optimizer is not being used INDEXES)
OLTP/Batch Environment: we can set MBRC
value is 4 to 16.
Decision support system/Data warehousing
system: We can set the MBRC value is 1 MB/DB_BLOCK_SIZE in instance or session
level. Most of the DW queries are going FTS and its better to use FTS.
MBRC, DB_BLOCK_SIZE and SSTIOMAX having
relationship and these parameters decide the I/O Performance of oracle.
SSTIOMAX
– This one is oracle internal parameter and cannot be changed this parameter.
This parameter can vary on oracle version.
SSTIOMAX
parameter value can decides, maximum amount of data transfer in a single IO of
a read or write operation.
SSTIOMAX Value:
1)
Oracle 7.3 = 128 K
2)
Oracle 8.0.5 = 1 MB
3)
Upto Oracle 10.2.0.5 = 1 MB (Solaris SPARC 64 bit/Linux)
Oracle 10g R2 onwards, oracle automatically tuned this parameter. If we
are not set MBRC parameter, oracle will maximum use 1 MB for single I/O
read/write operations.
4)
Oracle 11gR2 = 32 MB ( Not yet confirmed)
SSTIOMAX =< db_block_size * db_file_multiblock_read_count
MBRC default value is 8 and we can
change this parameter both Instance/Session level.
In my database having
db_file_multiblock_read_count =32. It means server process can fetch 32 blocks
from disk to buffer cache on single I/O? NO
how calculate the MBRC value?
DB_FILE_MULTIBLOCK_READ_COUNT parameter
works based on DB block size and OS level I/O size.
Scenario: 1
DB_BLOCKS_SIZE= 8K
Tablespace Block size = 8K
DB_FILE_MULTIBLOCK_READ_COUNT =32
How much I/O blocks can server process
fetch at a time?
I/O= (DB_BLOCKS_SIZE) * (DB_FILE_MULTIBLOCK_READ_COUNT
) = 8 * 32 = 256 KB I/O
Note: Both DB Block and tablespace block
size are same.
Scenario: 2
DB_BLOCKS_SIZE= 8K
Tablespace Block size = 4K
DB_FILE_MULTIBLOCK_READ_COUNT =32
How much I/O blocks can server process fetch
at a time?
= ((DB_BLOCKS_SIZE) * (DB_FILE_MULTIBLOCK_READ_COUNT
))/Tablespace Block size =( 8 * 32)/4 = 64 KB I/O
Note: Both DB Block and tablespace block
size are different
Scenario: 3
DB_BLOCKS_SIZE= 8K
Tablespace Block size = 8K
DB_FILE_MULTIBLOCK_READ_COUNT
=8 (Default value- Not explicitly set)
Oracle 10gR2 – OS I/O optimal size is 1 MB
How automatically calculate the MBRC value in 10gR2 or later release?
SSTIOMAX =< db_block_size * db_file_multiblock_read_count
1 MB = 8 * MBRC
MBRC = 1024 K (OS level I/O optimal size) /
8 K ( block size)
MBRC = 128 blocks (Oracle can fetch
128 blocks for single I/O read)
How to reset the MBRC parameter to default
value?
1) alter system reset db_file_multiblock_read_count scope=spfile sid='*';
2) Restart the instance.
Reference:
Thanks !!!
