Re: db block size
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2004 08:36:51 +0100
I think you're right about the performance through block
size manipulation. I KNOW that you can improve performance
if you bypass the file system and pick the right block size for
very special cases. I KNOW that you can make things worse if
you are wrong about how the data is actually accessed
and manipulated. I KNOW that it is extremely difficult to
set up a proper test case - especially when the critical issue
is concurrency, and particularly in view of the pl/sql strategy
of holding buffer pins to reduce latch contention.
At the end of the day, the performance gain is very small,
the effort involved in getting that gain is pretty large, and
the risk of making performance worse is significant.
Any structural change to the data storage mechanics
that you use for an object is likely to change the access
costs on that object. If your database hasn't been set
perfectly for the CBO, then any change to the cost of
access may cause dramatic changes in critical execution
paths.
Having said that, 10,000 random, isolated, single-row
queries against a look-up table will hit the root block
of the index 10,000 times - with 20,000 latch gets,
and hit each block in the table 10,000/N times (where
N is the average rows per block). This gives ONE hot
block. Do the same with a perfect single table hash
cluster, and you only hit the table blocks, and you only
latch once on the table block. No hot spot. If you can't
do a perfect (i.e. zero-collision) hash cluster, then the
smallest block size is a good move, as it reduces (very
slightly) the CPU cost of scanning the block for collisions.
(but any collision requires two latch gets anyway, which
is likely to be the more significant cost).
(PS I also got the hash table point from Steve Adams' -
but at his three day presentation at the Miracle Masterclass
that Mogens Norgaard runs each year in Denmark).
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: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, June 21, 2004 7:47 PM
Subject: RE: db block size
This sounds like one of those "efficiencies on the edges" discussion.
Everybody makes great points. But the real end result might be a gain in
efficiency by about a decimal of a percent.
Another great Oracle innovation that *may be* used in about 1% of the
installations. My gut feeling is that Oracle is adding functionality that
not many of us would use.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Paul Drake [mailto:discgolfdba@xxxxxxxxx]
Sent: Monday, June 21, 2004 2:39 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: db block size
--- Mladen Gogala <mladen@xxxxxxxxxxxxxxx> wrote:
>
> On 06/21/2004 01:57:00 PM, Paul Drake wrote:
>
> > are you saying that there are no exceptions to the "Absolutely zero
> > benefits" clause above?
>
> Yes, he is saying just that. I'm saying exactly the
> opposite thing.
> The main question for you is who do you trust, Pete
> or me? Go ahead,
> make my data.
>
> --
> Mladen Gogala
> Oracle DBA
Mladen,
I usually trust the data. I would also be averse to
going against some of the teachings of Ixora. ;)
I could wield the mighty course notes from Steve
Adams' seminar at the Hotsos Performance Symposium (pg
106 in particular), but that would still be in the
realm of heuristics and without actual data from test
cases - so I will not do so at this time.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
