Re: Tablespace/Datafile Layout for Datawarehouse

On Jun 12, 8:05 pm, Greg Rahn <g..._at_structureddata.org> wrote:
> On Jun 10, 1:29 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> 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 Rahnhttp://structureddata.org

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. http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#sthref1473

I don't have http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html so I can't remember... Received on Tue Jun 12 2007 - 22:34:19 CDT