Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









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)


If you like Oracle tuning, see the book "Oracle 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.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational