Oracle block size, Advise Pl.

[Gopinath]

hello guys,
what would be the impact of having an OS block size greater than the Oracle block size?
I had recently seen an oracle 8.0.4 database runnning on Sco Unixware 7.1 where the unix block size was 4kb while the oracle block size was 2 kb!!
the system was running slow, could this be the sole reason?
the hit ratio was 50 % .
Please advise
gopinath

[sambavan]

Yes, this could be a possiblility. But my suggestion at this time would be try increasing the db_block_buffer and shared_pool_size. And try doing some performance tuning befire you try new block_size by creating a new database and migrating the old one to the new.

Good luck,
Sam

[anujpathak]

This could be one of the reason because for your 2k block data os is allocating 4k at os level. and when os can provide 4k of data in one read u are getting only 2k.

if u can re install the oracle database its better to go for it. otherwise
try to apply some other performance tuning techniques.

[oddster]

Hi,

If you rebuild your db, I'd recommend a block size of 8K.
Oracle themselves say this should be used as standard for all but 'tiny' databases (< 1Gb).

I presume the 'Hit ratio' you refer to is that of the data buffer.
As a previous reply implies, this is primarily a result of your SGA buffer cache being too small - you should look at either increasing this or, if resources are limited, pinning some of your frequently accessed objects in memory.

Are you 24x7?? The shutdown of the database causes the SGA to be flushed and thus all data must be re-read from disk on restart - degrading performance and therefore your hit ratio.

Let me know how you get on,

Best regards,

Andrew Oddy

[tamilselvan]

I believe most of the Unix OS block size is 512 bytes.
Are you sure that Unixware OS block size is 4K?

[Gopinath]

Sorry,
I was misinformed regarding the unix block size, it IS half Kb.
I have seen that due to small size of the log file, (1 MB), log switching is taking place almost every two minutes.
Also, the multiblock read count parameter has been set as 8 (oracle block size = 2Kb)
Could all the above reasons have a very serious impact on the database performance?
Thanks

[Halo]

Increase the size of the redo log files. db_multiblock_read_count of 8 is not bad, unless if this is a data warehouse. Increase the size of the db_block_buffer to get a better hit ratio.