Learn Oracle data warehouse tuning from the world records

Vendors spend millions of dollars tuning their benchmarks, and the TPC disclosure report are a goldmine of Oracle tuning tips. You can learn a lot about Oracle tuning by reading the TPC benchmark full disclosure reports.

The previous world record for Oracle TPC-H (data warehouse) benchmark was 1,500 QphH (query per hour), and this record has now been broken with an HP Superdome at 1,700 QphH. This benchmark used:

• large data blocks - 32k blocksize

• Huge PGA - 50g PGA region size (pga_aggregate_target)

• Huge SGA - 90g SGA target (sga_target)

But the most recent world record is even more impressive, with this world record TPC-H Oracle data warehouse benchmark report.

• Tiny disks - Smaller disk give higher bandwidth and this benchmark used tiny 32 gig disks.
  

• Huge RAM buffers - This benchmark used a 30 gig data buffer and a 150 gig pga_aggregate_target.

Let's take a closer look that the Oracle tuning tips hidden inside this benchmark.

The Oracle server

This benchmark noted 170k data warehouse transactions per hour, using an HP superdome (with a 3 year server cost of $6.6m), using an astounding 512 gigabytes of RAM:

• Processors = 64 CPU's, each 64-bit (Intel Itanium2)
  

• Database size = 10,000 gig - made-up of "tiny" 32 gig platters
  

• Server RAM = 512 gig - Yes, that's half a trillion bytes of RAM

Tuning tricks for Oracle data warehouse configuration

Here are the "tricks" that Oracle used to get super-fast performance in this data warehouse test, a lesson for anyone who tunes a data warehouse environment:

• Tiny disks - Each disk was only 32 gigabytes, and each disk was not full. This provides higher bandwidth. Read my notes on the plague of super-large disks for details.
  

• RAID 10 - This benchmark used Oracle ASM with SAME (stripe and mirror everywhere, a.k.a. RAID1+0).
  

• Giant RAM regions - Remember, disks are now 50 years-old, and there is a limited to this 1950's technology of magnetic-coated platters. This benchmark used a 30 gigabyte data buffer (db_cache_size) and pga_aggregate_target = 150g. This benchmark also chose a giant shared pool with shared_pool_size = 50g.
  

• Large data blocks - Numerous proofs have shown that Oracle indexes build cleaner tree structures in 32k blocks, plus a single I/O results in more data being delivered into the SGA/PGA.
  

• Server-specific parameters - There are special parameters for most server types that can dramatically help performance. In this case, the test used the Oracle parameter hpux_sched_noage and the HP-UX parameter ASYNC_BUF_CONF.
  

• Huge log buffer - Oracle used to recommend keeping the log_buffer small (under 10 meg), but our experience is that a larger log_buffer can increase throughput for high DML transactions. This benchmark used log_buffer=268,435,456, about 250 megabytes!
  

• Adjust Oracle optimizer parameters - Adjusting optimizer_index_cost_adj has a profound system-wide influence on the cost-based SQL optimizer, and most OLTP system use a smaller value (10-50) for influence higher index usage, and data warehouses use a larger value (100-300) to "prefer" full-table-scans. This benchmark used optimizer_index_cost_adj = 200. I have a whole chapter dedicated to holistic SQL tuning in my book "Oracle Tuning: The Definitive Reference".
  

• High Query Parallelism - Server with multiple CPU's can greatly increase the speed of full-table scans with Oracle parallel query. This benchmark used these parallel parms:

parallel_adaptive_multi_user = true
parallel_execution_message_size = 65535
parallel_max_servers = 1600
parallel_min_servers = 1600
parallel_threads_per_cpu = 3

Here are the Oracle initialization parameters:

Here are the parms for the ASM instance: