Re: What blocksize?

Magnus Bergh wrote:

> In article <HQyu3.13553$vu2.3645_at_news.rdc1.tx.home.com>, Bert Scalzo
> bscalzo_at_home.com says...
> > That said, pick 4096 for OLTP.
> > If you had been a DW, then 8192 would be more appropriate. The old
> > default of 2048 is a default from way back and is no longer a good choice
>
> I find it hard to decide which blocksize is best. The books and
> documentation I have read is very vague about this.
>
> Why is 4096 better than 2048 if you have a system which most of the time
> only access few rows and don't use much full table scans? And how about
> the db_file_multiblock_read_count parameter? With the defaults in the
> starter database with blocksize=2048 and db_file_multiblock_read_count=8
> means that 16kb data is fetched for each i/o.
>
> Wouldn't increasing the db_file_multiblock_read_count give the same
> benefits as having a larger blocksize?
>
> Another questions: Should blocksize (or maybe more correctly blocksize *
> db_file_multiblock_read_count) match the physical cluster size on the
> disk?

Difficult questions. I'll offer this from my own experience.

I was working with a database application that we had ported from 7.2 to 7.3. At the time of the original porting we kept the blocksize of 2048.

On this system we had batch jobs which ran nightly, weekly and monthly. The montly jobs were the largest and from the time that the job kicked off from the cron until it finished as anywhere from 8 to 10 hours.

At some point it was decided that that our application should use the same block size as other Oracle 7.3 instances in the data center so we exported the data and rebuilt the database using a 4096 blocksize and everything else remaining basically the same. We then found that the time that it took to run the monthly cycle was about 60% of what it had been before - and the only thing that had really changed was the block size.

Apparently there was something that had changed in the database engine with 7.3 because past experiments had shown that there was no substative difference between the performance of a 7.2 database using 2048 or 4096 block size.

So, what I said is not particularly very scientific and I can't demonstrate why there was so much of a difference, but I do know that I when it was my turn to monitor the monthly jobs I was able to go to bed before the sun came up.

Ken Received on Tue Aug 24 1999 - 08:07:46 CDT