Re: Performance difference between 2 machines

On Mon, 20 Jun 2005 23:33:07 +0200, "Matthias Hoys" <idmwarpzone_NOSPAM__at_yahoo.com> wrote:

>
><sybrandb_at_yahoo.com> wrote in message
>news:1119255391.716548.104290_at_g49g2000cwa.googlegroups.com...
>> The obvious cause is the difference in database block size.
>> Ct shouldn't have set it to 16k. Means one physical I/O executed by
>> Oracle, will result in *2* physical I/Os on O/S level. Those two I/Os
>> will suffer from rotational delay by the disks (ie, the second 16K will
>> be read not immediately after the first).
>> IMO, setting the database block size bigger than the O/S block size
>> will result in massive delays... which is what we are seeing here.
>>
>>
>> Hth
>> ---
>> Sybrand Bakker
>> Senior Oracle DBA
>>
>
>Sybrand, related question : we are using AIX 5.2 64-bit with jfs2
>filesystems, data on EMC Symmetrix. What would be the recommended database
>block size ? And do we need a smaller block size for OLTP than for DWH
>databases ?
>
>Matthias
>

Howard Rogers on www.dizwell.com recommends 4k for AIX. I have no reason to doubt that, as that is straight from the AIX specific doco, and it is the AIX default.

As to your second question: theoretically you would. However, remember it is of no use to set the block size smaller than a disk block. Which basically means 2k is ancient history. Remember also, you can speed up full table scans by setting db_file_multiblock_read_count. And setting the database block size affects both scattered reads and sequential reads, and Oracle will always read a complete block. This can hurt you in index lookups. If I recall Howard Rogers remarks, he is basically recommending to leave it alone, unless you have something Mickeysoft based. In that case any multiple of 8k will do.

--
Sybrand Bakker, Senior Oracle DBA