Re: What blocksize?

Using a higher blocksize can reduce the heigth of the btree indexes. This reduces IO when accessing these.

Sebastian

Kenneth C Stahl wrote:
>
> 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 Wed Aug 25 1999 - 04:39:41 CDT