Mapping Oracle Disk Architectures Oracle Database Tips by Donald Burleson

Today's disk devices are normally delivered as complete I/O subsystems, complete with their own memory cache, channels, disk adapters, and SCSI adapters. Understanding the architecture requires mapping the number of ports, the size of the disk cache, the number of disk adapters, and the mapping of I/O channels between the disks and the disk cache. Figure 8-7 shows a sample of a disk architecture map for a disk array.

Figure 8-45: A sample architecture of a disk array

Developing this type of disk map is very important to load balancing within Oracle because there are many possible bottlenecks within the disk array subsystem that can cause slowdowns. In addition to monitoring for disk waits, we also need to monitor for SCSI contention, channel contention, and contention between the disk adapters. Fortunately, many of the major disk vendors (EMC, IBM) provide their own proprietary disk utilities (e.g., NaviStar, Open Symmetrics Manager) to perform these disk monitor functions.

The Multiple RAM Buffer Issue

We are also seeing disk arrays being delivered with a separate RAM cache for the disk arrays, as shown in Figure 8-8. These RAM caches can be many gigabytes in size and contain special software tools for performing asynchronous writes and minimizing disk I/O.

Figure 8-46: Multiple RAM caches with an Oracle database

The Oracle DBA needs to consider the RAM cache on the disk array because it changes the basic nature of disk I/O. When Oracle cannot find a data block in one of the data buffers in the SGA, Oracle will issue a physical read request to the disk array. This physical read request is received by the disk array, and the disk RAM cache is checked for the desired block. If the desired block is in the RAM cache, the disk array will return the block to Oracle without making a physical disk I/O. The fact that Oracle physical requests may not match actual read requests is a very important point because it can lead to misleading statistics. For example, the stats$filestatxs table shows the number of reads and writes to files. If you are using a disk array such as EMC, these I/O statistics will not correspond to the actual disk reads and writes. The only conclusive way to check ?real? disk I/O is to compare the physical I/O as measured on the disk array with Oracle's read and write statistics. In many cases, the disks are performing less than half the I/O reported by Oracle, and this discrepancy is due to the caching of data blocks on the disk array RAM memory.

Next, let's look at file striping and see how it can be used to load balance the I/O subsystem.

File Striping with Oracle

File striping is the process of splitting a tablespace into small datafiles and placing these datafiles across many disks. With the introduction of RAID (redundant arrays of inexpensive disks), we also have the option of block-interleaf striping (RAID 1), which places each data block in the tablespace on a separate disk.

Other methods of Oracle file striping involve taking a large tablespace and splitting it into many Oracle datafiles. These files may then be spread across many disks to reduce I/O bottlenecks, as shown in Figure 8-9.

Figure 8-47: Striping a tablespace across multiple disks

However, manual file striping has become obsolete because of the large size of disks. In 1990, a 20GB database would probably have been composed of 20 physical disks, each within 1GB of storage. With many disks in a database, the Oracle DBA could improve throughput by manually striping the busiest tablespaces across many disks.

Commercial disks are getting larger every year, and it is very difficult to find small disk devices that contain less than 36GB of storage. Just ten years ago, the IBM 3380 disk was considered huge at 1GB of storage. Today, the smallest disks available are 18GB. The larger disks mean that there are fewer disk spindles, and fewer opportunities for manual file striping. Since it is often not possible to isolate Oracle tablespaces on separate disks without wasting a huge amount of disk space, the Oracle administrator must balance active with inactive tablespaces across their disks.

NOTE: There is a feature started in Oracle8i called 'single table clusters.? By using a cluster, the keys are grouped in the same physical block?reducing I/O and speeding data retrieval by key.

 

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