Re: Enable 32K Block in 8K Block DB

"Charles" <cdavis10717_at_comcast.net> wrote in message news:83dbb3cd.0403281556.43addf38_at_posting.google.com...
> All,
>
> How do I enable a 32K block size in a database that was created with
> 8K block originally under Oracle 8.1.7.4.0?
>
> I want to have different block sizes for tablespaces in this database,
> which is now Oracle 9.2.0.4.0 on AIX.

No, you don't. Trust me, you don't.

It sounds like a good idea. And if you were running on raw, it would be.

But you probably aren't and therefore it isn't.

Your Oracle block size must match your file system buffer size exactly, and on AIX by default that's 4K.

So you got the original block size wrong as well.

And the only reason I'm more fervent than usual about this topic is that I've just been testing and quantifying the matter, results of which will soon be published. But you'd better believe that at this stage, Steve Adams' advice at www.ixora.com.au on the matter is looking exceedingly correct. Big time.

> I'm thinking of doing these steps.
>
> 1. Backup Controlfile to trace. Shutdown.
> 2. Alter init.ora to specify 32k block size.
> 3. Add init.ora parmeters for 8k cache size.
> 4. Startup Oracle and recreate/reuse the database with the backup
> controlfile to trace.

You got it about right, in the sense that when you have made a mistake in choosing your database block size, then you can do nothing about it other than re-creating the database from scratch, and using something like export and import to move the data between the two databases. Of course, in 9i you can misuse a technology or three, and create tablespaces with different blocksizes from the "default" blocksize (the db_block_size block size), provided merely that you pre-arrange for a cache to be available to handle the block reads from said odd-sized tablespaces.

For example:

set ... db_32K_cache_size=16M
then... create tablespace XXX datafile 'whatever' size 500MB BLOCKSIZE 32K;

Unless you can enable direct I/O on this file system, however, then forget about mucking around with different block sizes.

And I have to say in any case that 32K is rather ambitious. This had better be a very-largely-read-mostly sort of database, otherwise you are likely to introduce *huge* amounts of contention by moving to 32K. Better keep an eye on your buffer busy waits if you do it, anyway.

Remember that db_block_size *must* be the block size for SYSTEM and TEMP. It's important you get those two right, because so much database I/O involves them. So please read Steve's articles for the moment, and make your judgement on that basis. Around those two key default tablespaces, you do have some leeway in 9i, but not much if you've a file system buffer to contend with. So it may be that you don't actually need to do anything substantially more than create a couple of tablespaces with odd-sized blocks to house a few specific problem tables.

I think we need to know why you think 32K is the right block size for you before making any further calls on the matter

Regards
HJR Received on Sun Mar 28 2004 - 19:03:40 CST