Re: Question on diff block sizes in DB

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 19 Apr 2004 19:29:53 +0100

Notes in-line.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

----- Original Message ----- 
From: "Mladen Gogala" <mgogala@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, April 18, 2004 9:21 PM
Subject: Re: Question on diff block sizes in DB

On 04/18/2004 04:03:15 PM, Jonathan Lewis wrote:
> With a stripe size of 64k, That's a minimum of 4 discs
> kicked into action on a single read.

Is this bad? That means that every read larger then 64k will be done in
parallel, and not sequentially.

--- Putting it another way - the query won't complete
--- until the read that went into the longest queue gets
--- to complete.

>  Now imagine you
> kick off a parallel tablescan:  how many devices do you
> actually have for your 17GB, and how much I/O contention
> will a single parallel request cause on your system.

That is not necessarily true. Contention will be caused if and only
if the channel between the RAID device and the RAM doesn't have sufficient
throughput for transferring all that data, which is normally not the case,
at least not with FC/AL type channels.

--- I was thinking more of the case where you have four
--- slaves making a request, which turn into a total of 16
--- I/O requests, against a small number of devices.  It
--- doesn't take many parallel queries before the device
--- queue lengths become the significant bottleneck.

> If you are stuck with a 64K stripe size, I'd consider
> keeping your multi-block read size down to 64K in
> the hope of minimising contention..

You don't want to read in parallel? Why would that be good?

--- It could be good for a single-user system, but I was
--- assuming the OP was expecting more than one person
--- to use the database.

>                                             But as a special
> can you guarantee that the boundary of a 32K Oracle data
> block will always synchronize with the boundary of a 64K
> stripe ?

On EMC Symmetrix I was told that it is possible to align raw devices
in such a way. I have never managed an EMC array, so I cannot tell
for certain.

--- I believe it's not usually a problem when you use raw
--- devices - so long as you remember to do it.  I believe
--- that it isn't all that hard on (some) filesystems - so long
--- as you realise it may be necessary. (I think Cary has a
--- story about that).

>  If you can't then perhaps one block out of every
> two will span two stripes, and therefore activate two
> disk drives.  If you switch to 8K blocks then it's only
> one block in eight that would have the problem.

Well, that's an old wisdom which doesn't necessarily hold water with the
latest Symmetrix and Shark boxes any more.

--- "necessarily" is a good word.

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: