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 








Transient Disk Hot Spots

Oracle Database Tips by Donald Burleson

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 disk 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 accessed by the table (see Figure 8-2).

Figure 8-40: The roving hot spot within a transaction 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 200 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, as shown in Figure 8-3.

Figure 8-41: The hot spot travels back in the table

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 within an Oracle data warehouse.

In standard business applications, we see this type of time-based entry into 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 8-4). 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.

Figure 8-42: High seek delay on a disk

There are several techniques that can be done to remedy this problem:

  • Segregate objects Identify all tables with high insert activity and segregate them 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.

In addition to roving hot spots, these highly active Oracle tables will also manifest themselves as sparse tables.

The Sparse Table Phenomenon in RAC

Sparse tables generally occur in RAC when a highly active table is defined with many freelists, and the table has heavy INSERT and DELETE activity. In a sparse table, the table will appear to have thousands of free blocks, yet the table will continue to extend, and the table will behave as if Oracle does not have any free data blocks. A sparse table in a data warehouse can consume a huge amount of unnecessary storage, consuming many gigabytes of new storage while the table appears to have lots of free space. Remember, when you have multiple freelists, the freelists are independent and Oracle cannot share freelist blocks. An INSERT task will only attach to one freelist, and it is only able to use free blocks that are attached to that freelist.

The cause of a sparse table in Real Application Clusters is a lack of balance between INSERT and DELETE activity. In our example, we have three freelists defined for the table, and each freelist gets new blocks in five-block chunks as the table expands.

As we can see, only one of the three freelists is populated with the free blocks! The other two freelists remain empty, and must request blocks by increasing the high-water mark for the table. This causes the table to extend, even though it may be largely empty. Extension occurs because each freelist is unaware of the contents of other freelists inside the segment header.

The remedy, of course, is to parallelize the RAC purge job into three simultaneous DELETE tasks. By parallelizing the purge, all three freelists are evenly populated with newly empty blocks.

Of course, we must set the number of freelists to the number of simultaneous INSERT or UPDATE operations, so we cannot reduce the number of freelists without introducing segment header contention.

So, what can we do to identify sparse tables in RAC? The following query selects tables that contain multiple freelists, with more than one extent, where there is excessive free space.

To find tables with excessive free blocks on a freelist, we must compute the amount of data used within the table. First, we calculate the average row length (avg_row_len) in the data dictionary view and the number of rows (num_rows) by performing a table analyze (that is, analyze table xxx estimate statistics). When we multiply the number of rows in the table by the average row length, we approximate the actual consumed size of the data within the table. We then compare this value with the actual number of allocated bytes in the table.

The idea is that a sparse table will have far more allocated space than consumed space because a single freelist contains a disproportional number of free blocks. Here is a script called sparse.sql that generates this report.


column c1  heading "Tablespace";
column c2  heading "Owner";
column c3  heading "Table";
column c4  heading "Bytes M" format 9,999;
column c5  heading "Extents" format 999;
column c7  heading "Empty M" format 9,999;
column c6  heading "Blocks M" format 9,999;
column c8  heading "NEXT M" format 999;
column c9  heading "Row space M" format 9,999;
column c10  heading "Pct Full" format .99;

        substr(dt.table_name,1,10) c3,
        ds.extents c5,
        ds.bytes/1048576 c4,
        dt.next_extent/1048576 c8,
       (dt.empty_blocks*4096)/1048576 c7,
       (avg_row_len*num_rows)/1048576 c9,
       (ds.blocks*4096)/1048576 c6,
       (avg_row_len*num_rows)/(ds.blocks*4096) c10
from    sys.dba_segments ds,
        sys.dba_tables   dt
where   ds.tablespace_name = dt.tablespace_name
  and   ds.owner = dt.owner
  and   ds.segment_name = dt.table_name
and dt.freelists > 1
and ds.extents > 1
and dt.owner not in ('SYS','SYSTEM')
and (avg_row_len*num_rows)/1048576 > 50
and ds.bytes/1048576 > 20
order by c10;

Next is the output from sparse.sql. This will identify tables that have lots of free space within their existing extents. If any of these tables extend before using up their free blocks, we can assume that the table has a freelist imbalance. The remedy for this imbalance is to reorganize the table.

Table      Extents Bytes M NEXT M Empty M Row space M Blocks M Pct Full
---------- ------- ------- ------ ------- ----------- -------- --------
TST03           65   1,241     20      14         118    1,241      .10
LIKE             3     148     49      24          76      148      .52
VBRK             2     124      4       0          69      124      .56
STXL            35   1,775     40       7       1,021    1,775      .57
VBAK             5     234     49       0         136      234      .58
KOCLU           27   1,889     49      27       1,144    1,889      .61
VBUP             2     866     49       0         570      866      .66
VBUK             2     147     28       0         103      147      .70
VBAP            46   4,314     50       0       3,034    4,314      .70
NASTY            3     137     10       2          97      137      .71
VBPA             5     582     32       0         426      582      .73
LIME             7   2,350     49       0       1,735    2,350      .74
VBRP            45   2,675     49       0       2,029    2,675      .76
WFPRC           30     123     10       7          95      123      .77
VLPMA           16     575     25      23         444      575      .77
EXCDOC          18     432     20      13         337      432      .78
VRPMA           24     700     20       7         549      700      .78
VBEP             4   2,134     49      49       1,698    2,134      .80

Now that we understand the general nature of I/O activity in an Oracle database, let's look at some global solutions for placing Oracle datafiles onto our disk devices.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & 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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.