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
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
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.
set lines 80;
column mount_point heading 'MP';
break on mount_point skip 2;
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.
---- -------------------- ------------------------------
/u02 annod01.dbf ANNOD
/u03 annox01.dbf ANNOX
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.