Oracle8 Tuning Release 8.0 A58246-01 |
Library | Product | Contents | Index

# 3
Diagnosing Performance Problems in an Existing System

This chapter provides an overview of factors affecting performance in existing systems that have been properly designed. Note, however, that tuning these factors cannot compensate for poor design!

- Tuning Factors for a Well-Designed Existing System
- Insufficient CPU
- Insufficient Memory
- Insufficient I/O
- Network Constraints
- Software Constraints

Later chapters discuss each of these factors in depth.

## Tuning Factors for a Well-Designed Existing System

The following figure illustrates the many factors involved in Oracle system performance, when considering a well-designed existing application.

Attention: Tuning these factors is effective only after you have tuned the business process and the application, as described in "Performance Tuning Method". Tuning these factors cannot make up for the performance gains that must be designed into the system.

#### Figure 3-1 Major Performance Factors in a Well-designed System

Performance problems tend to be interconnected rather than isolated. The following table provides a key to performance factors in an existing system, and the areas in which symptoms may appear. Buffer cache problems, for example, may show up as issues of CPU, memory, or I/O. Tuning the buffer cache for CPU may in turn improve I/O.

##### Table 3-1 Key to Tuning Areas for Existing Systems

ORACLE TUNING AREAS | LIMITING RESOURCES
--- | ---
| CPU | Memory | I/O | Network | Software
Application
Design/Architecture | X | X | X | X | X
DML SQL | X | X | X | X | X
Query SQL | X | X | X | X | X
Client/Server Roundtrips | X |  |  | X |
Instance
Buffer Cache | X | X | X |  |
Shared Pool | X | X |  |  |
Sort Area | X | X | X |  |
Physical Structure of Data/DB File I/O | X |  | X |  |
Log File I/O |  | X | X |  |
Archiver I/O | X |  | X |  |
Rollback Segments |  |  | X |  | X
Locking | X | X | X |  | X
Backups | X |  | X | X | X
Operating System
Memory Management | X | X | X |  |
I/O Management | X | X | X |  |
Process Management | X | X |  |  |
Network Management |  | X |  | X |

## Insufficient CPU

In a CPU-bound system, the CPU resource is completely used up, service time is too high and you want to achieve more. Alternatively, you have too much idle time, want to achieve more, and the CPU is not completely used up. There is room to do more: you need to determine why so much time is spent waiting.

To diagnose insufficient CPU, you must check CPU utilization by your entire system, not only utilization by Oracle Server processes. At the beginning of a workday, for example, the mail system may consume a very large amount of the available CPU while employees check their messages. Later in the day, the mail system may be much less heavily used, and its CPU utilization will drop accordingly.

Workload is a very 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 understandable and 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 not room for peak workload. You have a CPU problem if idle time and time waiting for I/O are both close to zero (less than 5%) at a normal or low workload.

See Also: Chapter 13, "Tuning CPU Resources"

## Insufficient Memory

Sometimes a memory problem may be detected as an I/O problem. There are two kinds of memory requirements: Oracle and system. Oracle memory requirements affect the system requirements. Memory problems may be the cause of all the paging and swapping that goes on in the machine. Make sure that your system does not start swapping and paging. The whole system should be able to run within the limitations set by internal memory.

System memory requirements for non-Oracle processes plus Oracle memory requirements should be equal to or less than internal memory. To achieve this, you can trim some elements of the Oracle requirements, most likely buffer cache, shared pool, or redo log buffer. On the system level you can trim the number of processes and/or the amount of memory each process uses. You can also identify which processes are using the most memory. One way to reduce memory usage might be by sharing SQL.

See Also: Chapter 14, "Tuning Memory Allocation"

## Insufficient I/O

Be sure to distribute I/O evenly across disks and channels. I/O resources involve:

- Channel bandwidth: number of I/O channels
- Device bandwidth: number of disks
- Device latency: latency will be part of your wait time

I/O problems may result from limitations of your hardware configuration. Your system needs enough disks and SCSI busses to support the transaction throughput you desire. You can evaluate the configuration by figuring the number of messages all your disks and busses can potentially support, and comparing that to the number of messages required by your peak workload.

If the response time of an I/O becomes too high, the most common problem is that wait time has gone up (response time = service time + wait time). If wait time goes up, it means that there are too many I/O requests for this device. If service time goes up, this normally means that the I/O requests are larger, so you write more bytes.

The different background processes (DBWR, ARCH, and so on) perform different kinds of I/O, and each process has different I/O characteristics. Some read and write in the block size of the database, some read and write in larger chunks. If service time is too high, stripe the file over different devices.

Mirroring can also be a cause of I/O bottlenecks unless the data is mirrored on the same number of disks as the database itself.

See Also: Chapter 15, "Tuning I/O"

## Network Constraints

Network constraints are similar to I/O constraints. You need to consider:

- Network bandwidth: Each transaction requires that a certain number of packets be sent over the network. If you know the number of packets required for one transaction, you can compare that to the bandwidth to determine whether your system is capable of supporting the desired workload.
- Message rates: You can reduce the number of packets on the network by batching them rather than sending lots of small packets.
- Transmission time

As the number of users and the demand rises, the network can sometimes quietly become the bottleneck in an application. You may be spending a lot of time waiting for network availability. Use available operating system tools to see how busy your network is.

See Also: Chapter 16, "Tuning Networks"

## Software Constraints

Operating system software determines:

- The maximum number of processes you can support
- The maximum number of processes you can connect

Before you can tune Oracle effectively, you must ensure that the operating system is at its peak performance. Work closely with the hardware/software system administrators to ensure that Oracle is allocated the proper operating system resources.

Note: On NT systems there are no pre-set or configurable maximum numbers of processes that can be supported or connected.

See Also: Operating system tuning is different for every platform. Refer to your operating system hardware/software documentation as well as your Oracle operating system-specific documentation for more information.
In addition, see Chapter 17, "Tuning the Operating System".

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index