世界上最快的Oracle数据仓库的一些性能配置信息

最新推荐文章于 2021-04-09 13:13:53 发布

cuihan5409

最新推荐文章于 2021-04-09 13:13:53 发布

阅读量154

点赞数

Learn Oracle data warehouse tuning from the world records

[@more@]

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:

instance_type = rdbms

aq_tm_processes = 0

audit_trail = FALSE

compatible = 10.1.0.2

control_files = (+DG1/control1,+DG1/control2)

cpu_count = 64

db_block_checksum = false

db_block_size = 32768

db_cache_size = 30g

db_file_multiblock_read_count = 64

db_files = 2400

db_name = 10tb

db_writer_processes = 16

dml_locks = 40000

global_names = FALSE

hpux_sched_noage = 180

instance_name = tpch

job_queue_processes = 0

log_buffer = 268435456

log_checkpoints_to_alert =

Here are the parms for the ASM instance:

instance_type=asm

shared_pool_size=4G

db_cache_size=2g

asm_diskgroups=DG1

ASM.instance_number=1

instance_number=1

processes=500

ASM_DISKSTRING='/dbms/links/roradsk*'

lock_sga=TRUE

background_dump_dest='/opt/app/admin/ASM/log'

core_dump_dest='/opt/app/admin/ASM/log'

user_dump_dest='/opt/app/admin/ASM/log'