Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Enable 32K Block in 8K Block DB

Re: Enable 32K Block in 8K Block DB

From: Jonathan Lewis <>
Date: Tue, 30 Mar 2004 08:36:43 +0000 (UTC)
Message-ID: <c4bbir$icb$>

Note in-line


Jonathan Lewis

The Co-operative Oracle Users' FAQ

April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar

"Mike Ault" <> wrote in message

Good reference, worth reading. A significant fraction of it is about a product, but it includes sensible technical material, and doesn't attempt to distort or be selective about technical details in order to sell the product.
> Note the test that proves it.
But everyone who reads the paper can also note the following preamble to the test: <<quote>> For example, large (16-32K) blocksize data caches can be used to contain data from indexes or tables that are the object of repeated large scans or tables that would always suffer from row migration or chaining. <<end quote>> The test case is not fully described, but let's we assume the cleanest example that would fit the details - the table has an id which is sequence based, the query is an "index-only" query which does a range scan rather than a fast full scan, and the index was built clean with the default of pctfree 10, then the query has to be something like: select /*+ index(t1) */ count(*) from t1 where id between 1 and 380000 (I recorded 836 CR gets, but who's going to fuss about a small error - especially when there are a few recursive calls in there). That's a very special case type of query to get a reduction of 400 logical I/Os. Change it to a query that has to do a randomly scattered visit to the table for every index entry and the logical I/O comparison would be: 380844 vs 380415 In that scenario, it becomes much more important to ask - is the benefit worth pursuing, what costs might I incur as a side effect that could far outweigh the benefit.
> Multiple blocksizes are used in ALL the Oracle10g TPC-C benchmarks
> because they allow far faster transaction execution with less I/O.
> These vendors spent hundreds of thousands of dollars to get the
> fastest performance and their choice of multiple blocksizes speaks for
> itself.
I didn't have time to read this benchmark, but I've recently read the HP million transactions per minute full disclosure document. One thing that stands out is that the benchmarks are designed to survive the few hours needed for the test, and wouldn't survive a week in a full production system. I wouldn't quote a benchmark as a generic proof - would only go so far as "Oracle uses this technique for a reason - in what special cases might it work for you".
Received on Tue Mar 30 2004 - 02:36:43 CST

Original text of this message