Oracle UNIX 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 inserts:

* 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 the table.

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 rows.

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 the table.

This Kim Floss article shows the Oracle 10g segment advisor recommending a rebuild of an index:

Oracle index rebuild advisor (Source: Oracle Corporation)