Re: Enable 32K Block in 8K Block DB

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<406775f0$0$13661$afc38c87_at_news.optusnet.com.au>...
> "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
Thank you for replying.

The database I am working with is a 1.8TB SAP R/3 oracle database. I wanted to reorganize its indices into 32K tablespaces since some Oracle Performance book author(s) have indicated that's a good idea for indices.

Aside from that, SAP R/3 databases have thousands of tiny or empty tables defined in them, and I wanted to reorganize them onto 2K blocks.

Then, I could better size the caches for these tables as I attempt to tune this database.

That's the background incentive behind this request.

But aside from starting a thread about databse tuning, I'd just like to learn the proper steps for enable the multiple block sizes so I at least have the option of using them, pending I comply with my AIX block size constraints.

Thanks.

Charles Received on Mon Mar 29 2004 - 07:28:56 CST