Oracle Tips by Burleson
Oracle9i and multiple block
The ability of Oracle to support multiple block sizes did not get a
lot of fanfare during the publicity rollout of Oracle9i. Rather than
being touted as an important tool to reduce disk I/O, the multiple
block size feature was buried far down on the list of new features
of the Oracle9i database. However, for the Oracle administrator,
multiple blocksizes are extremely important and exciting. For the
first time, you will be able to customize your data buffer sizes
according to the specific needs of your database.
The ability to support multiple block sizes within Oracle9i opens up
a whole new world of disk I/O management. Prior to Oracle9i, your
entire Oracle database had to have a single block size and this
block size was determined at the time that the database was created.
With the introduction of Oracle8i, we received the ability to
segregate tables and index blocks into three separate data buffers,
but all of the buffer caches had to be the same block size. We had
the KEEP pool to store frequently referenced table blocks, the
RECYCLE pool to hold blocks from large-table full-table scans, and a
DEFAULT pool for miscellaneous object blocks.
With Oracle9i, we can define tablespaces with block sizes of 2K, 4K,
8K, 16K and 32K, and assign tables and indexes to the best block
size to minimize I/O and best manage wasted space in our data
buffers. When we combine the new data buffers for these block sizes,
we get a total of seven separate and distinct data buffers to
segregate our incoming table and index rows.
As we know, disk I/O is the single most expensive operation within
an Oracle9i database, and multiple block sizes give us a powerful
new tool to manage disk I/O with more power than ever before.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA