Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle benchmark multiple blocksize hash cluster table and large db_cache_size

Oracle Tips by Burleson Consulting


2007 Update: For the latest consensus on using multiple blocksizes in Oracle, see
The latest consensus on multiple blocksizes.


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. 

http://www.tpc.org/results/FDR/TPCC/unisys_es7000-420_291K_040209_fdr.pdf

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 gig or RAM and achieved over one million transactions per minute. 

http://www.tpc.org/results/FDR/TPCC/HP%20Integrity%20rx5670%20Cluster%2064P_FDR.pdf 

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:

_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: Here are the data block buffer parameters for each RAC node:

db_cache_size = 4000M
db_recycle_cache_size = 500M
db_8k_cache_size = 200M
db_16k_cache_size = 4056M
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.

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.

Oracle benchmark references:

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

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


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

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.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.