With the advent of Oracle10g we are seeing blistering benchmarks using
a multi-million dollar servers with up to 64 CPUs and over a
half-terabyte of SGA RAM. Because the vendors want to dazzle,
they hire the best experts to hypercharge their benchmarks using
Oracle high-performance techniques.

If we take a close look at the
benchmark methodology we cannot attribute the blistering transaction
speed solely to super-fast hardware platforms. In order to appreciate
the nature of these blistering benchmarks we need to take a close look
at how the Oracle professional designed the database to accommodate
super-fast data retrieval.

I highly recommend the book "Oracle
Benchmarking" for more details on conducting and interpreting Oracle
benchmarks. This book is written by numerous Oracle tuning experts
and shows a complete method for Oracle and SQL Server benchmarking

UNISYS Windows
Benchmark

In this benchmark, UNISYS set the
world-record for price-performance, achieving over a quarter-million
transactions per minute using Oracle10g on Windows. The
$1,400,000 server had 16 Intel Itanium 2 processors running at 1.5GHz,
each with 6MB of Level 3 (iL3) cache and 128GB of memory.

This voluminous benchmark disclosure report (206 pages) offers some
interesting clues into the way that the Oracle DBA configured
Oracle10g for this world-record benchmark:

Real Application Clusters –
The benchmark used 16 Oracle instances, each mapping to four
processors.

Multiple blocksizes – This
world record used four separate blocksizes (2k, 4k, 8k, 16k) to
isolate RAM data buffers and place objects within the most
appropriate block sizes.

Oracle Hidden Parameters –
We see that the benchmark DBA employed several Oracle hidden
parameters to boost performance. Like most vendors, they take
advantage of hardware-specific performance features:

Large RAM data buffers –
For each of the 16 RAC nodes, this benchmark used about 44 gigabytes
of RAM data buffers each, distributed into five separate RAM data
block buffers. The total RAM data block buffer storage was over 700
billion bytes: Here are the data block buffer parameters for each
RAC node:

Single table hash cluster –
The benchmark used single-table hash clusters to speed access to
specific rows, bypassing index access with faster hash access to
rows. Their hash cluster used of the RECYCLE pool because
single-table hash cluster access is random by nature and another
task is unlikely to need the block in the buffer.

Conclusions

There are some important lessons in
these benchmarks for the Oracle professional who wants to hypercharge
their application:

Use Multiple blocksizes -
Using multiple blocksizes allows you to segregate data blocks in the
SGA data buffer cache. Multiple blocksizes are also beneficial
for improving the speed of sequential access tablespaces (indexes
and temp tablespace) by using the db_32k_cache_size.

Use large data buffers -
Both of these benchmarks had over 100 gigabytes of data buffer cache
(db_cache_size, db_keep_cache_size, db_32k_cache_size),
Caching of data can improve the rate of logical I/O to physical disk
I/O's and experts say that logical I/O is 20x to 200x faster than
disk access.

Use hash clusters - Oracle
hash cluster tables can improve random row access speed by up to 4x
because the hash can get the row location far faster than index
access. Also multiple table hash clusters can store
logically-related rows on a single data block, allowing you to
access a whole unit of data in a single physical I/O.

You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.

��

Burleson is the American Team

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail:
and include the URL for the page.