Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Myth revisited ...

Re: Myth revisited ...

From: Yong Huang <yong321_at_yahoo.com>
Date: 17 Nov 2003 14:10:57 -0800
Message-ID: <b3cb12d6.0311171410.16a09aae@posting.google.com>


"Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message news:<3fb8d18a$0$13984$afc38c87_at_news.optusnet.com.au>...
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:9u3ub.14700$aT.6285_at_news-server.bigpond.net.au...
> >
> > The vast majority of Oracle databases sit on top of O/S file systems and
> > file systems use buffered I/O (unless direct I/O is set). Therefore,
> > although having large block sizes for indexes sounds like a great idea in
> > principle, in practice it will actually *hurt* performance. The correct,
> > optimal block size for these environments is the I/O buffer size. Setting
> > the block size any larger, say twice the I/O buffer size (eg. 8K on AIX)
> > would result in every logical I/O on the index requiring two O/S calls.
> > Having double (or more) O/S calls per logical read on your indexes is going
> > to have a detrimental effect, even if such larger blocks succeed in reducing
> > the index's overall height.
>
> Whoa boss! Are you telling me Oracle will divide the block size by the
> file system block size and issue multiple calls?
>
> Because if it does the usual read() or write() call, all it needs
> to indicate is that it wants 16K and it's up to the OS to do two
> I/O requests (which are NOT the same as two calls from Oracle) to
> get those 16K, assuming of course 8k file system size.
>
> Otherwise you'd be limited on I/O size to the file system block size
> on each OS call, which is not true at all! You can issue in a single
> read()/write() call quite large requests. Without any major context
> switching needed.
>
> So yes, in the example above you get 2 I/O calls, but initiated
> by the file systems code in the OS itself, which is quite efficient
> and very close to a readv() in speed. We talking low level I/O
> calls, the kind you can't do unless you're using raw.
> What is terribly inefficient is to request an I/O size that is
> not an exact multiple of the file system block size. For obvious
> reasons.
> But to issue a call that asks for twice the file system block size
> (or three or four times, for that matter) is not a killer by
> any stretch of the imag.

I was going to post a response to Richard's message and saw yours. You're quite right. When db_block_size is 16k for instance, the Oracle process issues one single system call that asks for 16k read or write, even though the filesystem block size (I/O chunk size) is another number, e.g. 8k. Richard may be misreading Steve Adams' original db_block_size article.

Regardless what theory proposed by whom, I only trust real stories or good benchmarks. I have a feeling that in many typical cases, using a db_block_size twice as big as filesystem block size yields better performance than using a db_block_size matching filesystem block size. Bert Scalzo's article shows that 8k is definitely better than 4k on ext2 (www.linuxjournal.com/article.php?sid=5840&mode=thread&order=0). It's very likely his filesystem is 4k but we can confirm with him.

Yong Huang Received on Mon Nov 17 2003 - 16:10:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US