Re: Enable 32K Block in 8K Block DB

"Charles" <cdavis10717_at_comcast.net> wrote in message news:83dbb3cd.0403290528.7dae3736_at_posting.google.com...

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

It's not, and the advice is utter nonsense.

Unless you are running on raw, or have a file system that can do direct I/O, then mucking about with your block sizes is (a) a complete waste of time and (b) is actually going to introduce performance problems.

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

If there are 10,000 tables which are empty, and you are housing them in 8K blocks, you have wasted approximately 400MB of disk space (initial extents of 5 blocks). Are you really that desperate for a trifling 400MB of space in a 1.8TB database that you are willing to introduce performance-crippling block write issues should those empty tables ever become non-empty?

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

But that's the point. You *don't* have the option of using them. Not really. You're kidding yourself if you think you do.

And if you haven't got the message by now, anything that Burleson suggests is to be taken with a mine-full of salt.

Regards
HJR Received on Mon Mar 29 2004 - 13:32:01 CST