| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: db block size
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/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
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_at_yahoo.com]
Sent: Monday, June 21, 2004 2:39 PM
To: oracle-l_at_freelists.org
Subject: Re: db block size
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.
-- 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 Wed Jun 23 2004 - 02:33:34 CDT
 |
 |