Optimizing Storage Structures - Oracle Database 11gR2 Performance Tuning - page 82

There are some considerations to be taken care of, however; when having tablespaces with
different DB block sizes in the database—as we have seen— we have to reserve space in the
database buffer for a different DB block size, and the memory reserved to a particular block
size cannot be used for caching database blocks of a different size. This situation led to a
possible waste of memory. For example, we have 512 MB reserved for db_16k_cache_size
unused, because we have few objects stored with this database block size, and the db_8k_
cache_size is fully utilized. Tuning the buffer cache can become a nightmare in such an
environment. Why? Because we need to identify the specific requirements for each database
block size used in the database and to optimize the corresponding buffer size.
Row chaining causes poor performance because accessing a row in the database has to read
more than one DB block, even when we access the table by an index lookup. When we plan to
introduce different block sizes in the database, we have to keep in mind the pros and cons of
a larger block size. The larger the block size, the more likely there will be contention issues on
the database block.
There are also advantages in using multiple block sizes, which are as follows:
F Contention reduction: small rows in a large block perform worse under heavy DML
than large rows in a small block size.
F Reduced row chaining: placing large object rows (BLOB, CLOB) into a tablespace
with a larger block size can greatly reduce row chaining and improve I/O.
F Faster updates: heavy insert/update tables can see faster performance when
segregated into another block size, which is mapped to a small data buffer cache.
Smaller data buffer caches often see faster throughput performance.
F Reduced Pinging: RAC can perform far faster with a 2K block size, greatly reducing
cache fusion overhead.
F Less disk space waste: when using Oracle 11 g advanced compression, testing
shows that a 32k block size is the best choice to maximize compression and
minimize waste.
F Less RAM waste: moving random access small row tables to a smaller block size
(with a corresponding small block size buffer) will reduce buffer waste and improve
the chances of the other data blocks remaining in the cache.
F Minimize redo generation: some experts recommend a 2K block size for bitmap
indexes, to minimize redo generation during bitmap index rebuilds.
F Faster scans: tables and indexes that require full scans can see faster performance
when placed in a large block size.