Oracle block size vs. OS block size

[Manuel G]

We have a doubt related to block sizes:

- Oracle says "Database block size must be equal or greater than OS block size (n*OS block size)"

- Answer is Which is OS block refered by Oracle? We know at least 3 different:

1) Physical extensions size?
2) f_bsize reported by fstyp?
3) f_frsize reported by fstyp?

- We are handling an I/O problem and planning to increase block size in both OS and Database so we need to know what blocks we are talking about.

- When block matter will be clariffied we?ll open a new answer for doing block increase.

Thanks in advanced for help us.

[Andreas D. Skjervold]

Hi
The Oracle blocksize is set during database creation and is found in init.ora :
DB_BLOCK_SIZE=8192 (in Bytes)

The OS blocksize (physical page size) is typical 4096 Bytes on unix systems.
I'm infact not sure where you'll find this parameter in your system.

But you'll need a multiple of this, because otherwise the remaining part of the os block will be left unused.

We run with 8192 as DB_BLOCK_SIZE on our OLTP databases. For Datawarehouses larger walues might be better.

Andreas

[Alexander M. Ermes]

Hi there.
We run Oracle Applications for years now.
after doing the first reorg we have set OS block size and db_block_size to 8192.
That gave us the best results. We run 15 production databases on a V2500 with an EMC disk array.
Rgds
Alexander M. Ermes

[Andreas D. Skjervold]

Hi again!

You can use df -g to check the file system blocksize, and infact on my boxes this is 8192.

Andreas

[Manuel G]

Thanks, I?ve tried df -g and show me a 8K block size.

Is there any relation between parameters listed on my first message and this one reported by df? I?ve got a big doubt with that question.

If block size reported by df is correct one (this one Oracle says), we would have a 2K block size database so we need to recreate it with 8K block size, is that correct?

Thanks.

[Magdi KAMAL]

Hi again Manuel,

You can check the value of OS block size by issuing the command :

df -g

Oracle block size is defined in :

$ORACLE_HOME/dbs/init.ora
the entry is : db_block_size
expressed in bytes.

Notice :
I would prefer to leave the OS block size as it is, and tune the Oracle block size.


Magdi

[Magdi KAMAL]

Hi Manuel,

I mean by my last reply that we leave the OS block size 8k and sure not 2k.

I didn't read your reply, I just answered after reading your question.

Magdi

[Manuel G]

Hi again:

- Thanks for all your responses, they make me think we have something wrong in our system.

- We just need a bit more, look:

We contacted HP support in Spain and asked them about block size for testing Database block size was the same as it. They suggested to use "fstyp -v lvolname" and watch f_bsize parameter.

This command shows us a f_bsize=1K.

df, as all of you suggested, reports a 8K block size.

Our Database block size is 2K so we would have a great configuration mistake.

We should recreate database for fixing it what involves:
1) Export full database.
2) Recreate database with 8K. block size.
3) Import back database.

Are you sure that parameter returned by df is the correct OS block size instead of that reported by fstyp?

If so, we?ll proceed to recreate database (all we have because they?ll be mis-configured all of them I guess).

I attach outs from fstyp and df.

Thanks very much guys.

[jessica Koo]

Hi,

You considered these ?
1.
Bf you re-create your db, pls verify that
the exported db(e.g written at 2k block )
can be imported/retrievable with a e.g. 8k block.

2.
The difference in f_bsize;
It's due to the difference earlier
and latest sys. architecture of each machine.
The point is to select a block size which suits your architecture. E.g recommended FS_BLK_Size is 8k on a HP-UX 11.0 64bit. And DB_BLOCK_SIZE = multiple of OS_BLK_Size.
where FileSys Blk Size is the equivalent of OS_BLK_Size;

If there's serious performance issue,
pls refer to http://technet.oracle.com/doc/hp/server.804/A59371_01/ch3_opt.htm for more tuning info. of
Oracle on HP-UX.
e.g; raw devices, asy I/O to better performance

3. f_frsize;
A smaller fragment size allows more efficient allocation of disk space but increases the size of the filesys's free space map; value assign at creation of File System.
While a larger fragment size is desirable if you anticipate a file system with few but large files.

Hope it helps.

- Jessica

[Andreas D. Skjervold]

Hi again!

You're absolutely right to go for a 8K db_block_size, and there should be no problems in exporting, recreating and importing the database.
(Except maybe for running out of rollback segments, but that you'll avoid by setting parameter commit=y in the imp statement)

Andreas