Re: general recommendations for setting block size?

A copy of this was sent to "Nuno Souto" <nsouto_at_nsw.bigpond.net.au.nospam> (if that email address didn't require changing) On Thu, 9 Sep 1999 22:33:17 +1000, you wrote:

>Agreed. In fact, I've been using 4K since V6.
>And without the slightest impact on performance.
>

Sorry -- i'll have to disagree that 8k uses more space then 4k.

There is much less block overhead in an 8k database (almost 50% less). I can fit more rows on 1 8k block then on a 4k block in general. I'll have less chained rows for long rows (and chained rows consume more storage then non-chained rows).

You'll want to watch your pctfree and pctused since they are percentages (20% of 8k is lots more then 20% of 4k) but other then that -- whenever i take a <8k blocksize database and put it into an 8k blocksize database -- it takes less space, not more.

So -- why the theory that 8k wastes space?

>IMHO, it's not dependant on the particular version
>of ORACLE. More likely, the speed of the disk/controller
>hardware combination and speed of CPU/memory access.
>
>In most systems built in the last 5 years, 4K is a very good
>compromise for speed/space. In fact, most of the UNIX
>implementations nowadays use a default FS block size
>of 4K. And NT seems to be limited to 4K in its low-level
>I/O size anyway, so it can't hurt there either.
>
>To check this on any UNIX FS system, get hold
>of Perl and do a stat("/") or any other FS attach point.
>The 11th element of the returned array will tell you the
>real FS block size. Most UNIX systems have some means
>of executing a stat() call to get this info, but formats may differ.
>The Perl call seems to return standard values no matter what.
>
>8K may be applicable to hardware like DEC's Alpha (sorry Compaq,
>old habits die hard...) or systems running data warehouse
>databases. But with the proliferation of U2-SCSI and very fast
>and high capacity disks combined with oodles of fast memory,
>it won't be long before we can standardise on 8K and stuff the
>space wastage...
>
>Of course, there are always very specific and odd cases. YMMV.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 09 1999 - 08:00:16 CDT