Nearly all databases were I/O-bound back in the days of Oracle7 when
disk was super expensive.
Since most applications are data intensive, the database industry
developed all types of elaborate methods for load balancing the I/O
subsystem to relieve disk contention. As disk prices declined,
Oracle created mechanisms (VARRAY tables, materialized views,
snapshots) for
introducing redundancy into third-normal form table structures,
thereby reducing table join overhead.
On the IBM mainframes, the DBA could specify the absolute track
number for any data file with the absolute track (ABSTR) JCL
argument, allowing the mainframe DBA to control placement of the
data files on their IBM 3380 disks. Prior to the advent of RAID and
the Oracle10g SAME (Stripe and Mirror Everywhere) architecture, the data file
placement rules were quite different.
-
Read-write head delay (seek delay):
The time required to position the read-write head under the
appropriate disk cylinder can consist of 90 percent of disk access
time. Be aware that it is especially unwise to place competing
files in outermost cylinders. Back in the days of 3350 disks, the
DBA could load an ISAM file into a 3350 and literally watch the
device shake as the read-write heads swung back and forth.
-
Data transmission Delay:
A huge source of delay for distributed databases and
databases on the Internet. For instance, many worldwide Oracle
shops use replication techniques and place systems staggered across
the world to reduce data transmission time.
-
Rotational delay:
The rotational delay is the speed of rotation divided by
two, assuming that a platter will have to spin a half-revolution to
access any given track header. Once on the proper cylinder,
the read-write heads must wait until the track header passes beneath
them.
Indeed, even now these three components of disk
access latency exist. Before large data buffer caches and
RAID, the DBA had to manually place data files on the disk and
monitor I/O patterns to ensure there was no disk contention.
If RAID striping is not in use, the manual disk placement rules
remain important. The rules include:
-
File Placement: On
disks greater than 100 gigabytes where the data cannot be cached in
the data buffers, the DBA might consider placing high I/O data files
in the middle absolute track number to minimize read-write head
movement. This is a situation in which the low access data files
reside on the inner and outer cylinders of the disk. As shown below,
high impact data files should be placed to minimize read-write head
movement:
Oracle has another approach in the Oracle
11gr2 "ASM intelligent file placement" feature.
In
intelligent file placement the data file is broken down into "hot"
and "cold" disk platter areas, leveraging on the fact that the
outermost sectors of a disk contain more space per revolution than
the inner "cold" sectors.
It is not news that I/O throughput is influenced by the amount of
time required for the read-write heads to move under the proper
cylinder.
These
benchmark tests show how the throughput can vary depending upon
the cylinder placement on a very large spinning disk platter.
All of these disk I/O rules still apply even if hardware or software
RAID, Solid-state Disk (SSD), or 100 percent data caching are not in
use.
With that introduction to the manual methods for Oracle data file
management, it is a good time to look at how advances of the past
decade have simplified this important task of disk I/O management.
Disk Architectures of the 21st Century
During the 1980s, countless DBAs spent a great deal of their time
managing the disk I/O sub-system. Indeed, the manual file placement
rules are cumbersome and complex. However, there are three main
technologies that have altered this approach:
Solid State Disk
At a cost of about $10k (USD) per gigabyte, the new solid state
disks retrieve data hundreds of time faster than traditional disks.
Many Oracle shops are using RAM SAN technology for their TEMP
tablespace, undo files, and redo log files.
Large RAM Caching
In 64-bit Oracle, the
db_cache_size is only limited by the
server, allowing many shops to run fully cached databases. Prices
of RAM should fall in the next five years, such that most systems
can be fully cached, thereby making disk management obsolete.
Oracle has a utility called
v$db_cache_advice that allows the DBA
to predict the benefit of adding RAM buffers. This same concept has
been incorporated into the Oracle10g Automatic Memory Management (AMM)
as shown in Figure 13.1.
Figure 13.1:
Output from
the v$db_cache_advice utility.
Oracle estimates the physical reads for different sizes of the
db_cache_size. In Figure 13.1, it is clear that doubling
the db_cache_size
from 856 to 1,672 will cut disk I/O by more than 80 million disk
reads. However, as full-caching is approached, less frequently
referenced data becomes cached, and the marginal benefit of caching
decreases as shown in Figure 13.2.
The marginal increase in data buffer blocks is asymptotic to disk
I/O.
This is a y = 1/x function where:
1
RAM buffers = ------------------------
physical reads
Figure 13.2:
The
marginal gains from large RAM caches.
In Figure 13.2, as full data caching is approached, the marginal
advantage of blocks to
db_cache_size decreases. With the
Automatic Memory Management (AMM) feature in Oracle10g, Oracle can
be used to track the usage of RAM within the
shared_pool_size,
pga_aggregate_target and
db_cache_size. In
addition, Oracle10g will automatically adjust the sizes of these SGA
regions based on current usage.
Full details on tuning the RAM data buffers are available in the
chapter on Instance Tuning with AWR. Now, it is time to take a look
at the influence of RAID on Oracle databases.
