The number of CPUs on your Oracle server has an important bearing on the
default settings for several important Oracle parameters. In Oracleand beyond, Oracle is becoming more aware of his external environment, and
Oracle adjusts the default values of Oracle parameters based upon the
characteristics of the server hardware.
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
Let?s take a close look at how the number of CPUs influences these
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
db_block_lru_latches (undocumented starting in Oracle)
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
OracleDBAs running multiple data buffers (e.g. db_32k_cache_size)
recommend re-setting this undocumented parameter to the recommended maximum
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
parallel_max_servers should be set high enough to allow Oracle to choose
the best number of parallel query slaves for each query.
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
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 Oraclewith 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: