Oracle IBM terabyte RAM benchmark Oracle Database Tips by Donald Burleson

This amazing new TPC benchmark for IBM Oracle is the first to use a trillion bytes of RAM.  With the advent of 64-bit processors it is now possible to fully cache large databases, eliminating disk I/O.  As an alternative to large RAM, many companies are now employing RAM disk (SSD), for nanosecond access speeds to Oracle data.  This benchmark used a IBM eServer p5 595 Model 9119-595, an 8 million dollar environment with spectacular performance. Here is the executive summary, noting that the benchmark simulated 1.2 million users!

Learn Oracle tuning from Oracle benchmarks

Benchmark vendors invest millions of dollars in tuning their Oracle databases, and benchmarks are a great way to understand detailed parameter settings to optimizer Oracle in specific environments. 

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

Features of high performance Oracle benchmarks

The salient point of this benchmark are noted in the initialization parameters where we see several important Oracle tuning tips for this type of IBM AIX system:

• Over 60 custom Oracle parameters (see below).
   
• Large RAM regions - This benchmark utilized over a terabyte of RAM.
   
• Multiple blocksizes (to segregate I/O).  Multiple blocksizes have proven to be a great approach for high performance Oracle systems, and this benchmark used a 178 gigabyte default cache, plus separate 2k, 16k, KEEP and RECYCLE pools.  This approach allows the DBA to segregate objects into separate tablespaces (according to processing requirements) and apply them to separate buffer pools:

db_cache_size = 176000M
db_2k_cache_size = 2048M
db_16k_cache_size = 99000M
db_keep_cache_size = 600000M
db_recycle_cache_size = 64000M

• Over 30 undocumented (hidden) Oracle parameters.  Especially note the setting for _optimizer_cost_model (set to io, as opposed to cpu), and the large NUMA (non-uniform memory access) setting of 500 megabytes:

 _NUMA_pool_size = 536870912
 _collect_undo_stats=false
 _awr_restrict_mode=true
 _db_writer_flush_imu=false
 _db_cache_pre_warm=FALSE
 _undo_autotune = false
 _imu_pools = 358
 _optimizer_cache_stats = false
 _optimizer_cost_model = io
 _cursor_cache_frame_bind_memory = true
 _db_writer_coalesce_area_size = 16777216
 _kghdsidx_count = 1
 _ksmg_granule_size=268435456
 _two_pass=false
 _session_idle_bit_latches=3000

• Huge shared pool - This benchmark used a 14 gigabyte shared_pool_size, showing that large shared pools can be highly performant in Oracle 10g.

Oracle benchmark references:

Here is the full set of init.ora parameters used in this amazing terabyte benchmark:

compatible = 10.1.0.0.0
_NUMA_pool_size = 536870912
_collect_undo_stats=false
_awr_restrict_mode=true
_db_writer_flush_imu=false
timed_statistics = FALSE
query_rewrite_enabled=false
db_name = tpcc
control_files = /home/oracle/db/control_001
dml_locks = 500
log_buffer = 67108864 # 1048576x cpu
parallel_max_servers = 0
parallel_min_servers = 0
db_files = 3000
fast_start_io_target = 0
db_cache_size = 176000M
db_2k_cache_size = 2048M
db_16k_cache_size = 99000M
db_keep_cache_size = 600000M
db_recycle_cache_size = 64000M
enqueue_resources = 60000
processes = 2000
sessions = 3000
transactions = 2800
shared_pool_size = 14000M
cursor_space_for_time = TRUE
db_block_size = 4096
undo_management = auto
UNDO_TABLESPACE = undo_1
_db_cache_pre_warm=FALSE
trace_enabled = FALSE
db_block_checksum = FALSE
trace_enabled = FALSE
statistics_level = basic
plsql_optimize_level = 2
pga_aggregate_target = 0
_undo_autotune = false
undo_retention = 1
_imu_pools = 358
_optimizer_cache_stats = false
_optimizer_cost_model = io
fast_start_mttr_target = 0
db_writer_processes = 16
log_checkpoint_interval = 453701520
log_checkpoints_to_alert = TRUE
log_checkpoint_timeout = 1700
java_pool_size = 0
remote_login_passwordfile = shared
disk_asynch_io = TRUE
db_block_checking = FALSE
cursor_space_for_time = TRUE
lock_sga = TRUE
replication_dependency_tracking = FALSE
db_file_multiblock_read_count = 1
_cursor_cache_frame_bind_memory = true
max_dump_file_size=5M
_db_writer_coalesce_area_size = 16777216
aq_tm_processes = 0
_kghdsidx_count = 1
_ksmg_granule_size=268435456
_two_pass=false
_session_idle_bit_latches=3000
utl_file_dir=*

UNIX (AIX) parameters for the IBM benchmark include:

SW_dist_intr false Enable SW distribution of interrupts
True
autorestart true Automatically REBOOT system after a
crash True
boottype disk N/A False
capacity_inc 1.00 Processor capacity increment
False
capped true Partition is capped
False
conslogin enable System Console Login
False
cpuguard enable CPU Guard
True
dedicated true Partition is dedicated
False
ent_capacity 32.00 Entitled processor capacity
False
frequency 528000000 System Bus Frequency
False
fullcore false Enable full CORE dump
True
fwversion IBM,SF222_081 Firmware version and revision
levels False
id_to_partition 0X80000D71EE800001 Partition ID
False
id_to_system 0X80000D71EE800000 System ID
False
iostat false Continuously maintain DISK I/O history
True
keylock normal State of system keylock at boot time
False
max_capacity 32.00 Maximum potential processor
capacity False
max_logname 9 Maximum login name length at boot
time True
maxbuf 20 Maximum number of pages in block I/O
BUFFER CACHE True
maxmbuf 0 Maximum Kbytes of real memory
allowed for MBUFS True
maxpout 0 HIGH water mark for pending write I/Os
per file True
maxuproc 20000 Maximum number of PROCESSES
allowed per user True
min_capacity 1.00 Minimum potential processor capacity
False
minpout 0 LOW water mark for pending write I/Os
per file True