Oracle block size vs. OS block size

[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.