1748182 Members
3524 Online
108759 Solutions
New Discussion юеВ

Oracle Block Size

 
Jason Berendsen
Regular Advisor

Oracle Block Size

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
11 REPLIES 11
Volker Borowski
Honored Contributor

Re: Oracle Block Size

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
Respected Contributor

Re: Oracle Block Size

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
Respected Contributor

Re: Oracle Block Size

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
Honored Contributor

Re: Oracle Block Size

... 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
Acclaimed Contributor

Re: Oracle Block Size

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
If it ain't broke, I can fix that.
JACQUET
Frequent Advisor

Re: Oracle Block Size

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.

PJA
Wodisch
Honored Contributor

Re: Oracle Block Size

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
Frequent Advisor

Re: Oracle Block Size

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
Frequent Advisor

Re: Oracle Block Size

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.
Put a smile on your users face, offer them a kiss (Hershey's Kiss).