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 , db_32k_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:

§       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: The benchmark DBA employed several Oracle hidden parameters to boost performance. Like most vendors, they take advantage of hardware specific performance features:

_in_memory_undo=false

_cursor_cache_frame_bind_memory = true

_db_cache_pre_warm = false

_in_memory_undo=false

_check_block_after_checksum = false

_lm_file_affinity

§       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.  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"  = 500M

db_8k_cache_size = 200M

db_16k_cache_size E "db_16k_cache_size"  = 4056M

db_2k_cache_size E "db_2k_cache_size"  = 35430M

§       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.

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

§       Multiple blocksizes: 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 parameter.

§       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/Os and experts say that logical I/O is 20 to 200 times faster than disk access.

§       Hash clusters: 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 physical I/O.

The importance of instance configuration should be clear. 

Conclusion

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"  parameters, configuring the SGA regions and adjusting the disk I/O block sizes.

§       Oracle server kernel parameter, especially I/O configuration, has a huge influence on Oracle instance performance.

§       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 beneficial blocksize.

§       Any tablespaces whose objects experience full-scans should be placed into a large blocksize.  This includes certain indexes, the TEMP tablespace, and data warehouse tables.

§       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

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm