Re: Is my Oracle Server issuing more IO than it can handle
- From: Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>, okh@xxxxxxxxxxxxx
- Date: Tue, 7 Dec 2010 10:05:24 -0800 (PST)
I don't recall the cpu being heavily utilized - although i'll ask the os team
for those stats too and check. this is what i see from the awr report - which
appears to show the cpu is more idle than not:
AVG_BUSY_TIME 36,662
AVG_IDLE_TIME 326,101
AVG_IOWAIT_TIME 157,442
AVG_SYS_TIME 17,481
AVG_USER_TIME 19,087 (1 hour snap, 5 CPU)
Do any of you have any thoughts w.r.t to question 1 - whether those
calculations
can be representative of the disks i may need.
thanks
________________________________
From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
To: okh@xxxxxxxxxxxxx
Cc: "oracledbawannabe@xxxxxxxxx" <oracledbawannabe@xxxxxxxxx>;
"oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Tue, December 7, 2010 10:55:48 PM
Subject: Re: Is my Oracle Server issuing more IO than it can handle
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@xxxxxxxxxxxxx> 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@xxxxxxxxx>:
>
>
>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
>>
>>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
