Re: block size
- From: Dan Norris <dannorris@xxxxxxxxxxxxx>
- To: niall.litchfield@xxxxxxxxx
- Date: Mon, 26 Oct 2009 14:02:41 -0500
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@xxxxxxxxx> 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
>
Other related posts:
