Oracle Block Size

[Jason Berendsen]

Our systems run HP-UX 11.00. Our DBA's are asking what our OS block size is set to so they can set their Oracle block size accordingly. We are explaining to them that with VxFS block size doesn't hold the relevance that it did with HFS. It is our understanding that extents are the parameter that should be looked at.

So they are wanting to set their block size to 64MB to match the max extent size of a VxFS filesystem. They state Oracle's block size is dynamic, so this would not cause any degredation. Is this a correct statement? In this case should we be looking at extents or OS blocks?

It seems to us that if they set their block size to the maximum (64MB) there will be a lot of wasted reads. Are we understanding the VxFS block size and extent structure properly?

Thanks

[Volker Borowski]

Hi,

64 M ! Bytes is nonsense !

The Oracle Block is the smallest unit, Oracle can address. Imagine just creating 20 Tables will cost you 1GB !

vxfs has a blocksize, that you usally set to the value that Oracle needs.

newfs -b 8192 ......

for a oracle blocksize of 8k is fine.
No need to adjust the fragment size as it was with HFS.

Hope this helps
Volker

[Praveen Bezawada]

Hi
It is advisable to have oracle block size that is a multiple of OS block size ( or page size ). Otherwise it had lead to excessive fragmentation and effect the performance of oracle.

..PRB..

[Praveen Bezawada]

Hi
One more point.....
we general have a value like 8192k in case of large databases where you expect many read/writes to the database.

...PRB...

[Volker Borowski]

... oh forgot about the more important RAM.

Setting the mentioned block size would leed to a database buffer pool in a size of 16 blocks eating up 1GB of RAM.

Since Oracle needs to read an entire block into memory, before it can address the data in it, selecting a single row from a table would lead to a read-io of 64MB of data (needed or not).

Go for 8K und you will be fine. for special cases you might consider 64 K, but not 64 M !

Volker

[A. Clay Stephenson]

Hi:

I think 64MB is a bit excessive by 3 orders of magnitude! After quite a bit of measurement, I find that 8KB is a very good all-around blocksize. You may want to experiment with the db_multiblock_read_count init.ora parameter. I would start at 8 and measure. The good news is that this parameter unlike db_block_size can be changed without rebuilding the database.

I think you will find that the product of db_block_size and db_multi_block_read_count will optimize at between 64KB and 256KB.

Regards, Clay

[JACQUET]

Hi,

For another information, the default Block Size when creating Vxfs File System is 8k.
You can see it when you execute :
df -g /vxfs_fs
Otherwise, i completely agree with PRB:
db_block_size = k * vxfs_bs
k = 1 is Ok.

PJA.

[Wodisch]

Hello Jason,

Oracle has never had a "dynamic block size", so your
DBAs do not know what they talk about ;-)

Show them the ouput of "fstyp -v" onto the filesystem
containing the database datafiles, and they will read
"8192 bytes/block" - which is a lie, of course, for it is
at the default of 1024bytes/block certainly, but they
will be satisfied, and you do not have to tell them :-)

HTH,
Wodisch

Oh, and whoever wrote about 8192k - hey, that's 8MB!
Are you crazy? having only 1000 db_block_buffers then
would need more than 8GB of SGA!!!

[ajax13]

Oracle recommends this parameter be between
2048 to 32768 BYTES. They also recommends it should not be smaller than OS block size. I was never able to figure out the math behind this, but I had an instructor who told me if NT make 8K if Unix make 16K or 32K. I always use 16K.

Thanks

[Ed Ulfers]

Here's the skinny from a Senior Oracle DBA...

Up until 11.0, the HP OS page size was 4k.
Now this is a configurable value, but most SA don't touch it.

The filesystem fragment size is also something to consider, generated during the file system initialization. The default is 2K.

Most reasonable systems I set up with 8K block size for Oracle, and have the SA rebuild the filesystem with an 8k fragment size.
This usually works farely well over-all.

Here's the catch...
The best settings are dependent on the Application being run with the knowledge of the Hardware it is on. As a Senior DBA, I tell the SA what I want, not the other way around.

Hope that helps,
-- Ed Ulfers
Oracle/UNIX Admin.

[Jared Westgate_1]

Hello Jason,

With all the different replies you've gotten, I figured that I'd try to clear the air so to speak. It is important to note that the Oracle Block size is just that, the size of an ORACLE block. What that means is that the block size is the smallest amount of data that oracle will read/write.

When you read data from the database, Oracle will grab however many blocks is specified in the multiblock read count parameter file, and put it into the DB block buffer cache.

Now for the pros/cons of block sizes. The smaller the block size, the more overhead Oracle uses. That is because each oracle block has header information. However, if you have small rows in your database AND you are performing a lot of non-sequential read/writes, in general, it is favorable to have a small block size. This is because you will be pulling only the data you need.

If you have a large block size (say, over 8k), it uses less overhead (ie, there are less headers because there are less blocks). This is good if you are doing a lot of sequential reads/writes.

Let me use an example to show you this. Say you have a block size of 16k. Your rows are about 3k each. If you are reading rows 2,3,4 from a table, the database may only need to read one block to get the data because all 3 rows would be stored in one block. If you had a block size of 4k, you would need to read 3 blocks to get this data.

Now, if you need rows 3,6,19 and you have a block size of 16k. You will have to do 3 reads. The first read will get rows 3,4,5. The next, 6,7,8 and the last 19,20,21. That means you are pulling 6 extra rows you didn't need. If you have a 4k block size, you would only need 3 reads, pulling no extra data.

This is all very simplistic, but I hope you get the idea. The general recommendations seem to be, if you are an OLTP type of application, try to use a smaller block size. With a batch type of application, use the biggest block size you can. The last time I checked, 16k was the largest Oracle block size you could specify on HP-UX.

I like to use a block size of 8k for databases that are a mix of application types (or if I don't know what kind of application it will be). This is considered a 'medium' block size. The best advice I can give you is experiment with it. It is not easy to do, but if you have the opportunity, try out the block sizes.

The big catch here is don't make the block size smaller than the OS block size. Remember that the database HAS to pull a full block. If the OS has to do two reads for every one Oracle block, you are wasting a lot of resources. Also, ALWAYS make the Oracle block size a multiple of the OS block size.

I hope I covered everything and made it a little easier to understand. Let me know if you have any questions.

Jared Westgate

[Praveen Bezawada]

Sorry my mistake it was to 8192b and not 8192K..

and thanks Wodish for pointing it out ... : )

...PRB...