Howard J. Rogers wrote:
> "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
I agree with one caveat. IIRC AIX 5L allows for other block sizes
up to 256K. You must check with your SA to determine the block size
before installing Oracle.
Reference: http://hpcf.nersc.gov/vendor_docs/ibm/gpfs/am3admst09.html
So while I believe you and Steve are correct on the Oracle basics. It is
possible that the original 8K block size is correct Ok: Highly unlikely
but possible.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Sun Mar 28 2004 - 23:17:17 CST
