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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  The Approach to Locating Hot Disks

Oracle Tips by Burleson

For other operating environments, we are concerned whenever we see a backlog of I/O tasks waiting to access data on a single disk. For other operating systems, the iostat utility can be used to detect I/O issues.

Once you've identified the hot disks, look closely to find out which files and tables on the disks experience most of the activity so that you can move them to less-active disks as needed. The actual process of identifying hot files and disks involves running data collection utilities, such as STATSPACK and the UNIX iostat utility, and then using the collected I/O data to pinpoint the sources of excessive I/O measurements.

Here are the cardinal rules for disk I/O:

  • There is a difference between a busy disk and a disk that is waiting for I/O to complete. In the next section we will explore the UNIX iostat utility and show how you can identify busy disks.

  • If you are using RAID such as RAID 0+1, the Oracle data blocks will be spread randomly across all of the disks, and load will rise and fall in a uniform fashion.

  • Senior Oracle DBAs often prefer not to implement RAID striping so that they have more control over the disk I/O subsystem.

  • Many disk arrays such as EMC provide sophisticated disk monitoring tools such as Open Symmetrics Manager and Navistar. These tools report on more than simple disk waits, and highlight contention for disks, channels, and disk adapters.

Now that we understand the basic principles behind locating hot disks, 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.

The basic iostat utility

The UNIX iostat command syntax looks like this:

L 8-8

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:

L 8-9

           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

L 8-10

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

L 8-11

>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 HP/UX output we see the following columns:

  • Device name

  • Kilobytes transferred per second

  • Number of seeks per second

  • Milliseconds per average seek

iostat on Solaris

L 8-12

>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 HP/UX, here we see each disk presented horizontally across the output. We see disks sd0, sd1, sd6, and sd35.

The -x option of the HP/UX 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.

L 8-13

>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.


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