Re: block size
Date: Mon, 26 Oct 2009 14:02:41 -0500
Message-ID: <bc04324b0910261202h3b0eae0fm64543144a7763852_at_mail.gmail.com>
Here are some facts that may contribute to your discussion:
- The Oracle Database Machine databases are created and tested with 8k blocks. That goes for DW and OLTP databases. 21GB/s scan rate from disk, 50GB/s scan rate from flash are with 8k blocks. These are published numbers, I'm not giving away any secrets here.
- A data file using 8k blocks will hold a little less data than the same sized data file using 16k blocks due to the block overhead.
- The larger the block size, the lower the concurrency, generally speaking. So beware of using very large block sizes as any potential future updates (never say never...) may suffer from concurrency issues.
I think the larger block size arguments come from believing that DW databases are generally larger than OLTP databases and so you want to avoid the additional block overhead and pack more data into the same files by avoiding block overhead. DWs don't usually have the concurrency requirements that OLTP does, so the larger blocks don't hurt anything.
I don't have a particular suggestion or recommendation myself except to note that the Oracle Database Machine uses 8k blocks for all standard deployments. In my own experiences, the vast majority of Oracle databases in use today use 8k block size, though I admit that I don't know of any block-size-specific bugs or issues.
Dan
On Mon, Oct 26, 2009 at 1:32 PM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:
> so, discussion with DW architect today
>
> "but this isn't OLTP,this is DW, in a datawarehouse you want the biggest
> block size you can have".
>
> my question to you (and him)
>
> "why? What's the theory and evidence for this?"
>
> In other words *why* do people think this? Note I'm specifically not after
> pointers to religious wars (entertaining though these are) but to particular
> logical arguments. This chap is bright (and like me somewhat loquacious) and
> I disagree with him, the resultant discussion could go on a while so I'm
> hoping to short circuit it.
>
> Platform is RHEL 4 64bit, 64bit Oracle 11gR1. This is *not* a warehouse
> with huge amounts of data (say, 500mb per year).
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 26 2009 - 14:02:41 CDT