BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Building the Oracle UNIX File-to-Disk Architecture Administration

Oracle UNIX/Linux Tips by Burleson Consulting

Building the Oracle File-to-Disk Architecture

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

/u01

hdisk31

hdisk41

hdisk51

/u02

hdisk32

hdisk42

hdisk52

/u03

hdisk33

hdisk43

hdisk53

/u04

hdisk34

hdisk44

hdisk54

/u05

hdisk35

hdisk45

hdisk55

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 query below 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 below queries the dba_data_files view and reports the mapping.

rpt_disk_mapping.sql
set pages 999;
set lines 80; 

column mount_point heading 'MP';

break on mount_point skip 2;

select
   substr(file_name,1,4) mount_point,
   substr(file_name,21,20) file_name,
   tablespace_name
from
   dba_data_files
group by
   substr(file_name,1,4),
   substr(file_name,21,20) ,
   tablespace_name
;

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

MP   FILE_NAME            TABLESPACE_NAME
---- -------------------- ------------------------------
/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 UNIX commands for mapping of our disks to files,

letís see how STATSPACK can be extended to capture disk I/O information.

Extending STATSPACK for Disk I/O Data

Our data collection approach relies on I/O information from Oracle and from the physical disks. We will start by using existing STATSPACK tables, but we will also extend STATSPACK to add the disk I/O information. We will use the UNIX iostat utility to capture detailed disk I/O because almost every dialect of UNIX has the iostat utility. However, there is a dialect issue. Just as vmstat has different dialects, iostat is slightly different in each version of UNIX, and the Oracle DBA will need to customize a data collection mechanism according to his or her requirements. Even within the same dialect, there are arguments that can be passed to the iostat utility that change the output display.

Note: the iostat utility will not provide useful information if you are using RAID5 or any disk array architecture that masks the physical location of the disk spindles.  In these cases you must reply on proprietary disk monitors such as EMC Symmetrics.

The basic iostat utility

The UNIX iostat command syntax looks like this:

iostat <seconds between samples> <number of samples>

For example, to request five samples, spaced at 10 seconds apart, we would issue the command as follows:

root> iostat -t 10 5

Unlike the vmstat utility when all of the data is displayed on one line, the iostat output will have many lines per snapshot, one for each physical disk. Letís begin by taking a short tour of the different dialects of the iostat command. We will begin by showing differences between iostat for Solaris and HP/UX, and then show a method for extending STATSPACK to capture STATSPACK data for AIX servers.

iostat on AIX
root> iostat 1 1 

tty:      tin         tout      cpu:   % user    % sys     % idle    % iowait
          0.0        73         1.0     44.0      56.0        0.0        0.0      

Disks:        % tm_act     Kbps    tps    Kb_read   Kb_wrtn
hdisk0          17.0        44.0      11.0         44         0
hdisk1          33.0       100.0      25.0        100         0
hdisk2          15.0        60.0      14.0         56         4
hdisk3          16.0        76.0      19.0         76         0
hdisk4           0.0         0.0       0.0          0         0
hdisk5           0.0         0.0       0.0          0         0

Here we see each of the disks displayed on one line. For each disk we see:

* The percentage tm_act

* The Kbytes per second of data transfer

* The number of disk transactions per second

* The number of Kbytes read and written during the snapshot period.

iostat on HP/UX
root> iostat 1 5 

  device    bps     sps    msps   

  c1t6d0      0     0.0     1.0 
  c2t6d0      0     0.0     1.0 
c11t11d0      0     0.0     1.0 
 c7t11d0      0     0.0     1.0 
c11t10d0      0     0.0     1.0 
 c7t10d0      0     0.0     1.0 
 c5t10d0      0     0.0     1.0 
c10t10d0      0     0.0     1.0 

 
c11t9d0      0     0.0     1.0 
  c7t9d0      0     0.0     1.0 
  c5t9d0      0     0.0     1.0 
 c10t9d0      0     0.0     1.0 
 c11t8d0      0     0.0     1.0 
  c7t8d0      0     0.0     1.0 
  c5t8d0      0     0.0     1.0 
 c10t8d0      0     0.0     1.0 
 c5t11d0      0     0.0     1.0 
c10t11d0      0     0.0     1.0 
 c5t12d0      0     0.0     1.0 
 c7t12d0      0     0.0     1.0 
c10t12d0      0     0.0     1.0 
c11t12d0      0     0.0     1.0

In the HPUX output we see the following columns:

* Device name

* Kilobytes transferred per second

* Number of seeks per second

* Milliseconds per average seek

iostat on Solaris
root> iostat 1 5

   tty        sd0           sd1           sd6           sd35           cpu
 tin tout kps tps serv  kps tps serv  kps tps serv  kps tps serv   us sy wt id
   0    6  53   6   10    0   0    0    0   0    0    0   0    0    0  0  2 97
   0  234   0   0    0    0   0    0    0   0    0    0   0    0    1  0  0 99
   0   80  24   3   10    0   0    0    0   0    0    0   0    0    0  2  2 97
   0   80 120  15    8    0   0    0    0   0    0    0   0    0    0  0  6 94
   0   80   0   0    0    0   0    0    0   0    0    0   0    0    0  0  0 100

Unlike the iostat output for HPUX, here we see each disk presented horizontally across the output. We see disks sd0, sd1, sd6 and sd35.

The -x option of the HPUX iostat utility changes the output from vertical to horizontal. For each disk, we report the reads per second, writes per second, and percentage disk utilization.

root> iostat -x 1 3

                  extended device statistics                  

device       r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b
sd0          0.0    6.5    1.2   51.6  0.0  0.1    9.6   0   4
sd1          0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
sd6          0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
sd35         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
nfs1         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0

                  extended device statistics                  

device       r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b
sd0          0.0   16.9    0.0  135.3  0.0  0.2   12.3   0   9
sd1          0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
sd6          0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
sd35         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
nfs1         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0

                  extended device statistics                  

device       r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b
sd0          0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
sd1          0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
sd6          0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
sd35         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
nfs1         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0

Now that we see the differences between the dialects of iostat, letís see how this information can be captured into STATSPACK extension tables.

 


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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracleģ is the registered trademark of Oracle Corporation.



Hit Counter