Transient Disk Hot Spots Administration
Oracle UNIX/Linux Tips by Burleson Consulting
Transient Disk Hot Spots
In any Oracle database it is not uncommon to
see sporadic hot spots appear on disks as the I/O signature changes.
As you know, Oracle offers a wealth of data buffering tools that are
designed to keep Oracle data blocks within RAM and prevent I/O.
These techniques include table caching and separate data buffers.
However, unless you have your database fully cached in the data
buffers, you will always experience I/O activity.
I/O Patterns Within High-Update Tables
There is a special case of disk I/O that
occurs when a transaction table experiences high-volume insert and
update operations. For example, consider an order processing system
with 3,000 data entry operators constantly slamming orders into a
large order table. Let?s further assume that at any given time,
there are 200 transactions inserting into this table. As we will
see, a close inspection of the datafiles will reveal several
important characteristics within tables that have high-volume
* Roving hot spots on disk
* The sparse table phenomenon
Roving Hot Spots on Disk
To understand roving hot spots on disk we
will give a simple example. Let?s assume that our database has a
table named transaction that has 200 data entry operators constantly
adding rows. The transaction table is defined with 200 freelists,
and the table is gathering free blocks by raising the high-water
mark for the Oracle table. This example assumes that there are not
any free blocks on the freelists for the table, such as the case
where the APPEND hint is used with the insert statements. Since we
know that Oracle bumps the high-water mark for a table in increments
of five blocks, our 200 concurrent inserts would generate intensive
SQL insert activity that is isolated to a set of 1,000 blocks within
Because each of the insert transactions must
request a separate free block from the transaction table to insert
their new transactions, Oracle will grab free blocks (five at a
time) from sequential free space in the tablespace. These free
blocks are likely to be contiguous blocks on the disk. Since these
contiguous blocks are likely to reside on the same disk cylinder, it
is likely that this disk would experience I/O contention at the end
of the table.
As our data entry operators continue to hand
key entries into the transaction table, we see the hot spot moving
along the disk as new cylinders are accesses by the table.
As we can see, the hot spot will travel
through the tablespaces as Oracle blocks become full and the Oracle
tables expand. So long as all of the SQL insert statements add
blocks into a new data block, the hot spot will travel cleanly
across the disks.
However, what happens after a transaction
purge job is run? When older transactions are deleted from the
table, blocks become free and are added to one of the 100 freelists
for the table. As the freelists are loaded with newly empty blocks
from the purge job, the hot spot will travel backward in the table,
returning to the area of the table where the purge job removed the
Oracle tablespaces that contain high-volume
tables often experience the ?roving hot spot? phenomenon. This is
especially true for tables where rows are inserted and deleted on a
date-time basis, such as a fact table with an Oracle data warehouse.
We see this type of time-based entry into
many Oracle tables. For example, orders for goods are inserted in a
time-based sequence, data warehouses load their data in a time
sequence, and most every online system adds and purges rows based on
a time sequence.
So, given that these roving hot spots will
appear, what can you do to manage the activity? The trick to
managing roving hot spots is to ensure that the free blocks always
reside on adjacent cylinders. When Oracle data blocks are re-added
to the freelists, we cannot guarantee that they will be close
together on the disk, and this condition can create a ?disk
thrashing? situation. Disk thrashing occurs when free blocks are
located on widely distant cylinders on the disk (see Figure 4-7). As
our 200 tasks compete for free blocks, the read-write heads thrash
back and forth attempting to meet the needs of each transaction. The
time required for a disk?s read-write head to move between cylinders
is called ?seek? delay, and seek delay is the single most
time-consuming factor in disk access.
There are several techniques that can be
done to remedy this problem:
* Segregate objects? Identify all tables with
high insert activity and segregate then into a separate tablespace.
* Use fresh data blocks? Ensure that all new
inserts go onto new data blocks at the end of the table by using the
APPEND hint in all insert statements.
* Reorganize tables? Reorganize the table
after purge jobs are run to reclaim the freed blocks onto the end of
Kim Floss article shows the Oracle 10g segment advisor
recommending a rebuild of an index:
Oracle index rebuild advisor (Source: Oracle Corporation)
If you like Oracle tuning, see the
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.