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

[Mark van Hassel]

Manuel,

The value of the "-b" option of newfs is f_frsize in the fstyp -v output and fragment size in the df -g output. This is confusing.

Also check the mincache=direct and convosync=direct options for vxfs mounts, these options will bypass the system's buffercache, essentially creating raw IO.

Also the different log options (also with mount) will increase/decrease performance.

[Manuel G]

Jessica:

Can I conclude from your message that OS_block_size wich Oracle refers is that reported by df?

You asked to check importing whith 8K database block size, is there any way to do it without recreating and trying?

[Manuel G]

Hi again:

I?ve found a document that clarifies my doubt with fstyp and df command (see attachment).

Conclussion is:

In VxFS file systems, block size is that reported by fstyp (f_frsize).
The one reported by df is not the correct one.

In spite of this, we?ll recreate database with 8K block size and prove it.

[Andreas D. Skjervold]

Hi
The OS block size refered to by Oracle is the size reported in df, that is my opinion anyway.

As of checking the import; this should not be an issue as the exp/imp utilities doesn't care about the physical layout of the database. (other than that the filesystem layout must be identical).

exp creates an logical dump of the database, containing all sql statements necescary to recreate all database objects (tablespaces, tables, indexes etc) and table data.

During imp session the tablespaces will created with an implisitt "create tablespace.." statement. This is no different than if yourself is running this statement in your new 8K database, and thus ending up with 8K block tablespaces either way.

There is no way of doing this without the exp, create and imp procedure. (Although I've seen that the new 9i database supports different db_block in each tablespace, I think that in that case too you'll have to exp / imp to change things (system tablespaces anyway))

Andreas

[Mark van Hassel]

Hi,

Indeed, changing the block size of an vxfs file system will hardly have an impact on performance.
If you want to see with what options the file system was created use "mkfs -m [lvol name]". This gives the options specified with the newfs commands like "-b" , logsize, large file on/off etc.

[jessica Koo]

f_bsize can be seen as a composition of f_bsize. E.g 8k blk is made up of 8*1k(or 1*8k)fragments. Fragments are for efficient allocation of spaces.

I agree with Andrea's point on
The OS block size refered to by Oracle is the size reported in df.
Or the f_bsize is the OS Blk Size is the File System Blk Size use in storage access method in the VxFS case.

[Manuel G]

Hi again:

Only a few more.

When we talk about "recreate database" what we have to do is
(1) stop database
(2) change db_block_size in init.ora
(3) start database

Is simply that or should we anything more?

Thanks.

[A. Clay Stephenson · Accepted solution]

Hi Manuel:

No, it's not that simple. You have to
0) Do a full export with nobody else on the database.
1) Shutdown database (and listener).
2) Remove all database files and control files
3) Recreate database with the new blocksize
4) Startup the database
5) Do a full import
6) Startup listener

Given your present situation, you will see some improvement from this process (maybe as much as 15%).

Regards, Clay

[Andreas D. Skjervold]

Hi

Things are'nt simple when you want them to be.. Clay's list is what you'll have to do.

But I would like to give you another hint: if you're experiencing I/O problems you'll have to make sure that the files that make up the database is sufficiently spread across different disks.
The more disks the better but this is a minimum suggestion:
Disk 1: Datafiles for Application data
Disk 2: Datafiles for Application Indexes + System tablespace
Disk 3: Datafiles for Temp and rollback segments
Disk 4: Online Redo logs (Member1)
Disk 5: Online Redo logs (Member2)
Disk 6: Archived Redo logs

Idealy you should consider splitting the most active Application tablespaces over several disk depending on the I/O rate.

Specially redo and rollback should be kept apart as well as archived logs.

If you use localy managed tablespaces (as of 8i) the system tablespace isn't a problem anymore as Data Dictionary operations take place in the local tablespace instead of in the System tablespace.

Andreas

[Manuel G]

Andreas:

- I?ve read what you suggested about spreading data yesterday.

- Problem we face up is Service Guard + clustering + mirroring.

- We have a 2 node cluster, external disk box and Service Guard + Mirroring software.

- All datafiles lies in disk box where we just have 2+2 disks (4 Gb. each). In fact, we had a misconfigured disk box because only 1+1 disks were been used. This causes great I/O activity on peak times, specially on 1 disk which supports writes and all reads, the other one is designed for mirror so it only suppports writes.

- What we want to do is making use of the other 1+1 and spread the most accesed tables over both groups and internal box disks.

- If we notice apreciable improvement we?ll try to persuade our customer to buy 2+2 more. (Disk box has free space for new disks)

- I?d prefer to buy 20 disks box and don?t create a High Availability system that are making us suffer more than we wanted.

- We were very inexperienced when we began this project and we are paying consequences now. Our know how in matters like Oracle, HP-UX, etc.. were growing up whith project development and we made a lot of mistakes during it.


- Thanks a lot.