Re: DB_BLOCK_SIZE set to the highest value

Mike;

We set DB_BLOCK_SIZE to an appropriate value for our application. Rule of thumb from Oracle is smaller for OLTP, larger for Data Warehouse. For hybred databases go inbetween. DB_BLOCK_SIZE should be a multiple of OS block size, so never pick a DB_BLOCK_SIZE smaller than your OS block size. DB_BLOCK_BUFFERS specifies how many blocks of DB_BLOCK_SIZE your SGA can hold at a time. This will vary depending on your DB_BLOCK_SIZE. Also remember that DB_BLOCK_SIZE is set before you create the database and cannot be changed afterward. The CREATE DATABASE command uses your parameter file and this is one of only two parameters I know that can't be changed after the database is created. If you need to adjust your DB_BLOCK_SIZE you have to export your data, drop and recreate your database, and import your data into your new database.

Oh... and this all changes in Oracle9i because they're going to support multiple block sizes in the same database. You'll be able to specify a block size at the tablespace level... but you'll need to configure a buffer cache in your parameter file for each block size you're going to use.

Best!

Randall

Mike Jay <mikejay_at_mitre.org> wrote in message news:3B0E5FB6.3B92BD67_at_mitre.org...
> Do we set DB_BLOCK_SIZE to the highest value or to the OS block size?
>
> According to CH20 of Oracle8i Designing and Tuning for
> Performance (A76992-01) and CH1 of Oracle8i Reference (A76961-01), the
> DB_BLOCK_SIZE is operating system and device dependent.
>
> Using the "df -g" command (no quotes) on the local system to check
> for the optimal value, my system has 8192 block size so that's what
> I use for the DB_BLOCK_SIZE in my initTHESID.ora pfile.
>
> Did I miss the point? What about the SGA and the DB_BLOCK_BUFFERS as
> the means to ensure adequate memory?
>
> I am a developer rather than a "real DBA" so I have to rely on the
> manuals to make the tradeoffs as opposed to "real world" experience.
>
> Have a great day,
> mikejay
>
> Dino Hsu wrote:
> >
> > Dear all,
> >
> > In a book, it says:
> > "In the config.ora file, setting the DB_BLOCK_SIZE parameter to the
> > highest value supported by your operating system" (see note for
> > source)
> >
> > My question:
> > 1.How to find the highest values in Windows NT/2K and Unix's?
> > 2.Are these values somehow related to the file system block size?
> > 3.Why highest?
> > Thanks in advance.
> >
> > Dino
> >
> > Note: soure information:
> > p.44, Kevin Loney, Oracle 8i DBA Handbook
> > Ch1. Getting Started with the ORACLE Architecture ->
> > Creating a Database ->
> > Modifying the Template Creation Scripts
Received on Fri May 25 2001 - 09:04:28 CDT