Using Oracle UNIX Administration with Raw Devices Oracle UNIX/Linux Tips by Burleson Consulting

Using Oracle with Raw Devices

Because of the high amount of I/O that many Oracle systems experience, many Oracle DBAs consider the use of ?raw? devices. A raw device is defined as a disk that bypasses the I/O overhead created by the Journal File System (JFS) in UNIX. The reduction in overhead can improve throughput, but only in cases where I/O is already the bottleneck for the Oracle database. Furthermore, raw devices require a tremendous amount of manual work for both the Oracle administrator and the systems administrator. Oracle recommends that raw devices should only be considered when the Oracle database is I/O bound. However, for these types of Oracle databases, raw devices can dramatically improve overall performance. If the database is not I/O bound, switching to raw devices will have no impact on performance.

It is worth mentioning here the feature in Oracle 8.1.7 called directIO supported on some of the platforms. Veritas also has a product called QuickIO. These options give Oracle near RAW performance without the additional overhead in managing RAW devices.

In many UNIX environments such as AIX, raw devices are called virtual storage devices (VSDs). These VSDs are created from disk physical partitions (PPs), such that a single VSD can contain pieces from several physical disks. It is the job of the system administrator to create a pool of VSDs for the Oracle administrator. The Oracle administrator can then take these VSDs and combine them into Oracle datafiles. This creates a situation where an Oracle datafile may be made from several VSDs. This many-to-many relationship between Oracle datafiles and VSDs makes Oracle administration more challenging.

In summary, raw devices for Oracle databases can provide improved I/O throughput only for databases that are already I/O bound.

However, this performance gain comes at the expense of increased administrative overhead for the Oracle administrator. We also know that raw devices will only improve the performance of Oracle databases whose Oracle subsystem is clearly I/O bound. For systems that are not I/O bound, moving to raw devices will not result in any performance gains.

The UNIX iostat utility is great for showing those physical disks that have bottlenecks. Since we know the tablespace and table for each hot datafile, we can intelligently move the hot datafiles to a less active disk. Let?s begin by exploring the nature of disk load balancing for Oracle.

Load Balancing Disks with Oracle Databases

With terabyte-sized and Web-enabled Oracle8 databases becoming more commonplace, the task of disk load balancing has never been more critical. These huge databases are too massive to be cached in an Oracle data buffer, yet these databases often serve thousands of users who expect instant response times. The most important thing that the DBA can do to minimize disk I/O is to balance the load on the disks.

By placing datafiles strategically on the physical disks, you can minimize the likelihood of any one disk becoming stalled while handling simultaneous I/O requests. This section provides a strategy for collecting I/O information into Oracle tables and generating reports to deliver maximum guidance in the load-balancing process for multiple physical disk systems. The purpose of collecting I/O statistics is to provide data for load balancing. Load balancing involves moving datafiles on the physical disks such that no single disk becomes stalled waiting for simultaneous I/O requests.

STATSPACK Reports for Oracle Datafiles

To perform I/O load balancing, we need to get information about the amount of I/O for an Oracle datafile, relative to the total I/O from the database. Remember, a hot file is not necessarily causing a disk bottleneck. The goal of the STATSPACK technique below is to alert the Oracle DBA to those datafiles that are taking a disproportionate amount of I/O relative to other files in the database.

The script we use for this purpose is called rpt_hot_files.sql, and this script is also incorporated into our generalized DBA alert script, statspack_alert.sql.

The rpt_hot_files.sql script is listed below. Let?s take a look at how this script works. The idea is to compare the overall I/O between snapshots (hourly in this case) to the total I/O for the database, as shown in Figure 4-13.

Figure 13: Elapsed time I/O comparison

To get the data we need, we rely on two STATSPACK tables:

* stats$sysstat?The stats$sysstat table contains two important metrics. These are used to compute the total read I/O and write I/O for the entire database:

* Total physical reads (statistic#=40)

* Total physical writes (statistic#=44)

* stats$filestatxs?The stats$filestatxs table contains detailed read I/O and write I/O, totaled by datafile name.

We then compare the system-wide total for read and write I/O with the individual I/O for each Oracle datafile. This allows us to quickly generate an alert report to tell us which files are having the most I/O activity. If we were judicious in placing important tables and indexes into separate tablespaces and datafiles, this report will tell us exactly which database objects are the most active.

Note that you can adjust the thresholds for the rpt_hot_files.sql script. You can set the threshold to 25 percent, 50 percent, or 75 percent, reporting on any files that exceed this threshold percentage of total read and write I/O.

This is a very important script and appears in the generic statspack_alert.sql script. It is critical that the DBA become aware whenever an Oracle datafile is consuming a disproportionate amount of disk I/O. The script below is somewhat complex, but it is worth your time to carefully examine it to understand the query. Lets examine the main steps of this SQL statement:

1. We select the individual I/O from stats$filestatxs and compare the value for each file to the total I/O as reported in stats$systat.

2. The WHERE clause determines when a file will be reported. You have the option of adjusting the reporting threshold by commenting out one of the three choices?25 percent, 50 percent, or 75 percent?of the total I/O.

Rpt_hot_files.sql
set pages 9999;
set feedback off;
set verify off;

column mydate heading 'Yr. Mo Dy  Hr.' format a16
column file_name format a35
column reads  format 99,999,999
column pct_of_tot  format 999

--prompt
--prompt
--prompt ***********************************************************
--prompt  This will identify any single file who's read I/O
--prompt  is more than 25% of the total read I/O of the database.
--prompt
--prompt  The "hot" file should be examined, and the hot table/index
--prompt  should be identified using STATSPACK.
--prompt
--prompt  - The busy file should be placed on a disk device with
--prompt    "less busy" files to minimize read delay and channel
--prompt    contention.
--prompt
--prompt  - If small file has a hot small table, place the table
--prompt    in the KEEP pool
--prompt
--prompt  - If the file has a large-table full-table scan, place
--prompt    the table in the RECYCLE pool and turn on parallel query
--prompt    for the table.
--prompt ***********************************************************
--prompt
--prompt

select
   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   new.filename                          file_name,
   new.phyrds-old.phyrds                 reads,
   ((new.phyrds-old.phyrds)/
   (
   select
      (newreads.value-oldreads.value) reads
   from
      perfstat.stats$sysstat oldreads,
      perfstat.stats$sysstat newreads,
      perfstat.stats$snapshot   sn1
   where
      sn.snap_id = sn1.snap_id
   and
      newreads.snap_id = sn.snap_id
   and
      oldreads.snap_id = sn.snap_id-1
   and
     oldreads.statistic# = 40
   and
     newreads.statistic# = 40
   and
     (newreads.value-oldreads.value) > 0
   ))*100 pct_of_tot
from
   perfstat.stats$filestatxs old,
   perfstat.stats$filestatxs new,
   perfstat.stats$snapshot   sn
where
   snap_time > sysdate-&1
and
   new.snap_id = sn.snap_id
and
   old.snap_id = sn.snap_id-1
and
   new.filename = old.filename
and
   -- **********************************************************
   -- Low I/O values are misleading, so we filter for high I/O
   -- **********************************************************
   new.phyrds-old.phyrds > 100
and
-- **********************************************************
-- The following will allow you to choose a threshold
-- **********************************************************
 (new.phyrds-old.phyrds)*4>  -- This is 25% of total
-- (new.phyrds-old.phyrds)*2> -- This is 50% of total
-- (new.phyrds-old.phyrds)*1.25> -- This is 75% of total
-- **********************************************************
-- This subquery computes the sum of all I/O during the snapshot period
-- **********************************************************
(
select
   (newreads.value-oldreads.value) reads
from
   perfstat.stats$sysstat oldreads,
   perfstat.stats$sysstat newreads,
   perfstat.stats$snapshot   sn1
where
   sn.snap_id = sn1.snap_id
and
   newreads.snap_id = sn.snap_id
and
   oldreads.snap_id = sn.snap_id-1
and
  oldreads.statistic# = 40
and
  newreads.statistic# = 40
and
  (newreads.value-oldreads.value) > 0
)
;

--prompt
--prompt
--prompt ***********************************************************
--prompt  This will identify any single file who's write I/O
--prompt  is more than 25% of the total write I/O of the database.
--prompt
--prompt  The "hot" file should be examined, and the hot table/index
--prompt  should be identified using STATSPACK.
--prompt
--prompt  - The busy file should be placed on a disk device with
--prompt    "less busy" files to minimize write delay and channel
--prompt    channel contention.
--prompt
--prompt  - If small file has a hot small table, place the table
--prompt    in the KEEP pool
--prompt
--prompt ***********************************************************
--prompt

select
   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   new.filename                          file_name,
   new.phywrts-old.phywrts               writes,
  ((new.phywrts-old.phywrts)/
   (
   select
      (newwrites.value-oldwrites.value) writes
   from
      perfstat.stats$sysstat   oldwrites,
      perfstat.stats$sysstat   newwrites,
      perfstat.stats$snapshot  sn1
   where
      sn.snap_id = sn1.snap_id
   and
      newwrites.snap_id = sn.snap_id
   and
      oldwrites.snap_id = sn.snap_id-1
   and
     oldwrites.statistic# = 44
   and
     newwrites.statistic# = 44
   and
     (newwrites.value-oldwrites.value) > 0
   ))*100 pct_of_tot
from
   perfstat.stats$filestatxs old,
   perfstat.stats$filestatxs new,
   perfstat.stats$snapshot   sn
where
   snap_time > sysdate-&1
and
   new.snap_id = sn.snap_id
and
   old.snap_id = sn.snap_id-1
and
   new.filename = old.filename
and
   -- **********************************************************
   -- Low I/O values are misleading, so we only take high values
   -- **********************************************************
   new.phywrts-old.phywrts > 100
and
-- **********************************************************
-- Here you can choose a threshold value
-- **********************************************************
 (new.phyrds-old.phywrts)*4>  -- This is 25% of total
-- (new.phyrds-old.phywrts)*2> -- This is 50% of total
-- (new.phyrds-old.phywrts)*1.25> -- This is 75% of total
-- **********************************************************
-- This subquery computes the sum of all I/O during the snapshot period
-- **********************************************************
(
select
   (newwrites.value-oldwrites.value) writes
from
   perfstat.stats$sysstat   oldwrites,
   perfstat.stats$sysstat   newwrites,
   perfstat.stats$snapshot  sn1
where
   sn.snap_id = sn1.snap_id
and
   newwrites.snap_id = sn.snap_id
and
   oldwrites.snap_id = sn.snap_id-1
and
  oldwrites.statistic# = 44
and
  newwrites.statistic# = 44
and
  (newwrites.value-oldwrites.value) > 0
)
;

It is highly recommended that the DBA run this STATSPACK report daily so the DBA can constantly monitor for hot datafiles. Below is a sample of the output from this script. Note how it identifies hot files on an hourly basis.

***********************************************************
This will identify any single file who's read I/O
is more than 50% of the total read I/O of the database.
***********************************************************

Yr. Mo Dy  Hr. FILE_NAME                                 READS PCT_OF_TOT
---------------- ----------------------------------- ----------- ----------
2000-12-14 14    /u02/oradata/prodb1/bookd01.dbf             354         62
2000-12-14 15    /u02/oradata/prodb1/bookd01.dbf             123         63
2000-12-14 16    /u02/oradata/prodb1/bookd01.dbf             132         66
2000-12-14 20    /u02/oradata/prodb1/bookd01.dbf             124         65
2000-12-15 15    /u02/oradata/prodb1/bookd01.dbf             126         72
2001-01-05 09    /u02/oradata/prodb1/system01.dbf            180         63
2001-01-06 14    /u03/oradata/prodb1/perfstat.dbf            752        100
2001-01-06 15    /u02/oradata/prodb1/bookd01.dbf             968         69

***********************************************************
This will identify any single file who's write I/O
is more than 50% of the total write I/O of the database.
***********************************************************

Yr. Mo Dy  Hr. FILE_NAME                               WRITES PCT_OF_TOT
---------------- ----------------------------------- ---------- ----------
2000-12-18 21    /u02/oradata/prodb1/bookd01.dbf           2654         58
2000-12-29 15    /u02/oradata/prodb1/bookd01.dbf           1095         49

Now that we have examined how to identify hot files, let?s take a look at other useful STATSPACK reports that can tell us about disk activity.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.