Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Question on diff block sizes in DB

Re: Question on diff block sizes in DB

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Sat, 17 Apr 2004 18:37:14 -0400
Message-ID: <20040417223714.GA6901@medo.adelphia.net>

On 04/17/2004 04:30:40 PM, Tim Gorman wrote:
> Mike,
>
> With all due respect, the multiple block size feature is intended merely to
> support transportable tablespaces. Attempting to use it to optimize space
> or performance is just not very effective.

Not just that, it can be very detrimental to performance, because it splits the memory normally used for caching and thus prevents oracle from caching things optimally. I have to confess that I believed how 16k blocks would speed up full table scans from my big tables, so I created a 16k block tablespace and created a copy of a 23GB table in it. The difference was negligible, but I was able to fit far fewer index blocks and far fewer table blocks in my normal 8k pools, thus drastically reducing the efficiency of Cary Millsap's fabled "method C". All jokes aside, caching is an extremely important part of the RDBMS functionality which improves RDBMS performance by an order of magnitude. We're all joking about BCHR, but the fact of the matter is that caching is important. The lesson of the "method C" from Cary and Jeff's book is not that caching is not important, but that caching is not a substitute for a good application design and cannot be used as a measure of how well the system is performing. Needless interference with Oracle's caching system can and will slow things down, and creating multiple memory structures which cannot be used in place of each other (latches, keep pool, recycle pool, buffer pool) is precisely that. There are, however,legitimate uses of multiple block sizes, if one wants to substitute them for keep or recycle pools, but that must be well thought, planned and supported by performance monitoring. The main thing that must be understood is that one can have a table in 16k tablespace and an index in 8k tablespace. In other words, complicated DBA tasks should be performed by people who know what they're doing, just like Sledge Hammer from the immortal 80's show whose motto was: "trust me, I know what I'm doing". Of course, almost always, a disaster of biblical proportions ensued.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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 Apr 17 2004 - 17:33:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US