Your Oracle cpu_count is important

Let’s examine the role of the cpu_count on the performance of your Oracle database.  As we probably know, Oracle parallel query is heavily influenced by the number of processors, and full-table scans can be hugely improved by running them in parallel, normally by using N-1 parallel processes (where N=the number of CPUs on your dedicated Oracle server).

Here are some of the parameters that Oracle sets at install time, based upon your cpu_count:

• fast_start_parallel_rollback

• db_block_lru_latches

• parallel_max_servers

• log_buffer

Let’s take a close look at how the number of CPUs influences these parameters.

fast_start_parallel_rollback

This parameter governs the degree of parallelism (DOP) for the recovery of parallel DML or parallel DDL when you have a system crash.  When Oracle crashes (e.g. due to a power failure), Oracle detects “in-flight” updates during the warmstart phase of re-starting the Oracle instance.

Some long-running DML statements may run for hours, and take a long-time to recover at startup time.  Parallel DML recovery will dramatically speed-up the time required to re-start your Oracle database after an instance crash. The default value is 2 times the number of CPU of your system, but some DBAs recommend setting this value to 4 times the cpu_count.

db_block_lru_latches (undocumented starting in Oracle9i)

A too-small value for db_block_lru_latches normally manifests itself with a high LRU latch contention waits in your STATSPACK report. The default value is one-half the cpu_count on your dedicated server.  Oracle recommends that db_block_lru_latches never exceed (cpu_count * 2 * 3) or (db_block_buffers / 50), whichever is higher.

There is a problem with this computation whenever you have multiple buffer pools (KEEP, RECYCLE) because you cannot govern the number of latches assigned to each data buffer pool.  This default value for this may be too small if your db_writers is greater than 1.  Many savvy Oracle9i DBAs running multiple data buffers (e.g. db_32k_cache_size) recommend re-setting this undocumented parameter to the recommended maximum value.

parallel_max_servers

This parameter controls the maximum number of OPQ factotum processes (p000, p001) that are spawned during a parallel query when parallel_automatic_tuning is enabled. As we know, Oracle parallel query is most commonly used in data warehouse applications where there are lot’s of legitimate large-table full-table scans, and the DBA has set parallel_automatic_tuning to allow Oracle to determine the best degree of parallelism for a query.  The default value for parallel_max_servers is the number of CPUs on your server.  If you are running multiple instances on the same server, this default may be too high, in which case you will see excessive server paging and high CPU utilization. The degree of parallelism is also dependent upon the number of partitions in the target table, so parallel_max_servers should be set high enough to allow Oracle to choose the best number of parallel query slaves for each query.

log_buffer

Oracle documentation recommends that the maximum size for the log_buffer be either 500k, or (128K * cpu_count) whichever is greater.  The number of CPUs is important to the value of log_buffer, because multiple log writer (LGWR) processes may be spawned by Oracle to asynchronously offload the redo information.

Even though Oracle does not recommend a log_buffer greater than one meg, I have seen numerous shops where increasing log_buffer beyond one meg greatly improved throughput and relieved undo contention.

The log_buffer is one of the most misunderstood of the Oracle RAM region parameters, and there are several common mistakes:

• The log_buffer has been set too high (greater than 20 meg), causing performance problems because the writes will be performed synchronously because of the large size (log sync wait events are high).
   

• The log_buffer is not a multiple of the db_block_size.  In Oracle9i with multiple block sizes, the log_buffer should be a multiple of 2048 bytes.

In sum, Oracle is becoming more aware of it’s server environment, and it is the job of the savvy Oracle DBA to properly monitor and adjust these parameters to ensure optimal CPU usage and fast response time.  These derived Oracle parameters are even more important as Oracle customers undertake server consolidation and move dozens of databases onto giant servers with 32 or 64 CPUs.

Many related topics are covered in my upcoming new book Oracle Tuning: The Definitive Reference by Rampant TechPress.  This book will be printed in just a few weeks, and is available at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm