Re: Is my Oracle Server issuing more IO than it can handle
Date: Tue, 7 Dec 2010 20:36:35 +0100
Message-Id: <4CFAD664-6EED-4BC2-BE88-AAE30FC55BC2_at_oraconsult.de>
As far as I know there is no way but talking with the SAN guys. But - you cannot issue more IOPS than the subsystem can handle as it would just increase the queue and you'd get higher wait times. The system performs as much as it can. You should take a look at v$event_histogram to check how this average values have been distributed. Using SAN you should have lots of IOs in 1ms, 2ms and 4ms. The higher the value the more the SAN is accessing the disks directly because it cannot cache. Everything that goes beyond the average access of the disk is an indication for too much IO Requests and therefore higher wait times.
Take a look at http://blog.oraconsult.de/2010/09/analyse-von-wait-events-gewichtung-der-wartezeiten. it is in German but the graph is self explaining
regards
Felix Castillo Sanchez
Am 07.12.2010 um 20:25 schrieb Oracle Dba Wannabe <oracledbawannabe_at_yahoo.com>:
> No in fact a san is being used which is being shared with other database servers in the environment - so based on what you;ve said the san cache would be shared too. The servers are hp servers connected to an xp san.
> That said, if this hp server was the only one attached to it - the calculation i used below would then be valid? If not does anyone know how to calculate how many iops are possible from a storage and how much the oracle server is issuing?
> thanks
>
> From: Harel Safra <harel.safra_at_gmail.com>
> To: oracledbawannabe_at_yahoo.com
> Cc: Niall Litchfield <niall.litchfield_at_gmail.com>; okh_at_oraconsult.de; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
> Sent: Wed, December 8, 2010 12:12:42 AM
> Subject: Re: Is my Oracle Server issuing more IO than it can handle
>
> This calculation is only true if you work with disk drive directly attached to you servers, and even then when there is no cache in the raid controller.
> Once you start working with central storage systems things like cache size, i/o distribution and disk sharing start to come into play.
>
> For example, our EMC dMX4 storage has 96GB of cache. If your database is smaller than that (and nothing else uses the cache in this example) you could get very high throughput even with a single physical disk.
>
> Do you know which kind of storage you use? Is you system the only system attached to it?
>
> Harel Safra
>
> On 07/12/2010 20:05, Oracle Dba Wannabe wrote:
>> >> 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 >> 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? >>> >>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 07 2010 - 13:36:35 CST