The benefits of
multiple Oracle blocksize are most important for large OLTP databases and
the
benefits of multiple Oracle block sizes are very well
documented. Oracle blocksize I/O performance depends on
optimizing your db_file_multiblock_read_count for your Oracle
blocksize.
WARNING: Using
multiple blocksizes effectively is not simple. It requires
expert-level Oracle skills and an intimate knowledge of your I/O
landscape. While deploying multiple blocksizes can greatly
reduce I/O and improve response time, it can also wreak havoc in the
hands of inexperienced DBA's. Using non-standard blocksizes is
not recommended for beginners.
In general, different blocksizes can improve performance in a
variety of ways:
- Contention reduction - small rows in a large block
perform worse under heavy DML than large rows in a small
blocksize.
- Faster updates - Heavy insert/update tables can see
faster performance when segregated into another blocksize which
is mapped to a small data buffer cache. Smaller data
buffer caches often see faster throughput performance.
- Reduced Pinging - RAC can perform far faster with
smaller blocksizes, reducing cache fusion overhead.
- Less RAM waste - Moving random access small row
tables to a smaller blocksize (with a corresponding small
blocksize buffer) will reduce buffer waste and improve the
chance that other data blocks will remain in the cache.
- Faster scans - Tables and indexes that require full
scans can see faster performance when placed in a large
blocksize.
Here,
Oracle expert Robin Schumacher proves that Oracle indexes build with
less levels in a 32k Oracle blocksize and that a large blocksize will reduce
physical I/O latency. Here are details on setting the
blocksize
for index data files.
Properly implementing your Oracle blocksize will also reduce waste
in the data buffer cache and allow multi-block reads to perform more
efficiently. For example, a table where 500-byte rows are fetched in
a random fashion will have less buffer waste when placed in a 2k
blocksize, improving overall data caching. Here are benchmark
results to show the benefits of
large
Oracle blocksize and Oracle performance.
See these related notes on tuning the Oracle I/O subsystem:
