RE: Is my Oracle Server issuing more IO than it can handle
Date: Tue, 7 Dec 2010 11:04:48 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D68C96F7A65_at_SPOBMEXC14.adprod.directory>
If you're going to post to the list, I'd personally rather address someone with a name. (From my own skeptical mind I want to question the motive behind not giving a real name, or semi-real name)
You're questions show a pretty good understanding of some underlying principles - so I'm a little concerned what your angle is. Again, it may just be my skepticism, or you may in fact be angling for some information to use in a manner inconsistent with the principles of this list.
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor_at_ingrambarge.com<mailto:chris.taylor_at_ingrambarge.com>
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Oracle Dba Wannabe
Sent: Tuesday, December 07, 2010 10:36 AM
To: oracle-l_at_freelists.org
Subject: Is my Oracle Server issuing more IO than it can handle
Hi All, this is a 10.2.0.4 single instance database (non asm). I see the following events from awr (1 hour snapshot - however hourly snapshots after this show the same trend with respect to wait events): Event
Waits
Time(s)
Avg Wait(ms)
% Total Call Time
Wait Class
free buffer waits
17,926,869
193,146
11
67.7
Configuration
log file switch (private strand flush incomplete)
41,550
30,538
735
10.7
Configuration
log file sync
211,675
25,156
119
8.8
Commit
buffer busy waits
42,093
23,218
552
8.1
Concurrency
db file parallel write
376
14,274
37,963
5.0
System I/O
I know that db file parallel write only contributes to 5% of the total call time - but its avg wait time looks extremely poor - that and the fact that free buffer waits appear at top indicate that there's a db writer issue (db_writer_processes=4) - which leads me to believe perhaps its the IO subsystem. Now the storage team report there is nothing up with the storage. I was hoping someone could help with the following questions: 1. Is there someway from awr that I can determine that the Oracle server is issuing more IO than the storage system can handle for example: Physical reads:
954.74
16.68
Physical writes:
418.89
7.32
Phy Reads + Phy Writes = 1372 IOPS
Can I then say that if each disk can do 100 IOPS, that the storage system should at least have 13 Disks? (13x100 IOPS)? Or is that an over simplification?
2. Interestingly this DB server was moved onto a new box with a different storage and the issue is no longer observed there. Transactions Per Second on old box = 57.22 Transactions Per Second on new box = 225 Phy Reads + Phy Writes for New Box, are slightly under half compared to the old box: Physical reads:
243.02
1.08
Physical writes:
564.62
2.51
That said, the redo size per second on the new box is twice that of the old box (7mb/s : 3mb/s). The buffer cache and db writer processes are the same on both boxes.
Other than the storage aspect of things, I'm thinking (and will check) whether theres an o.s/kernel misconfiguration w.r.t to async io, etc that might be causing this on the old box.
Appreciate any thoughts on 1 or/and 2
Thanks
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 07 2010 - 11:04:48 CST