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_IDLE_TIME 326,101 
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 
can be representative of the disks i may need.

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

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 single instance database (non asm). I see the 
>following events from awr (1 hour snapshot - however hourly snapshots after 
>show the same trend with respect to wait events):
>>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 
>>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 
>>- 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. 
>>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 
>>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 
>>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 
>>(7mb/s : 3mb/s). The buffer cache and db writer processes are the same on 
>>Other than the storage aspect of things, I'm thinking (and will check) 
>>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

Niall Litchfield
Oracle DBA


Other related posts: