os blk size vs oracle blk size

[lastgreatone]

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?

[Darrell Allen · Accepted solution]

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

[Christopher Caldwell]

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]

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

[harry d brown jr]

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

[Andreas D. Skjervold]

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.

[Wodisch]

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]

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

Andreas

[Andreas D. Skjervold]

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

[lastgreatone]

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.