RE: Is my Oracle Server issuing more IO than it can handle

http://jamesmorle.wordpress.com/

He's a founder of BAARF - Battle Against All Raid 5 - and very knowledgable about storage subsystems in the Oracle world.

Rui Amaral
Database Administrator
ITS - SSG
TD Bank Financial Group
220 Bay St., 11th Floor
Toronto, ON, CA, M5K1A2
(bb) (647) 204-9106  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Amaral, Rui Sent: Tuesday, December 07, 2010 3:34 PM To: 'Oracle Dba Wannabe'; harel.safra_at_gmail.com Cc: niall.litchfield_at_gmail.com; okh_at_oraconsult.de; oracle-l_at_freelists.org Subject: RE: Is my Oracle Server issuing more IO than it can handle

It's a good chance that it's cache coming into play - though that is a very big generalization. Reads on a storage subsystem are typically faster because it doesn't have to worry about such things as parity whereas writes do (this is where you're RAID levels come into play).

Rui Amaral
Database Administrator
ITS - SSG
TD Bank Financial Group
220 Bay St., 11th Floor
Toronto, ON, CA, M5K1A2
(bb) (647) 204-9106  

-----Original Message-----
From: Oracle Dba Wannabe [mailto:oracledbawannabe_at_yahoo.com] Sent: Tuesday, December 07, 2010 3:28 PM To: Amaral, Rui; harel.safra_at_gmail.com Cc: niall.litchfield_at_gmail.com; okh_at_oraconsult.de; oracle-l_at_freelists.org Subject: RE: Is my Oracle Server issuing more IO than it can handle

Thanks, just another thought - if I go with the theory of the storage having an issue, I notice other io related requests - reads such as db file sequential/scattered having avg wait times under or at 5ms during this issue. Would that then imply that perhaps the storage is not at fault. I would have thought if the storage was io bound, that reads would be poor too? Or is this cache coming into play....thanks again

On Tue Dec 7th, 2010 2:32 PM EST Amaral, Rui wrote:

>you said moved to a different storage so a couple of questions come to mind on this:
>
>1) ask your storage guys how the 2 differ in configuration, ie., RAID
>types, FC configuration, I/O port sharing ( different db systems
>sharing the same port can be a problem)
>
>2) from the OS how the filesystem is configured. Misaligned block sizes
>between the array and the os filesystem can slow things down (think of
>it in terms of chained rows and you'll get close to the idea). This
>applies equally to ASM and regular filesystems
>
>3) and of course if they can tell if the luns are on the outside of the physical disks or not.
>
>in terms of calculating IOPS use Orion or Bonnie for pure OS throughput
>and then go from there. Just some thoughts
>
>Rui Amaral
>Database Administrator
>ITS - SSG
>TD Bank Financial Group
>220 Bay St., 11th Floor
>Toronto, ON, CA, M5K1A2
>(bb) (647) 204-9106
>
>
>
>________________________________
>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 2:26 PM
>To: Harel Safra
>Cc: Niall Litchfield; okh_at_oraconsult.de; oracle-l_at_freelists.org
>Subject: Re: Is my Oracle Server issuing more IO than it can handle
>
>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?
>
>
>
>NOTICE: Confidential message which may be privileged. Unauthorized use/disclosure prohibited. If received in error, please go to www.td.com/legal for instructions.
>AVIS : Message confidentiel dont le contenu peut être privilégié. Utilisation/divulgation interdites sans permission. Si reçu par erreur, prière d'aller au www.td.com/francais/avis_juridique pour des instructions.
      

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l