Oracle Disk Architecture Oracle Tips by Burleson Consulting

Oracle Disk Architecture

Nearly all databases were I/O-bound back in the days of Oracle7.  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

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.

Today’s disks still have three sources of delay, and there can be wide variations in disk access speed even though the average disk latency is 8-15 milliseconds. Internally, disks have changed little in the past 40 years.  They remain physical mechanisms with spinning platters and mechanical read-write heads.  The following list details how disk I/O works at the device level:

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:

File Striping: High impact data files should extend across many disk drives to spread the load and relieve disk and channel contention:

File Segregation: To relieve contention, the redo files and data files should be placed on separate disk spindles. This is also true for the archived redo log file directory and the undo log files.

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 21^st 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.

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

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.

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts:

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.