Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Tablespace/Datafile Layout for Datawarehouse

Re: Tablespace/Datafile Layout for Datawarehouse

From: EscVector <>
Date: Wed, 13 Jun 2007 02:30:37 -0000
Message-ID: <>

On Jun 12, 8:05 pm, Greg Rahn <> wrote:
> On Jun 10, 1:29 pm, "Dereck L. Dietz" <> wrote:
> > This is a datawarehouse so I'm thinking 16k block size. If there are tables
> > that don't need the large I/O they can always be put in a separate
> > tablespace with an 8k block size.
> > As far as performance over recovery ... performance of the database here has
> > taken a last place over the ease of management for the DBA at least in my
> > opinion. If there is anything that can help the performance of the database
> > but would cause the DBA more work it never happens.
> An 8k block works fine for warehouses. I always use 8k and I've done
> benchmarks where a single table was over 20TB. I would stay away from
> mixed block sizes. There really is no need for it at all in a
> warehouse. The only real advantage of going to a larger block size is
> if you use compression. Since oracle uses block level compression,
> the more repeated values, the better the compression...and one can fit
> more data in a 16k block so it is possible to get a bit of extra
> compression.
> Either way, chose one size and stick with it.
> Remember, the IO size is governed more by the multi block read count,
> rather than the block size.
> --
> Regards,
> Greg Rahn

I thought that IO size was governed by the OS and MBRC simply biased the optimizer.

The bias is either away or toward tables as opposed to index scans and IO is read at full capacity regardless if this setting.

The larger the block, the larger the storage waste depending on how you setup your tablespaces. I've used 16k in warehouses, but have also used 8K. It really depends knowing the required batch response times, and storage overhead.

There are a lot of articles about matching this up with storage boundaries and such, but I haven't seen large gains by doing this which may be because I've done it wrong :) Received on Tue Jun 12 2007 - 21:30:37 CDT

Original text of this message