Re: reduce block size?

NT's Sector size is 512 bytes, and cluster size is 1 sector per cluster.

I would go with the consultants recommendation, but I would also increase db_file_multiblock_read_count as high as it would go (OS_MAXIO_SIZE/DB_BLOCK_SIZE).
I think the OS_MAXIO_SIZE on nt is 2M. So you can probably set it as high as 1024. If you set it
too high oracle will just use the max.

Oracle won't read more blocks that it really needs, it only uses the multiblock buffers
when it is doing some sort of sequential scan.

If the majority of your updates are small, reducing the block size will dramatically cut down on your I/O.

Remember, for everything that you read in and change the whole block is written back out, even if all you changed was one character in a 8k block.

If it was recommended that you reduce your SGA, you most likely are short on memory, so reducing the block size will also help you out in that area also.

<harronc_at_ibm.net> wrote in message news:37D87E72.45189C2_at_ibm.net...
> I would say that the recommendation to reduce your block size from 8k to
> 2k is nonsense.
>

I don't agree

> Most operating system work with an IO block size of 8k. This means that if
> Oracle reads a single 2k block into SGA, 6k of the IO is wasted. Not very
> efficient.
>
> I've found that IO is optimal when the block size is equal to or a
> multiple of the operating system IO block size eg 8k or 16k. It most cases
> 8k is the right size but occasionally 16k will give a better IO
> performance especially if working with hash clusters.
>
> The only trade-off is that a larger block size requires more RAM. However
> RAM is cheaper that IO, easier to add to your system, and OLTP often
> become IO-bound before memory-bound, especially if you have any batch
> processes running against the database.
>
> I suggest you keep it at 8K.
> Ciaran Harron
>
>
> David Spaisman wrote:
>
> > Hello:
> >
> > I ma working with an Oracle 8.0.4 application on NT 4 sp4. There are
> > about 125 users and the applicaton is definitely transaction-based wit
> > about 125 tables. This application serves users in the U.S. We have a
> > similar IT group in Europe and the same application there. They
> > currently have less users buit the application is expected to grow(I
> > don't know at what rate)..
> >
> > My DBA associate in Europe had a consultant(I believe from Oracle)
> > review the application and made three recomendations ;1) Take care of
> > some fragmented tables 2) decrease the shared pool as it was causing
> > memory swapping and 3) reduce the block size from 8k to 2k because of
> > the transactional nature of the application.
> >
> > I agree with the first two recommendations but I am quite surprised
> > about the third: reducing the block size.
> >
> > Has any one ever seen this type of recommendation ? Is it correct or
> > not? Has any one ever done this?
> >
> > If you need any additional information, please let me know. Thanks.
> >
> > David Spaisman
>
>
>
Received on Thu Sep 09 1999 - 23:42:07 CDT