Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 64-bit 8.1.6.2 Optimal Block Size for Data Warehouse?

Re: 64-bit 8.1.6.2 Optimal Block Size for Data Warehouse?

From: <yong321_at_yahoo.com>
Date: Mon, 15 Jan 2001 19:49:50 GMT
Message-ID: <93vk8r$vgv$1@nnrp1.deja.com>

In article <3A5F0EF8.2EC1_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> solxrosenberg_at_my-deja.com wrote:
> >
> > I'm building a star schema using 64-bit Oracle 8.1.6.2 on a 12-way
 HP
> > v2500 running HPUX11 and using EMC disk arrays. My fact table (10
> > numeric columns) has 1 billion rows total across 24 partitions. I
> > have 5 dimension tables. My current Block Size is 8k. What are the
> > pros and cons of upping it to 16k or 32k? Most of the queries
 against
> > this schema will use Oracle's Star Transformation.
> > Thanks!
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
> Bigger will *probably* be better mainly due to flatter indexes.
> However due to all sorts of restrictions when using file systems,
 you'd
> probably see most benefit from larger block sizes when you're on raw.
>
> HTH
>
> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)

It's interesting to think about the effect of larger db_block_size on the height of indexes. But as we discussed one or two months ago on here, index height in Oracle is quite low anyway (Jonathan Lewis demoed that it's very difficult to get up to more than 4 levels even with 2k block size). So as Steve Adams says, it's better to have 8k block size on UNIX unless you use direct IO, fast IO, or raw disks. 8k is the IO buffer size on all UNIXes (not Linux). 16k or 32k will just serialize IO processing.

Yong Huang

(yong321_at_yahoo.com)

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 15 2001 - 13:49:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US