Re: [Q] db_block_size: 8K vs 2K default: Why is it better?

From: <markp7832_at_my-deja.com>
Date: Mon, 14 Feb 2000 14:23:21 GMT
Message-ID: <88934l$i49$1@nnrp1.deja.com>

In article <38A72282.D47EA434_at_hotmail.com>, Stephen Hurrell <hurrells_at_hotmail.com> wrote:
> <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
> <html>
> OK.
> <p>Still looking for valid information about the db_block_size effect.
> <p>I have read various documents describing why you would adjust your
block
> size from the default 2K.
> <br>These articles, books and personal opinions break down as follows:
> <ul>
> <li>
> Changing from the default 2k has no noticeable affect.</li>
>
> <li>
> Changing from the default 2k upto 4k or 8k has a big noticeable
effect.</li>
>
> <li>
> You should change oracle db_block_size to match your os or db size
(e.g.:
> 8k on HP-UX, 4k on Solaris 2.x)</li>
> </ul>
> I understand the relationship between larger block size results in
fewer
> block reads from disk, shallower indexes and all sorts of OS
performance
> improvements.</html>
>

The main reason to increase the database blocksize from 2K to 4k or 8k is for performance gains.

For an OLTP I would not go bigger than 8k at this time. As the hardware vendors continue to make improvements and advances the day is coming where even for an OLTP 32k and 64k make more sense than 2k.

Another reason performance improves with 4k and 8k over 2k is that you get less chaining in your tables with longer row lengths. It you are storing documents, audio, video, or other objects that are natural fit for the LOB datatype then larger block sizes help.

I have twice upgraded databases from 2K to 4k, both were on VMS, and in both instances there was a noticable improvement in performance for batch without any noticable problems for our on-line application. One screen was definitely better but for the most part we were unable to detect any change with our on-line application. The nightly export job also speeded up. This alone is why we always use 4k and 8k for our new databases.

--
Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Feb 14 2000 - 08:23:21 CST