Oracle reactive monitoring tools Oracle Tips by Burleson Consulting

 

IBM has published a fascinating benchmark using a 32-CPU processor server with a full terabyte of RAM: Oracle IBM terabyte RAM benchmark. This benchmark achieved over 1.6 million transactions per minute (over 25,000 transactions per second) using a $8.4 million dollar server environment.   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   The most interesting part is the knowledge that hardware vendors spend a fortune making their benchmarks as fast as possible.  Reading these benchmark documents are a great way to get Oracle tuning tips and techniques.  This benchmark has the following characteristics:   1 ? Used multiple blocksizes (4k, 8k and 16k block sizes) 2 ? Used over 500 billions bytes for the data buffer cache 3 ? Used the Oracle KEEP pool (600 gigabytes) 4 ? Used hidden parameters 5 ? Turned-off CPU costing for the SQL optimizer (_optimizer_cost_model=io and _optimizer_cache_stats=0)   Large RAM regions - Foremost in this benchmark we see the use of very-large RAM memory regions for Oracle to cache the most frequently-used data, plus a 14 billion byte shared pool:   db_cache_size = 176000M db_2k_cache_size = 2048M db_16k_cache_size = 99000M db_keep_cache_size = 600000M db_recycle_cache_size = 64000M shared_pool_size = 14000M   It?s also interesting that the benchmark uses a over 500 megabytes NUMA pool (non-uniform memory access) to leverage the super-fast T2 RAM that is localized near each CPU:   _NUMA_pool_size = 536870912   Undocumented parameters - We also see numerous undocumented Oracle parameters.  The Oracle10g database has loads of undocumented parameters and only the super-experts dare to use them because they are unsupported and risky.  However, when the stakes are high (selling multi-million dollar servers), they can be very useful:   _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   Reading the TPC benchmarks at www.tpc.org is one of the best ways to see secrets for top-performance for your specific hardware and Oracle version.  Of course, make sure that you fully-understand the techniques (especially the undocumented parms) and that you thoroughly test all changes before trying them in a production environment.