Re: Oracle vs filesystem block size

In article <370lps$e3q_at_ticsa.com>, gavin_at_durban.vector.co.za (Gavin Maltby, Vector Durban) writes:
|> Hi,
|>
|> According to the docs, the db_block_size for Oracle should be a
|> multiple of the filesystem block size on which the datafiles reside.
|> That makes good sense for random access. For large databases
|> I figure 8K for both is fine.
|>
|> A client running Oracle 6 on HP series 800 with logical volume
|> manager want to optimise block sizes when they move to 7.
|> What may have been a lack of communication between the dba
|> and the sys admin has resulted in the newer parts of the database
|> residing on 4Gb fast-wide disks with a filesystem block
|> size of 64K, while the db_block_size is 2K. My feeling is that
|> both should be moved to 8K (Oracle max). There is a suggestion
|> that just the Oracle block size could be moved to 8K while the
|> filesytems could stay at 64K---I don't like it.
|>
|> Anyone with experience/opinions on db_block_size vs filesystem block
|> size---I'd be pleased to hear from you.
|>

I've been going through the same analysis, so I don't have any experience yet. I reached the same conclusion that Oracle and file system block size should be the same. Having a smaller Oracle block will cause slower I/Os, since the file system will do the larger I/O anyway.

Why do you prefer 8K blocks to 2K blocks? When doing table scans the parameter db_file_multiblock_read_count kicks in and can be adjusted larger in response to small blocks giving maximum I/O throughput. On the other hand when doing single row retrieval, such as indexed retrieval, won't the smaller I/O complete faster?

Another option to consider on HP/UX is to use the logical volume manager to create raw volumes, and take the file system out of the picture completely. This was the approach stongly recommended by Oracle's Product Manager for HP at IOUW.

Bill Wood

My opinions, not anyone else's. Received on Thu Oct 13 1994 - 22:31:41 CET