Instance Tuning from Performance Benchmarks
A close look at the benchmark methodology leads to the
conclusion that blistering transaction speed cannot be
attributed solely to high-speed hardware platforms. In order to
appreciate the nature of these benchmarks, a closer look at how
the Oracle professionals designed the database to accommodate
high-speed data retrieval is needed.
Oracle 10g Windows Benchmark
The benchmark where UNISYS set the world record for
price-performance, achieving over a quarter of a million
transactions per minute using Oracle10g on Windows has already
been mentioned in this text. 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. The techniques used by the
Oracle DBA in this benchmark included:
Oracle Multiple blocksizes
115 gigabyte total SGA data buffer cache (db_cache_size
78 gigabyte KEEP pool (db_keep_cache_size
HP Linux Benchmark
This world record benchmark used a $6,000,000 HP server with
64-Intel Itanium2 processors and 768 gigabytes of RAM and
achieved over one million transactions per minute.
This voluminous benchmark disclosure report numbered 206 pages
and offered some interesting clues into the way that the Oracle
DBA configured Oracle10g for this world record benchmark:
The benchmark used 16 Oracle instances, each
mapping to four processors.
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.
The benchmark DBA employed several Oracle
hidden parameters to boost performance. Like most vendors, they
take advantage of hardware specific performance features:
_cursor_cache_frame_bind_memory = true
_db_cache_pre_warm = false
_check_block_after_checksum = false
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. The following
are the data block buffer parameters for each RAC node:
db_cache_size E "db_cache_size" = 4000M
db_recycle_cache_size E "db_recycle_cache_size"
db_8k_cache_size = 200M
db_16k_cache_size E "db_16k_cache_size"
db_2k_cache_size E "db_2k_cache_size" = 35430M
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.
There are some important lessons in these benchmarks for the
Oracle professional desiring to hypercharge their application:
Using multiple blocksizes allows the DBA 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
Both of these benchmarks had over 100 gigabytes
of data buffer cache (db_cache_size,
). Caching of data
can improve the rate of logical I/O to physical disk I/Os and
experts say that logical I/O is 20 to 200 times faster than disk
Oracle hash cluster tables can improve random row access speed
by up to four times 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 the DBA to access a whole unit of data in a single
The importance of instance configuration should be clear.
The proper settings for the Oracle instance parameters are the
single most important aspect of Oracle tuning. Oracle
parameters control the sizes of the SGA regions, the SQL
optimization process, and the ability of Oracle to invoke
automatic tuning activities such as cursor sharing, automatic
query rewrite and automatic memory management.
The main points of this chapter include:
Oracle instance tuning involves adjusting
important init.ora E "init.ora"
configuring the SGA regions and adjusting the disk I/O block
Oracle server kernel parameter, especially I/O
configuration, has a huge influence on Oracle instance
The most important Oracle parameters are those
that govern the SQL optimizer and the sizes of the SGA regions.
Oracle provides hidden instance parameters and
these can be very helpful on specific hardware. The TPC Oracle
benchmarks (www.tpc.org) should be consulted to see the
hidden Oracle parameters used by hardware vendors to see how
they are used.
Oracle has created a special KEEP pool to ensure
that frequently references data blocks stay inside the SGA. It
is the job of the DBA to identify and cache all high impact
tables and indexes in the KEEP pool.
Benchmarks show that multiple blocksizes should be
defined so that the DBA can map Oracle objects to the most
Any tablespaces whose objects experience
full-scans should be placed into a large blocksize. This
includes certain indexes, the TEMP tablespace, and data
Objects that experience high invalidations, such
as truncate table or high DML, should be segregated into a
separate data buffer, sometimes with a small size.
This chapter has been extensive, but it lays the foundation for
a look at proactive SQL Tuning which is a very complex an
important component of Oracle tuning.
SEE CODE DEPOT FOR FULL SCRIPTS