Browse oracle-l: This Month's Archive • Main Archive Page • Related posts • Previous by Date • Next by Date
High wio on New Hitachi SAN Storage of DB Server

From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
To: "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
Date: Sat, 12 Nov 2005 00:26:29 +0530
Folks,

While doing Application Testing of Hybrid Trans (OLTP mostly though) by
200 users (approx) on a NEWLY configured HITACHI SAN Storage, on DB
Server (of AIX) High wait for IO i.e. wio = 70 % till 1400 Hours is
observed.

NOTE - wio reduced to about 10 % gradually from 1400 Hours to 2000
Hours.

Average sar Output from morning to 1400 Hours:-

13:38:00    %usr    %sys    %wio   %idle
13:43:00       6       5      67      22
13:48:00      10       6      74      10
13:53:00      10       5      66      19
13:58:00       7       5      61      27
14:03:00       5       5      67      22
14:08:00       7       5      74      15
14:13:00       9       6      69      15

CONFIG
======
DB Server = 7 CPUs
OS AIX 5.2
Oracle 8.1.7
Hitachi Storage for DB Server = 40 underlying Disks
Hardware Raid
Init.ora disk_asynch_io = FALSE (When disk_asynch_io is set TRUE there
is extreme performance degradation with AIX 5.2 [seems a bug])


Comments by IBM
===============
The average number of process waiting for IO to complete is 12. This
indicates that these processes are waiting for the IO to complete. This
is the reason why we are seeing an average iowait of 70%.

The seek rate is 95.72% on the hdsdb9960lv  LV's indicates a high degree
of random IO, usually caused by the application or a high degree of disk
fragmentation.


STATSPACK report (will provide any other sections as needed)
================

DB Name         DB Id    Instance     Inst Num Release     OPS Host
------------ ----------- ------------ -------- ----------- ---
------------
ABNPROD         34298189 abnprod             1 8.1.7.4.0   NO  findb

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
 Begin Snap:          5 15-Oct-05 13:00:55      352
   End Snap:          6 15-Oct-05 14:00:37      352
    Elapsed:                  59.70 (mins)

Cache Sizes
~~~~~~~~~~~
           db_block_buffers:     215000          log_buffer:   18874368
              db_block_size:       8192    shared_pool_size:  754288000

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            298,013.21                837.31
              Logical reads:             55,540.47                156.05
              Block changes:              2,296.74                  6.45
             Physical reads:              3,109.99                  8.74
            Physical writes:                399.33                  1.12
                 User calls:              2,657.16                  7.47
                     Parses:                 64.98                  0.18
                Hard parses:                  5.44                  0.02
                      Sorts:                 75.56                  0.21
                     Logons:                  0.75                  0.00
                   Executes:              1,783.45                  5.01
               Transactions:                355.92

  % Blocks changed per Read:    4.14    Recursive Call %:   15.67
 Rollback per transaction %:   94.71       Rows per Sort:   10.87


Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                             Wait     %
Total
Event                                               Waits  Time (cs)
Wt Time
-------------------------------------------- ------------ ------------
-------
db file sequential read                         6,230,712    1,640,351
43.84
log file sync                                   1,087,475    1,286,467
34.38
db file scattered read                            351,411      416,508
11.13
log file parallel write                           706,201      288,168
7.70
buffer busy waits                                 334,943       69,830
1.87
          -------------------------------------------------------------


Qs How might this issue be approached?
Qs Are there any special O.S. parameters that might be set?

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely 
for the use of the addressee(s). If you are not the intended recipient, please 
notify the sender by e-mail and delete the original message. Further, you are 
not to copy, disclose, or distribute this e-mail or its contents to any other 
person and any such actions are unlawful. This e-mail may contain viruses. 
Infosys has taken every reasonable precaution to minimize this risk, but is not 
liable for any damage you may sustain as a result of any virus in this e-mail. 
You should carry out your own virus checks before opening the e-mail or 
attachment. Infosys reserves the right to monitor and review the content of all 
messages sent to or from this e-mail address. Messages sent to or from this 
e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
--
http://www.freelists.org/webpage/oracle-l
Follow-Ups:

RE: High wio on New Hitachi SAN Storage of DB Server
From: Adrian
High wio on New Hitachi SAN Storage of DB Server

Other related posts:
