Oracle8i Tuning
Release 8.1.5






Prev Next

Tuning CPU Resources

This chapter describes how to solve CPU resource problems. Topics in this chapter include:

Understanding CPU Problems

To address CPU problems, first establish appropriate expectations for the amount of CPU resources your system should be using. Then distinguish whether sufficient CPU resources are available and recognize when your system is consuming too many resources. Begin by determining the amount of CPU resources the Oracle instance utilizes in three cases, when your system is:

Workload is an important factor when evaluating your system's level of CPU utilization. During peak workload hours, 90% CPU utilization with 10% idle and waiting time may be acceptable; 30% utilization at a time of low workload may also be understandable. However, if your system shows high utilization at normal workload, there is no room for a peak workload. For example, Figure 18-1 illustrates workload over time for an application having peak periods at 10:00 AM and 2:00 PM.

Figure 18-1 Average Workload and Peak Workload

This example application has 100 users working 8 hours a day, for a total of 800 hours per day. Each user entering one transaction every 5 minutes translates into 9,600 transactions daily. Over an 8-hour period, the system must support 1,200 transactions per hour, which is an average of 20 transactions per minute. If the demand rate were constant, you could build a system to meet this average workload.

However, usage patterns are not constant--and in this context, 20 transactions per minute can be understood as merely a minimum requirement. If the peak rate you need to achieve is 120 transactions per minute, you must configure a system that can support this peak workload.

For this example, assume that at peak workload Oracle can use 90% of the CPU resource. For a period of average workload, then, Oracle use no more than about 15% of the available CPU resource as illustrated in the following equation:

20 tpm/120 tpm * 90% = 15%

Where tpm is "transactions per minute".

If the system requires 50% of the CPU resource to achieve 20 tpm, then a problem exists: the system cannot achieve 120 transactions per minute using 90% of the CPU. However, if you tuned this system so it achieves 20 tpm using only 15% of the CPU, then, assuming linear scalability, the system might achieve 120 transactions per minute using 90% of the CPU resources.

As users are added to an application, the workload can rise to what had previously been peak levels. No further CPU capacity is then available for the new peak rate, which is actually higher than the previous.

Detecting and Solving CPU Problems

If you suspect a problem with CPU usage, check two areas:

System CPU Utilization

Oracle statistics report CPU use by only Oracle sessions, whereas every process running on your system affects the available CPU resources. Effort spent tuning non-Oracle factors can thus result in improved Oracle performance.

Use operating system monitoring tools to determine what processes are running on the system as a whole. If the system is too heavily loaded, check the memory, I/O, and process management areas described later in this section.

Tools such as sar -u on many UNIX-based systems enable you to examine the level of CPU utilization on your entire system. CPU utilization in UNIX is described in statistics that show user time, system time, idle time, and time waiting for I/O. A CPU problem exists if idle time and time waiting for I/O are both close to zero (less than 5%) at a normal or low workload.

On NT, use Performance Monitor to examine CPU utilization. Performance Manager provides statistics on processor time, user time, privileged time, interrupt time, and DPC time. (NT Performance Monitor is not the same as Performance Manager, which is an Oracle Enterprise Manager tool.)


This section describes how to check system CPU utilization on most UNIX-based and NT systems. For other platforms, please refer to your operating system documentation.  

Memory Management

Check the following memory management areas:

Paging and Swapping Use tools such as sar or vmstat on UNIX or Performance Monitor on NT to investigate the cause of paging and swapping.

Oversize Page Tables On UNIX, if the processing space becomes too large, it may result in the page tables becoming too large. This is not an issue on NT.

I/O Management

Check the following I/O management issues:

Thrashing Ensure your workload fits into memory so the machine is not thrashing (swapping and paging processes in and out of memory). The operating system allocates fixed portions of time during which CPU resources are available to your process. If the process wastes a large portion of each time period in checking to be sure that it can run and to ensure all needed components are in the machine, the process may be using only 50% of the time allotted to actually perform work.

Client/Server Round Trips The latency of sending a message may result in CPU overload. An application often generates messages that need to be sent through the network over and over again, resulting in significant overhead before the message is actually sent. To alleviate this problem, batch the messages and perform the overhead only once or reduce the amount of work. For example, you can use array inserts, array fetches, and so on.

See Also:

For more details on tuning I/O, please see Chapter 20, "Tuning I/O".  

Process Management

Check the following process management issues:

Scheduling and Switching The operating system may spend excessive time scheduling and switching processes. Examine the way in which you are using the operating system because you could be using too many processes. On NT systems, do not overload your server with too many non-Oracle processes.

Context Switching Due to operating system specific characteristics, your system could be spending a lot of time in context switches. Context switching could be expensive, especially with a large SGA. Context switching is not an issue on NT which has only one process per instance; all threads share the same page table.

Programmers often create single-purpose processes, exit the process, and create a new one. Doing this re-creates and destroys the process each time. Such logic uses excessive amounts of CPU, especially with applications that have large SGAs. This is because you have to build the page tables each time. The problem is aggravated when you pin or lock shared memory, because you have to access every page.

For example, if you have a 1-gigabyte SGA, you may have page table entries for every 4K, and a page table entry may be 8 bytes. You could end up with
(1G /4K) * 8B entries. This becomes expensive, because you have to continually make sure that the page table is loaded.

Parallel execution and multi-threaded server are areas of concern if MINSERVICE has been set too low (set to 10, for example, when you need 20). For an application that is performing small lookups, this may not be wise. In this situation, it becomes inefficient for the application and for the system as well.

Oracle CPU Utilization

This section explains how to examine the processes running in Oracle. Two dynamic performance views provide information on Oracle processes:

For example, if you have 8 CPUs, then for any given minute in real time, you have 8 minutes of CPU time available. On NT and UNIX, this can be either user time or time in system mode ("privileged" mode, in NT). If your process is not running, it is waiting. CPU time utilized by all systems may thus be greater than one minute per interval.

At any given moment you know how much time Oracle has used on the system. So if 8 minutes are available and Oracle uses 4 minutes of that time, then you know that 50% of all CPU time is used by Oracle. If your process is not consuming that time, then some other process is. You then need to identify the processes that are using CPU time. If you can, determine why the processes are using so much CPU time attempt to tune them.

The major areas to check for Oracle CPU utilization are:

This section describes each area and indicates the corresponding Oracle statistics to check.

Reparsing SQL Statements

When Oracle executes a SQL statement, it parses it to determine whether the syntax and its contents are correct. This process can consume significant overhead. Once parsed, Oracle does not parse the statement again unless the parsing information is aged from the memory cache and no longer available.

Ineffective memory sharing among SQL statements can result in reparsing. Use the following procedure to determine whether reparsing is occurring:

  1. Begin by checking V$SYSSTAT to see if parsing in general is a problem:

    ('parse time cpu', 'parse time elapsed', 'parse count (hard)');


Response time  

Service time + wait time, therefore response time = elapsed time.  

Service time  

CPU time, therefore elapsed time - CPU time = wait time.  

In this way, you can detect the general response time on parsing. The more your application is parsing, the more contention exists and the more time your system spends waiting. Note the following:

Wait time/parse count  

Average wait time per parse.  


The average wait time should be extremely low, approaching zero. (V$SYSSTAT also shows the average wait time per parse.)  

  • Query V$SQLAREA to find frequently reparsed statements:


    Tune the statements with the higher numbers of parse calls.

    1. You have the following three options for tuning them:

    Inefficient SQL Statements

    Inefficient SQL statements can consume large amounts of CPU resources. To detect such statements, enter the following query. You may be able to reduce CPU usage by tuning SQL statements that have a high number of buffer gets.


    See Also:

    "Approaches to SQL Statement Tuning".  

    Read Consistency

    Your system may spend excessive time rolling back changes to blocks in order to maintain a consistent view. Consider the following scenarios:

    Use the following formula to find the number of buffers that were dirty at the end of the LRU:

    If many dirty buffers exist, it could mean that the DBWn process(es) cannot keep up. Again, increase the buffer cache size or tune the DBWn process.

    Scalability Limitations Within the Application

    In most of this CPU tuning discussion, we assume you can achieve linear scalability, but this is never actually the case. How flat or nonlinear the scalability is indicates how far away from optimal performance your system is. Problems in your application might be adversely affecting scalability. Examples of this include too many indexes, right-hand index problems, too much data in the blocks, or not properly partitioning the data. These types of contention problems waste CPU cycles and prevent the application from attaining linear scalability.

    Latch Contention

    Latch contention is a symptom of CPU problems; it is not normally a cause. To resolve it, you must locate the latch contention within your application, identify its cause, and determine which part of your application is poorly written.

    In some cases, the spin count may be set too high. It's also possible that one process may be holding a latch that another process is attempting to secure. The process attempting to secure the latch may be endlessly spinning. After a while, this process may go to sleep and later resume processing and repeat its ineffectual spinning. To resolve this:

    Solving CPU Problems by Changing System Architectures

    If you have maximized the CPU power on your system and have exhausted all means of tuning your system's CPU use, consider redesigning your system on another architecture. Moving to a different architecture might improve CPU use. This section describes architectures you might consider using, such as:

    Single Tier to Two-Tier

    Consider whether changing from several clients with one server, all running on a single machine (single tier), to a two-tier client/server configuration would relieve CPU problems.

    Figure 18-2 Single Tier to Two-Tier

    Multi-Tier: Using Smaller Client Machines

    Consider whether using smaller clients improves CPU usage rather than using multiple clients on larger machines. This strategy may be helpful with either two-tier or three-tier configurations.

    Figure 18-3 Multi-Tier Using Smaller Clients

    Two-Tier to Three-Tier: Using a Transaction Processing Monitor

    If your system runs with multiple layers, consider whether moving from a two-tier to three-tier configuration and introducing a transaction processing monitor might be a good solution.

    Figure 18-4 Two-Tier to Three-Tier

    Three-Tier: Using Multiple TP Monitors

    Consider using multiple transaction processing monitors.

    Figure 18-5 Three-Tier with Multiple TP Monitors

    Oracle Parallel Server

    Consider whether incorporating Oracle Parallel Server would solve your CPU problems.

    Figure 18-6 Oracle Parallel Server

  • Prev

    Copyright © 1999 Oracle Corporation.

    All Rights Reserved.