Re: Oracle 7.3.3 ---> Oracle 8 upgrade

Jonathan Lewis wrote in article <01bcf803$faf609f0$294b989e_at_WORKSTATION>...

>
>Can you prove that increasing the blocksize will help ?
>It is a common misconception that 'big datatabases require big block
>sizes'

I think there is no ready recipe in this regard, so my thinking about going to 16k block
size at this point is rather speculative.

Here is why I want to go to larger block size:

1. some of the tables we have have avg_row_len>700 (yes data model sucks but at this point we have other priorities) so in 4k block one can fit only ~5 rows. The difference 4k - 5*700 = 500 is wasted space which is 500/4k > 10% of total space. By going to 16k block size I can improve: 16k/700 ~ 22. The difference 16k-22*700= 600 which is < 5% of total space. So I can store more data per unit disk space, hence I can read more data per unit time, hence --> perfomance improves.
2. Our database sits on MTI RAID arrays with stripe depth of 64k. Any read request < 64k hurts perfomance. Hence by bumping up database block size I can improve the things a bit.

>The last couple of datawarehouse jobs I got into were larger than 1TB
>raw data,
>and one was almost certainly a prime candidate for a 2K blocksize,
>the other
>was a toss-up between 2K and 4K.

well, please explain how can you get better by reducing db_block_size to 2k (i.e. to less than OS block size which is 4k for AIX). I'm pretty sure anything less than 4k would be sharply detrimental to database perfomance. (just try to run "dd" command with bs=4k and bs=2k and compare).

Cordially,
Serge

>
>Index strategies and phsyical data clustering have a significant
>effect on optimum
>blocksize (unless memory is totally unlimited).
>
>
>
>S V <sv1_at_mindspring.com> wrote in article
><01bcf7b4$7111d7e0$3d5c0c26_at_sfinance3>...
>>
>> That's what we are contemplaiting
>> to do on Christmas eve.
>> The current size of database is about 100Gb.
>> It runs on AIX 4.2 platform.
>>
>> I have some questions to the respectable Oracle
>> community.
>>
>> 1. As part of the upgrade we also want to bump up
>> db_block_size to at least 8k from current 4k.
>> I personally would like to go to 16k block size
>> since our db is purely data warehouse-style with
>> quite big tables.
>> Does Oracle 8 support block sizes > 8k ?
>> 2. Apparently we would need to do full
>> database export/import. What kind of
>> problems should I expect with importing
>> Oracle7.3.3 dump into Oracle 8 database?
>> 3. Generally what kind of problems should
>> I expect when going Oracle7.3 --> Oracle.8 ?
>>
>> Thanks,
>> Serge
>>
>>
Received on Sun Nov 23 1997 - 00:00:00 CST