Re: Effect of multiple block sizes on performance

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 12 Jun 2004 11:55:03 +0100
Message-ID: <098801c4506b$b8a76f40$7102a8c0@Primary>

There are a few little oddities where
performance can be affected.

Using single table hash clusters - smaller block sizes can give a slight edge if you can't guarantee non-collision of hash keys.
If you aren't using CPU costing, the cost of tablescans is affected non-linearly, so (e.g.) a 16 block read on 4K blocks does not have the same cost as an 8 block read on 8K blocks. Bigger blocksizes will introduce a bias towards tablescans.

Of course, different block sizes may require different numbers of block gets for the same query, so possible changes in contention on latching. Bigger blocks could cause more collisions on inserts, updates, and deletes, especially on indexes.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: <ryan.gaffuri_at_cox.net> To: <oracle-l_at_freelists.org> Sent: Thursday, June 10, 2004 7:54 PM Subject: Effect of multiple block sizes on performance

: We have to publish data from several staging databases and we are using
transportable tablespaces. What are the effects of multiple blocksizes on performance?
:
: Has anyone used this under very heavy user load? We have a 4-Node RAC and
worst case stress level could be 2,000 transactions/second. We will also have a reporting element(which I hope will only run over night).
:
: Anyone use this? Not sure I want to, just looking at it. Its a hybrid
application so scaling is rather tricky.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Jun 12 2004 - 05:51:51 CDT