Tuning the Oracle10g Data Buffer Pools

There were many new features in the Oracle10g database that were announced with fanfare in the publicity that accompanied its introduction, but Oracle’s ability to support multiple block sizes received comparatively little attention.  As a result, the important role that multiple block sizes play in the reduction of disk I/O was less appreciated than it might have been.  For the Oracle administrator, multiple block sizes are extremely significant and exciting.  For the first time, data buffer sizes can be customized to fit the specific needs of the database.

Prior to the introduction of multiple block sizes, the entire Oracle database had a single block size, and this size was determined at the time the database was created.  Historically, Oracle8i allowed tables and index blocks to be segregated into three separate data buffers, but the buffer caches had to be the same size.  The KEEP pool stored table blocks that were referenced frequently, the RECYCLE pool held blocks from large-table full-table scans, and the DEFAULT pool contained miscellaneous object blocks.

Oracle10g opened up a whole new world of disk I/O management with its ability to configure multiple block sizes.  Tablespaces can be defined with block sizes of 2K, 4K, 8K, 16K, and 32K.  These tablespaces can be matched with similar sized tables and indexes, thus minimizing disk I/O and efficiently minimizing wasted space in the data buffers.  In Oracle10g, there are a total of seven separate and distinct data buffers that are used to segregate incoming table and index rows.

Many Oracle professionals still fail to appreciate the benefits of multiple block sizes and do not understand that the marginal cost of I/O for large blocks is negligible.  A 32K block fetch costs only one percent more than a 2K block fetch because 99 percent of the disk I/O is involved with the read-write head and rotational delay in getting to the cylinder and track. It also depends on the file system, since some file systems cannot handle multi-block I/O well.

This is an important concept for Oracle indexes because indexes perform better when stored in large block size tablespaces.  The indexes perform better because the b-trees may have a lower height and mode entries per index node, resulting in less overall disk overhead with sequential index node access.  The exploration of this important new feature begins with a review of data caching in Oracle10g.

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm