Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Load Balancing Disks with Oracle Databases

Oracle Tips by Burleson

With terabyte-sized and Web-enabled Oracle databases becoming more commonplace, the task of disk load balancing has never been more critical. These huge databases are too massive to be cached in an Oracle data buffer, yet these databases often serve thousands of users who expect instant response times. The most important thing that the DBA can do to minimize disk I/O is to balance the load on the disks.

By placing datafiles strategically on the physical disks, you can minimize the likelihood of any one disk becoming stalled while handling simultaneous I/O requests. This section provides a strategy for collecting I/O information into Oracle tables and generating reports to deliver maximum guidance in the load-balancing process for multiple physical disk systems. The purpose of collecting I/O statistics is to provide data for load balancing. Load balancing involves moving datafiles on the physical disks such that no single disk becomes stalled waiting for simultaneous I/O requests. The best way to start a file placement strategy is to create a transparent disk architecture whereby the DBA can correlate the datafile name to a specific Oracle table or index. Let's explore how we create this type of architecture.

Configuring Oracle Tablespaces and Datafiles

Since different application processes have different I/O patterns, hot disks may appear on different disks at different times during each day. The goal of disk load balancing is to eliminate disk I/O bottlenecks, but it is important to remember that these bottlenecks are transient in nature. Since Oracle transactions happen very quickly, a disk may experience an I/O bottleneck for a very short period, and this short-duration bottleneck may repeat itself thousands of times each day. However, many Oracle administrators make the mistake of summarizing I/O by the hour, and the disk will appear not to have bottlenecks since the I/O spikes will have disappeared in the hourly average, as shown in Figure 8-10.

Figure 8-48: Short I/O spikes can be lost with long measurement periods

To get the most accurate results, you should collect I/O statistics at frequent intervals—preferably no more than ten minutes between samples—over a representative time period, such as a week. Because individual application processes have different I/O patterns, bottlenecks may appear on different disks at various times during each day. And because Oracle transactions happen very quickly, a disk may experience an I/O bottleneck for a very short period—but a short-duration bottleneck may nonetheless repeat itself thousands of times each day. If you make the mistake of summarizing I/O by the hour, as many DBAs do, you won't see these bottlenecks because the I/O spikes will not be evident in the hourly average.

The point is simple: in order to accurately identify and correct disk I/O bottlenecks, you must measure in minor duration, preferably no more than 10 minutes between samples. We will discuss this technique in a following section where we will show how to extend STATSPACK to capture disk iostat information.

The goal of load balancing is to distribute the files across disks so as to achieve a single static optimal I/O throughput. Moving Oracle datafiles to other disks is not a trivial operation, and the datafile must be taken offline before the file can be moved to another disk. However, the good news is that once the I/O subsystem is balanced, the files will not need to be moved unless new processes change the I/O pattern for the disks.

The goal is to find the optimal file placement where overall load balance is achieved for all of the many variations of disk access. Load balancing is essentially the identification of hot disks, and the movement of datafiles to less-used cool disks. As such, disk load balancing is an iterative process since it is possible that relocating a datafile may relieve contention for one process, only to cause I/O contention for an unrelated process. Also, for databases placed on a small number of disks, it is possible that I/O contention cannot be avoided. Consider a 30GB database spread across two disks with 20 competing processes for data. On average, ten processes would be queued waiting for I/O from each of the two disks. Clearly, these types of systems will always experience I/O contention.

Within Oracle in any UNIX environment, we have a hierarchical relationship between entities. Each physical disk has many UNIX mount points, each mount point has many Oracle datafiles, and each datafile may have many Oracle tables, as shown in Figure 8-11.

Figure 8-49: The hierarchy of Oracle file structures on UNIX

After using data collected by iostat to identify a hot disk, you would use data collected by the Oracle utilities to identify which mount point and file contain the table causing the excessive I/O activity.

Identifying the hot disk is only the beginning of the quest. We must then see what mount point on the disk is causing the problem, which datafile on the mount point, and finally, what Oracle table is causing the excessive I/O. Only with this approach can the Oracle administrator fully understand how to perform disk load balancing. With that in mind, let's look at the first method for collecting Oracle I/O statistics. We will then move on to look at collecting UNIX I/O statistics.

This is an excerpt from "Oracle 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 performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter