Re: Is my Oracle Server issuing more IO than it can handle
Date: Tue, 7 Dec 2010 17:55:48 +0000
Message-ID: <AANLkTimazXekBJYBrZeNCg5ZH7g82L81Gazg97FnP_FF_at_mail.gmail.com>
And finally the wait time for log file sync is way too high (like at least 1 if not 2 orders of magnitude) . This can be an indication of either poorly performing storage (I got to learn this when we had a client with a duff disk in a raid 5 array on which the redo logs had been located!) or due to CPU starvation. I don't believe it is ever a symptom of memory problems though..
Given your other stats, I'd *guess* at the disk subsystem, but want to take more observations.
On Tue, Dec 7, 2010 at 5:05 PM, OKH <okh_at_oraconsult.de> wrote:
> There is also another thing. Your log files are too small and you have too
> few of them. I suppose your log files and data files share the same disk(s).
>
>
>
> Felix Castillo Sanchez
>
> Am 07.12.2010 um 17:35 schrieb Oracle Dba Wannabe <
> oracledbawannabe_at_yahoo.com>:
>
> 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.32Phy 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
>
>
-- Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 07 2010 - 11:55:48 CST