HP UX 11.0: Creating New LVM's For Oracle 8.1.7 Data

[LG Porter]

I have a HP N4000 Server presently configured with Oracle 8.1.7 instances. I'm in the process of configuring new logical volumes for Oracle data. At HPUX 10.X, the default block size was 8k(8192 bytes). What is the ideal block size associated with creating new LVM's containing Oracle data? What is the trade off between disk space and inceasing the block size? What is the best method for determining the block size of the presently configured LVM's?

[T G Manikandan]

oracle recommends its block size be equal to the OS block size or its multiples.

you can find the present block size on the lvol using
fstyp -v /dev/vgXX/lvolY

f_bsize---->block size

[T G Manikandan]

oracle recommendations

/*
Use 8K as the block size for Oracle Applications, SAP, and other large
commercial application packages.
Use 8K blocks if you optimize your database for batch activities or large
reports where large amounts of data are frequently read in by full table
scans.
For databases stored in the UNIX filesystem, use the same size for Oracle
blocks as the file system blocks.
For example, a default filesystem with 8K blocksize should have 8K blocks for
the database. If you choose a smaller size for the database, all writes of the
database have to do a read of the larger fs blocks and then write them to disk
(read before write).
If your database has tables with a row length of more than 4K, set the
db_block_size parameter to 8K blocks.
For applications which use direct read/writes with rows less than 4K, set
this parameter to use 4K blocksizes.
*/

[Steven E. Protter]

This decision really requires an analysis of your data.

If large data blocks are going to improve your Oracle performance, then you should go with larger block size.

The obvious penalty is that smaller oracle blocks will, when fragmented chew up more disk space.

It's kind of like the old DOS cluster size issue. A 1 byte file could occupy tons of disk space.

The best way to go obviously is to go with separate mount points for oracle data versus the other componenents, such as executables, archive logs and control files.

So, take a look at the oracle data and pick a block size that works for your data.

A lot of my peers who are oracle dba's are using 32K, 64K and 128K block size for oracle data, depending on your data.

We though actually still use default block size. Our analysis has shown no need to increase block size.

SEP

[Jeff Schussele]

Hi LG,

Steven's on track.
Data size should always make the call on block size.
BUT - The max block size IS 8k. Can't tune too much there.
The bigger keys to Oracle performance is striping - from wherever and THIS is where size can be tuned - , LV splits i.e. keep data, indices, logs & archives seperate on different LVs, and use diff mount options. Let Oracle cache data & index & let the OS cache logs & archs.

My $0.02,
Jeff

[T G Manikandan]

Oracle recommendations pointed above are for Oracle db_block_size and it not the OS block size.

Just want to be clear!

[Jeff Schussele]

I hear ya TG.
Just pointing out that you tune on the stripe size - not the block size.
There exist DBs where a block size of 8K would not be optimal.
Then again if they use raw volumes the question's mute.....

Cheers my friend,
Jeff