Using Multiple blocksizes in Oracle
Oracle Tips by Burleson Consulting
March 24, 2008
Oracle is always changing, and for the latest consensus on using multiple blocksizes in Oracle, see
research on multiple blocksizes.
The introduction of
Oracle 9i brought an amazing amount of complexity to the Oracle database engine.
Oracle introduced many new internal features, including bitmap free lists, redo
log based replication, dynamic SGA,
and perhaps the most important feature of all, the ability to support multiple
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.
At first, beginners denounced multiple block sizes because
they were invented to support transportable tablespaces. They did not know
that multiple block sizes have been used successfully in other DBMS systems
(IDMS, DB2) since the 1980's, and that multiple blocksizes are a proven
technology. Fortunately, Oracle has codified the benefits of multiple
blocksizes, and the
Oracle 11g Performance Tuning guide notes that multiple blocksizes are
indeed beneficial in large databases to eliminate superfluous I/O and isolate
critical objects into a separate data buffer cache:
“With segments that have atypical
access patterns, store blocks from those segments in two different buffer
pools: the KEEP pool and the RECYCLE pool. A segment's access pattern may be
atypical if it is constantly accessed (that is, hot) or infrequently
accessed (for example, a large segment accessed by a batch job only once a
Multiple buffer pools let you address
these differences. You can use a KEEP buffer pool to maintain frequently
accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent
objects from consuming unnecessary space in the cache. . .
By allocating objects to appropriate
buffer pools, you can:
- Reduce or
- Isolate or
limit an object to a separate cache"
In general, different blocksizes can improve performance in a variety
- 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
- 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
- Faster scans - Tables and indexes that require full scans can see
faster performance when placed in a large blocksize
For a complete treatment of using multiple blocksize to
improve I/O performance and manageability, see my book
Tuning: The Definitive Reference".
When you strip away all of
the advanced features, Oracle's job is to deliver data, and the management of
disk I/O is a very critical component and tuning of any Oracle database.
Anything that can be done to reduce the amount of disk I/O is going to have a
positive impact on the throughput of the Oracle database system.
If we take a look at the
various tuning activities within Oracle database, will see that the common goal
of almost Oracle tuning is the directed and immediate goal of reducing disk
I/O. For example, tuning an SQL statement to remove a full table scans makes the
query run faster because of the direct reduction in the amount of data blocks
that are read from the disk. Adjusting instance tuning parameters such as
DB_CACHE_SIZE also has the goal of reducing the amount of disk overhead.
To understand how using
multiple block sizes can improve performance of the Oracle database, we first
have to start by taking a look at the basic nature of disk I/O.
Understanding Oracle physical disk
Oracle data block is accessed from disk, we commonly see three sources of delay.
The first and most important source of delay is the read-write head movement
time. This is the time required for the read-write head to position itself under
the appropriate cylinder. We also see rotational delay as the read-write head
waits for the desired block the past beneath it. The third source of delay
is the data transmission time from the disk back to the Oracle SGA.
If we accept the premise
that 99 percent of the latency is incurred prior to actually accessing the
desired data block, then it makes sense that the marginal cost for reading a 32K
block is not significantly greater than the cost of reading a 2K block. In other
words, the amount of disk delay is approximately the same regardless of the size
of the block. Therefore it should follow that the larger the block you can
read in on a single I/O, the less overall I/O will be performed on the Oracle
The principle behind
caching is not unique to Oracle databases. Access for RAM is measured in
nanoseconds, while access from disk is generally measured in milliseconds. This
amounts to a to an order of magnitude improvement in performance if we can get
the Oracle data block into a RAM buffer. As Oracle grows more sophisticated and RAM becomes cheaper, we tend
to see Oracle databases with system global areas (SGA) that commonly exceed 10
GB. This has important ramifications for the performance of the Oracle database
because once read, the Oracle data blocks reside in RAM where they can be
accessed tens of thousands of times faster than having to go to disk in order to
retrieve the data block.
RAM buffers and Oracle
data access Oracle has always provided RAM data buffers to hold incoming data blocks, and data can be
read from the buffers 14,000 times faster than reading the data block from disk.
The RAM data buffer has evolved from a single buffer in Oracle7 to three data
buffers in Oracle8i. These were known as the KEEP pool, the RECYCLE pool, and
the DEFAULT pool (refer to figure 1).
Figure 1 - The Oracle8 data buffers
In Oracle we
still have the three data buffers, but we also have the ability to create a data
buffer for every supported blocksize for the Oracle server (refer to figure 2).
Figure 2 - The eight data buffers for Oracle
Within each data buffer, the data buffer hit ratio measures the propensity of a
data block to be in RAM memory. It is the job of the Oracle administrator to
allocate RAM pages among the data buffers to ensure the optimal amount of RAM
caching. With small buffers, a marginal increase of pages results in superior
caching (refer to figure 3).
Figure 3 - RAM pages added to a small data buffer
the RAM cache is increased, the marginal benefit from adding pages decreases
(refer to figure 4).
Figure 4 - The marginal decrease of disk I/O with large data buffers
Indexes and Large Data Blocks
Prior to Oracle, Oracle
professionals noticed that by moving the entire database to a larger block size,
they reduce disk I/O improve the performance of the entire system. This is
somewhat counterintuitive, and people ask "if I only need an 80-byte row, where
do I get the benefit of reading 16K block?"
The answer has to do with
indexes. Most well-tuned Oracle database have index based roughly equal to the
space of the table data. There's no question of a large block size for indexes
is going to reduce I/O, and therefore improve the overall performance of the
For most databases, creating multiple blocksizes is not
going to make a measurable difference, and the deployment of multiple blocksizes
must be carefully evaluated on a case-by-case basis. The DBA must carefully evaluate their database
for I/O patterns and buffer efficiency to see if
multiple blocksizes are right for their unique system.
While it is generally accepted that multiple
blocksizes are not for every shop, they may be appropriate for
large multi-terabyte databases where the DBA wants to incur the
additional monitoring complexity to be able to control their I/O
buffers at a lower-level of granularity.
For example, insert-intensive databases will perform less
write I/O (via the DBWR process) with larger block sizes. This is because
more "logical inserts" can take place within the data buffer before the block
becomes full and requires writing it back to disk. Some shops define their
high-insert tablespaces in a larger blocksize to minimize I/O, and some use SSD
to achieve insert speeds of over 10,000 rows per second.
For a more complete discussion, see my
Oracle Multiple blocksize tips.
For these very large, high activity databases, some DBA's will choose
to create a 32K
tablespace, a corresponding 32K data buffer, and then migrate all of the indexes
(but only those that experience multiblock reads) from their existing blocks into the 32K tablespace. Upon having
done this, the Oracle database can read a significant amount of index note
branches in a single disk I/O, thereby reducing stress on the system and
improving overall performance.
Allocating Objects into Multiple Block
So given that we have the
ability to create multiple data buffers within the Oracle database, how do we
decide what data that we want to put each of these data buffers?
Let's start with some of
the more common techniques.
Segregate large-table full-table scans
- Tables that experience large-table full-table scans will benefit from the
largest supported block size and should be placed in a tablespace with your
largest block size, setting db_file_multiblock_read_count according to this
Set db_recycle_cache_size carefully
- If you are not setting db_cache_size to the largest supported block size for
your server, you should not use the db_recycle_cache_size parameter. Instead,
you will want to create a db_32k_cache_size (or whatever your max is), and
assign all tables that experience frequent large-table full-table scans to the
largest buffer cache in your database.
The Data Dictionary uses the default cache
- You should ensure that the data dictionary (e.g. your SYSTEM tablespace) is
always fully cached in a data buffer pool. Remember, the block size of the data
dictionary is not as important as ensuring that the data buffer associated with
the SYSTEM tablespace has enough RAM to fully-cache all data dictionary blocks.
- in many cases, Oracle SQL statements will retrieve index information via an
index range scan, scanning the b-tree or bitmap index for ranges of values that
match the SQL search criteria. Hence, it is beneficial to have as much of an
index residing in RAM as possible.
Segregate random access
reads - For those
databases that fetch small rows randomly from the disk, the Oracle DBA can
segregate these types of tables into 2K Tablespaces. We have to remember that
while disk is becoming cheaper every day, we still don't want to waste any
available RAM by reading in more information to RAM than the number actually going be
used by the query. Hence, many Oracle DBAs will use small block size is in cases
of tiny, random access record retrieval.
Segregate LOB column
tables - For those
Oracle tables that contain raw, long raw, or in-line LOBs, moving the table rows
to large block size will have an extremely beneficial effect on disk I/O.
Experienced DBAs will check dba_tables.avg_row_len to make sure that the
blocksize is larger than the average size. Row chaining will be reduced while at
the same time the entire LOB can be read within a single disk I/O, thereby
avoiding the additional overhead of having Oracle to go out of read multiple
Check the average row
length - The block
size for a tables' tablespace should always be greater than the average row
length for the table (dba_tables.avg_row_len). Not it is smaller than the
average row length, rows chaining occurs and excessive disk I/O is incurred.
Tools for Viewing Data Buffer Usage
The process of segregating
Oracle objects into separate data buffers is fairly straightforward and Oracle
provides tools to assist in this effort. Many Oracle administrators are not
aware of those table blocks, which consume a disproportional amount of data space
within the data buffer caches, and Oracle provides numerous scripts to allow
you to see which objects reside most frequently within the data cache.
The query below counts the
number of blocks for all segments that reside in the buffer cache at that point
in time. Depending on your buffer cache size, this could require a lot of sort
object_name format a40
column number_of_blocks format 999,999,999,999
column object_name format a40
column number_of_blocks format 999,999,999,999
o.object_id = bh.objd
o.owner != 'SYS'
Here we see the object
name and the number of data blocks in the buffer.
. . .
Creating Separate Data Buffers
The process of assigning
table or index blocks into named block size tablespaces is very straightforward
The first step is to set a
database cache to correspond to that block size. Remember, with Oracle 9i we no
longer have to use the init.ora file, and we can create the named cache
dynamically with an alter database statement.
alter system set db_2k_cache_size=200M;
alter system set db_4k_cache_size=500M;
alter system set db_8k_cache_size=800M;
alter system set db_16k_cache_size=1600M;
We begin by creating a
tablespace and using the new blocksize parameter in the create tablespace
syntax. In the example below we create a 32K tablespace within the Oracle
Once we've created the
named RAM buffer, and the tablespace, we are now ready to migrate the Oracle
objects into the new tablespace. There a variety of methods for moving objects
from one tablespace to another, and many Oracle administrators are already
familiar with using the create table as select or CTAS syntax in order to move the table. For indexes, the alter
index rebuild command can be used to quickly migrate an index tree between
Out of all of the
sophisticated enhancements of Oracle, many experienced DBAs consider multiple
block sizes to be the most important feature for tuning the Oracle database. The
Oracle database administrator now has up to seven separate and distinct data
pools that they can manage and control, giving the Oracle DBA a huge amount of
control over the amount of data buffer blocks that can be assigned to specific
database objects. Through judicious inspection all of buffer access
characteristics, overall disk I/O can be tremendously reduced, and the
performance of the database will be greatly improved.
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.