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 03:34:19 -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

Maybe I'm reading it wrong, but it looks like it determined by os limits gathered by looking at mbrc and is a cost bias that limits or breaks up work when segment reads are smaller than block reads.

I don't have so I can't remember... Received on Tue Jun 12 2007 - 22:34:19 CDT

Original text of this message