Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Building the Oracle File-to-Disk Architecture Oracle Tips by Burleson

If you are not using a block-level block striping mechanism such as RAID 0+1, it is a good idea to map each physical disk spindle directly to a UNIX mount point. For example, here is a sample mapping for a set of triple-mirrored disks:

By mapping the UNIX mount points directly to physical disks, it becomes easy to know the disk location of a hot Oracle datafile. For example, if our STATSPACK hot file report (in the statspack_alert.sql script) indicates that /u03/oradata/prod/books.dbf is consuming an inordinate amount of I/O, we immediately know that /u03 is getting hit, and that /u03 maps directly to disk hdisk33 and its mirrored disks.

Please note that this mapping technology becomes more complex because of the large size of disk spindles. The trend has been toward creating very large disks, and it is not uncommon to find disks that range from 36GB to 72GB. In these cases, many small Oracle databases will reside on a single physical disk, and load balancing becomes impractical. However, this large-disk issue does not imply that the DBA should abandon disk monitoring simply because all of the files reside on a single disk. Remember, high file I/O can be corrected with the judicious use of the Oracle data buffers. For example, a hot table can be moved into the KEEP pool, thereby caching the data blocks and relieving the hot-disk issue.

It is interesting to note that some products such as EMC have developed methods to internally detect hot files and transparently move them to cooler disks. However, this approach has a problem. Blindly moving a hot datafile to a cooler disk is analogous to pressing into an overstuffed pillow: one area goes in, but another area bulges.

It is never simple in the real world. In the real world, the Oracle DBA may find a specific range of data blocks within a datafile that is getting high I/O, and they will segregate these blocks onto a separate datafile. This relates to the point we made earlier in this chapter that the Oracle DBA must always segregate hot tables and indexes onto separate tablespaces.

If you are not using RAID 0+1 or RAID 5, it is simple to write a dictionary query that will display the mapping of tablespaces-to-files and files-to-UNIX mount points. Note that the data selected from the dba_data_files view relies on using the Oracle Optimal Flexible Architecture (OFA). If we use the OFA, the first four characters of the filename represents the UNIX mount point for the file. We can also adjust the substring function in the following query to extract the filename without the full disk path to the file.

Reporting on the Oracle Disk Architecture

If your shop follows the OFA standard, you can write a dictionary query that will report on the disk-to-file mapping for your database. This script assumes that you use OFA names for your datafiles (e.g., /u02/oradata/xxx.dbf ), and that your UNIX mount points map to easily identifiable physical disks. The script here queries the dba_data_files view and reports the mapping.

rpt_disk_mapping.sql

L 8-3

Here is the output from this script. Please note that there is a one-to-one correspondence between Oracle tablespaces, physical datafiles, and UNIX mount points.

L 8-4

Now that we know the mapping of our disks to files, we are ready to look at some STATSPACK reports that will display all Oracle datafiles that exceed a threshold value.

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.