Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



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:

Mount Point

Main Disk

Mirror 1

Mirror 2





















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.


L 8-3

set pages 999;
set lines 80;

column mount_point heading 'MP';

break on mount_point skip 2;

   substr(file_name,1,4) mount_point,
   substr(file_name,21,20) file_name,
group by
   substr(file_name,21,20) ,

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

---- -------------------- ------------------------------
/u02 annod01.dbf          ANNOD
     arsd.dbf             ARSD
     bookd01.dbf          BOOKD
     groupd01.dbf         GROUPD
     pagestatsd01.dbf     PAGESTATSD
     rdruserd01.dbf       RDRUSERD
     subscrd01.dbf        SUBSCRD
     system01.dbf         SYSTEM
     userstatsd01.dbf     USERSTATSD

/u03 annox01.dbf          ANNOX
     bookx01.dbf          BOOKX
     groupx01.dbf         GROUPX
     pagestatsx01.dbf     PAGESTATSX
     perfstat.dbf         PERFSTAT
     rbs01.dbf            RBS
     rdruserx01.dbf       RDRUSERX
     subscrx01.dbf        SUBSCRX
     temp01.dbf           TEMP
     tools01.dbf          TOOLS
     userstatsx01.dbf     USERSTATSX

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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter