cancel
Showing results for 
Search instead for 
Did you mean: 

os blk size vs oracle blk size

SOLVED
Go to solution
lastgreatone
Regular Advisor

os blk size vs oracle blk size

The default file system block size on L-1000 11/64 is 8KB. The dba set the instances block size at 2KB. We have upgraded RAM to 1.5GB. The dba must increase the SGA. Should the oracle blk size be increased to 8Kb prior to increasing the SGA?
11 REPLIES
Darrell Allen
Honored Contributor
Solution

Re: os blk size vs oracle blk size

Hi Frankie,

For an HFS filesystem, the default filesystem block size is 8KB. For JFS (vxfs), it is 1KB.

This is from a recent post:

This is a very confusing topic. Hope the following helps.

Man newfs_vxfs will tell you that the default block size used when creating a vxfs filesystem is 1024 bytes. Look at the info below (particularly the last part) from knowledge base document number 1100330242:
http://us-support3.external.hp.com/cki/bin/doc.pl/sid=2405c118092bb1dd03/screen=ckiDisplayDocument?docId=200000054230626

<>
To determine the current block size for the vxfs file system:

fstyp -v /dev/vg00/lvol#

For example:

# fstyp -v /dev/vg00/lvol1

f_bsize: 8192

Note: The f_bsize parameter reports the block size for the vxfs file system.


To determine the current block size for the hfs file system:

tunefs -v /dev/vg00/rlvol# | grep bsize

For example:

# tunefs -v /dev/vg00/rlvol4 | grep bsize

sbsize 2048 cgsize 2048 cgoffset 24 cgmask 0xfffffff8
bsize 8192 bshift 13 bmask 0xffffe000

Here is an example of how HFS and VxFS actually interpret these fields:

Field HFS Value for VxFS
----- --- --------------
f_bsize block size (8K) largest possible block size (8K)
f_frsize fragment size (1K) actual block size (usually 1K)

<>


Here's an excerpt from document number S3100000884:
http://us-support3.external.hp.com/cki/bin/doc.pl/sid=c0a298f011fbef7f72/screen=ckiDisplayDocument?docId=200000046860490

<>
Use the following information to determine Oracle's optimal filesystem block size.

Execute the following command for the HFS environment:

df -g

Notice the value in the 'file system block size' field. The default value is 8192. Assign 8192 for Oracle in the init.ora file as the db_block_size parameter.


Execute the following command for the JFS environment:

echo "8192B.p S" | fsdb -F vxfs /dev/vg00/rlvol9

This command assumes that the Oracle database resides on lvol9. Use the logical volume that describes your environment.

Also, notice the 'r' before lvol9. The 'echo' command needs to execute against the raw device.

Once you execute this command, look for the number after 'bsize'. The default is 1024. Assign 1024 for Oracle in the init.ora file as the db_block_size parameter.

<>


From the docs above (and personally verified by creating a vxfs filesystem with the default block size) you will find that for vxfs filesystems f_frsize will correspond to the block size used to create the filesystem. You can easily find f_frsize using df -g /filesystem. Or use fstyp -v /dev/vg##/lvol and look for fragment size.

Darrell
"What, Me Worry?" - Alfred E. Neuman (Mad Magazine)
Christopher Caldwell
Honored Contributor

Re: os blk size vs oracle blk size

The default block size (that is if you built the filesystem using defaults) should be 1024.

(Default is vxfs, 1024 blocks).

To get the block size use
#fstyp -v special
where special is something like /dev/vg00/dblvol

vxfs
version: 3
f_bsize: 8192
f_frsize: 1024
f_blocks: 954368
f_bfree: 333592
f_bavail: 333592
f_files: 13696
f_ffree: 1073792296
f_favail: 1073792296
f_fsid: 1073741830
f_basetype: vxfs
f_namemax: 254
f_magic: a501fcf5
f_featurebits: 0
f_flag: 0
f_fsindex: 6
f_size: 954368


The block size for vxfs is actually the fragment size (doh!):

f_frsize: 1024

For optimal performance, Oracle should match this size. If you don't like the size, recreate the filesystem/lvol and specify the block size you'd like. Then make Oracle match that block size. I believe that most folks on "the forums" argue that a default of 1024 is too small; I believe most prefer 8192.



Darrell Allen
Honored Contributor

Re: os blk size vs oracle blk size

Hi again,

I thought this sounded familar:
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x59d828e43106d6118ff40090279cd0f9,00.html

If your filesystem block size is 8KB then it would be appropriate for the DB block size to be 8KB.

Not to belabor the point but your default filesystem type is specified in /etc/default/fs. I'd expect that to be vxfs but it doesn't have to be. And again, the default filesystem size for vxfs is 1024 bytes (1KB).

Darrell
"What, Me Worry?" - Alfred E. Neuman (Mad Magazine)
harry d brown jr
Honored Contributor

Re: os blk size vs oracle blk size

Why not!

http://web.singnet.com.sg/~petermag/tune.html

and this book might help:

http://vig.prenhall.com/catalog/professional/product/1,4096,0130187062,00.html

and then go to this link, although you will have to register (FOR FREE):

http://technet.oracle.com/doc/hp/server.804/A59371_01/ch3_opt.htm


live free or die
harry
Live Free or Die
Andreas D. Skjervold
Honored Contributor

Re: os blk size vs oracle blk size

Hi

Think I have answered this earlier but cant find the reference at the moment...

To cut everything to the bone:

Yes you should change the Oracle block size to 8K.
Oracle block size should be equal or a multiple of the os block size.

The bad news is that your DBA have to recreate the database to accomplish this task...
But then again he should have made the database right in the first place.

To change the blocksize:
- perform a full export of the database
- stop the database and delete all database files
- change init.ora parameter DB_BLOCK_SIZE to 8196
-recreate database with initial create script or create statements.
- perform full import of database.

As you see, this is better done right in the first place.

Andreas

PS: if this is a 9i database, individual tablespaces can be altered (block size) without recreating, but not the system tablespace.
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Wodisch
Honored Contributor

Re: os blk size vs oracle blk size

Hello Frankie,

we all seem to agree on the point of Oracle blocks being 8KB in size (db_block_size = 8192), but I do disagree on the file-system block-size:

What oracle wants (and they are right there) is to NOT have any head movement on the disk when an oracle block is read or written. You could achieve that goal with a file-system-block-size of 8KB, yes, but as long as all the 8 * 1KB file-system-blocks building one oracle-block are store in adjacent sectors on the diskdrive that's ok. And using the VxFS and creating the Oracle data files on the freshly made file-system is just doing that, since VxFS is allocating space in extents of MEGABYTES, not in individual single kilobyte blocks!
Hence show them the output of "fstyp -v" and point them the the "f_bsize" line (which is plain wrong) and they will be satisfied (seeing 8KB).
Tell them to take care of having the SGA sized properly is much more important: have them login to Oracle-Technet (the 3rd link Harry provided) and read the documentation about the "init*ora parameters", especially:
- db_block_buffers (lots of)
- shard_pool_size (huge)
- sort_area_size (not too small)
- pre_page_sga (true)
- lock_sga (true)
- log_buffer_size (1MB)

and get them a copy of Kevin Loney's "DBA Handbook"...

Just my $0.02,
Wodisch
Andreas D. Skjervold
Honored Contributor

Re: os blk size vs oracle blk size

Wodisch:
Your explanation is possibly the best I have seen. Clear and simple!

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Andreas D. Skjervold
Honored Contributor

Re: os blk size vs oracle blk size

Hey Frankie..

I see from your profile that you have assigned point to less than half of the 156 questions you have asked.

Please take time to reward us for contributing, after all this is part of the game in this forum.

Yours truly
Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
lastgreatone
Regular Advisor

Re: os blk size vs oracle blk size

Thanks to all for your replies, it has been an eye opener for the dba. And yes Andreas you are so right, I appreciate the expertise demonstrated via the forums and I will endeavour to reward accordingly.
Dennis J Robinson
Frequent Advisor

Re: os blk size vs oracle blk size

All.

Final answer on this:

Oracle Block size should either match or be a multiple of the OS block size.

In your case OS block size should be 8kb, OS fragment should also be 8kb.

In the filesystem/directory structure with the actual oracle installation, you should use the default vxfs filesystem parameters, as there are alot of small files which would eat up space with larger block size.

Smaller block size on vxfs side increase chance in fragmentation of the datafiles at the filesystem side. (DBA's will copy files in and out of filesystem, more than one at a time).

Smaller block size on vxfs side also incurs slightly larger CPU overhead in the copy of the OS block from kernel to user space. This slightly larger CPU overhead will show up as more time spent in system calls.

Good luck:



You know the drill
K.C. Chan
Trusted Contributor

Re: os blk size vs oracle blk size

Frankie, all the replies seems helpfull from an OS perspective. But if the database is created with db_block_size of 2KB, then their's no way of changing it unless you recreate the whole database.
Reputation of a thousand years can be determined by the conduct of an hour