Re: Enable 32K Block in 8K Block DB

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<40687d58$0$28283$afc38c87_at_news.optusnet.com.au>...
> "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
Let's examine these allocations objectively shall we?

1 – Indexes like large tablespaces
This is quite true. Robin Schumacher proved that the index tree builds cleaner and that range scans happen with less consistent gets:

http://www.embarcadero.com/resources/tech_papers/ResolvingOracleSpace_6_26.pdf

Note the test that proves it.

2 – TEMP likes large blocksizes

This is very true. All temp segment access is sequential and 32k blocksizes greatly reduce logical I/O and disk sort times. I'm working an a benchmark right now that shows a one-third speed improvement of disk sorts in a 32k vs. an 8k blocksize.

While the database will have to be created with the blocksize this large to use temporary tablespaces this size, most databases that require large sorts will be data warehouse or DSS and thus will also benefit from large blocksizes.

3 – Multiple blocksizes

Multiple blocksizes are used in ALL the Oracle10g TPC-C benchmarks because they allow far faster transaction execution with less I/O. These vendors spent hundreds of thousands of dollars to get the fastest performance and their choice of multiple blocksizes speaks for itself.

UNISYS benchmark: 250,000 transactions per minute.

http://www.tpc.org/results/FDR/TPCC/unisys_es7000-420_291K_040209_fdr.pdf

db_16k_cache_size = 15010M
db_8k_cache_size = 1024M
db_cache_size = 8096M
db_keep_cache_size = 78000M

HP benchmark: 1,000,000 transactions per minute.

http://www.tpc.org/results/FDR/TPCC/HP%20Integrity%20rx5670%20Cluster%2064P_FDR.pdf

db_cache_size = 4000M
db_recycle_cache_size = 500M
db_8k_cache_size = 200M
db_16k_cache_size = 4056M
db_2k_cache_size = 35430M

Now, post some actual tests that prove or disprove these and we can discuss this some more. Until then, let's move on.

Mike Ault Received on Mon Mar 29 2004 - 19:07:07 CST