Monitoring Server CPU Consumption
Oracle Tips by Burleson Consulting
CPU consumption on an Oracle server is a simple
matter because the server manages all CPU transactions automatically.
All servers are configured to use CPU cycles on an as-needed basis,
and the Oracle database will use CPU resources freely. The internal
machine code will manage the assignment of processors to active tasks
and ensure that the maximum amount of processing power is applied to
are evidenced in cases where the CPU run queue is greater than the
number of CPUs, as shown in Figure 6-2. In these cases, the only
solutions are to increase the number of CPUs on the processor or
reduce the CPU demands on Oracle. You can decrease CPU demands on
Oracle by turning off Oracle Parallel Query, replacing the standard
Oracle listener with the multithreaded server (MTS), and other actions
that would reduce the processing demands on the hardware.
Figure 6-29: CPU overload on an Oracle server
with 12 CPUs
Tasks are serviced in UNIX according to their
internal dispatching priority. Important tasks such as the UNIX
operating system tasks will always have a more favorable dispatching
priority because the UNIX system tasks drive the operating system
CPU overload is usually evidenced by high
values in the vmstat run queue column. Whenever the run queue value
exceeds the number of CPUs of the server, some task may be waiting for
service. When we see a CPU overload, we have several alternatives:
Add additional processors This
is usually the best solution, because
an Oracle server that is overloading the CPU will always run faster
with additional processors.
Reduce server load If
the CPU overload is not constant, task load balancing may be the
solution. For example, it is not uncommon to see a server overloaded
during peak work hours, and then return to 80 percent idle in the
evenings. In these cases, batch tasks can be rescheduled to execute
when there are more idle CPU resources available.
Alter task dispatching priorities Most
all operating systems allow the root user to change the dispatching
priority for tasks. As a general rule, the online database background
tasks are given more priority (a smaller priority value), while less
critical batch processes are placed with less priority (a higher
priority value). However, altering the default dispatching priorities
is not a good long-term solution, and it should only be undertaken in
Upgrading an Entire Server
On mission-critical databases where speed is a
primary concern, adding additional processors may not be the best
solution. Oracle tuning professionals will sometimes recommend
upgrading to a faster server architecture. For example, many of the
new 64-bit CPU processors will handle Oracle transactions an order of
magnitude faster than their 32-bit predecessors. For example, in the
IBM AIX environment, the IBM SP2 processors run on 32 bits. IBM's next
generation of processors utilize a 64-bit technology, and these
systems can process information far faster than their 32-bit
ancestors. The new IBM Regatta servers will often double the overall
processing speed of an Oracle database.
When making recommendations for upgrades of
entire servers, many Oracle tuning professionals use the analogy of
the performance of a 16-bit PC compared to the performance of 32-bit
PC. In general, moving to faster CPU architecture can greatly improve
the speed of Oracle applications, and many vendors such as IBM will
allow you to actually load your production system onto one of the new
processors for speed benchmarks prior to purchasing the new servers.
Adding Additional CPU Processors
Most symmetric multiprocessor (SMP)
architectures for Oracle databases servers are expandable, and
additional processors can be added at any time. Once added, the
processor architecture will immediately make the new CPUs available to
the Oracle database.
The problem with adding additional processors
is the high cost that can often outweigh the cost of a whole new
server. Adding additional processors to an existing server can
commonly cost over $100,000, and most managers require a detailed
cost-benefit analysis when making the decision to buy more CPUs.
Essentially, the cost-benefit analysis compares the lost productivity
of the end users (due to the response time latency) with the
additional costs of the processors.
Another problem with justifying additional
processors is the sporadic nature of CPU overloads. Oracle database
servers often experience “transient” overloads, and there will be
times when the processors are heavily burdened and other times when
the processors are not at full utilization. Before recommending a
processor upgrade, most Oracle tuning professionals will perform a
load-balancing analysis to ensure that any batch-oriented tasks are
presented to the server at nonpeak hours.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.