Note in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
"Mike Ault" <mikerault_at_earthlink.net> wrote in message
news:37fab3ab.0403291707.6f43b737_at_posting.google.com...
>
>
http://www.embarcadero.com/resources/tech_papers/ResolvingOracleSpace_6_26.pdf
>
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
