Remote DBA Plans
Remote DBA Service

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 each task.

CPU shortages 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:

1. 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.

2. 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.

3. 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 emergency situations.

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.