16
Diagnosing System Performance Problems

This chapter provides an overview of factors affecting performance in properly designed systems. Following the guidelines in this chapter cannot, however, compensate for poor design!

This chapter contains the following sections:

• Tuning Factors for Well Designed Existing Systems

• Insufficient CPU

• Insufficient Memory

• I/O Constraints

• Network Constraints

• Software Constraints

  Note: Later chapters discuss each of these factors in depth.

Tuning Factors for Well Designed Existing Systems

Figure 16-1 illustrates the factors involved in Oracle system performance for well designed applications.

Note: Tuning these factors is effective only after you have tuned the business process and the application, as described in Chapter 2, "Performance Tuning Methods".

Figure 16-1 Major Performance Factors in Well Designed Systems

Performance problems tend to be interconnected rather than isolated and unrelated. Table 16-1 identifies the key performance factors in existing systems as well as the areas in which symptoms may appear. For example, buffer cache problems may show up as CPU, memory, or I/O problems. Therefore, tuning the buffer cache CPU may improve I/O.

Table 16-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, CPU resources might be completely allocated, and service time could be excessive too. In this situation, you must improve your system's processing ability. Alternatively, you could have too much idle time, and the CPU might not be completely used up. In either case, you need to determine why so much time is spent waiting.

To determine why there is insufficient CPU, identify how your entire system is using CPU. Do not rely on identifying how CPU is used by Oracle server processes. At the beginning of a workday, for example, the mail system may consume a large amount of available CPU while employees check their messages. Later in the day, the mail system may be much less of a bottleneck, and its CPU use drops accordingly.

Workload is a very important factor when evaluating your system's level of CPU use. During peak workload hours, 90% CPU use 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 workloads, then there is no more room for a "peak workload". You have a CPU problem if idle time and time waiting for I/O are both close to zero, or less than 5%, at a normal or low workload.

See Also: For more information on CPU utilization, see Chapter 18, "Tuning CPU Resources".

Insufficient Memory

Sometimes a memory problem may be detected as an I/O problem. There are two types of memory requirements: Oracle and system. Oracle memory requirements affect the system requirements. Memory problems may be the cause of paging and swapping that occurs in the machine. So, make sure your system does not start paging and swapping. The system should be able to run within the limitations set by real memory.

System memory requirements for non-Oracle processes plus Oracle memory requirements should be equal to, or less than, the total available real memory. To achieve this, reduce the size of some of the Oracle memory structures, such as the buffer cache, shared pool, or the redo log buffer. On the system level, you can reduce 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 use is by sharing SQL.

See Also: For more information on memory, see Chapter 19, "Tuning Memory Allocation".

I/O Constraints

Be sure to distribute I/O evenly across disks and channels. I/O constraints include:

• Channel bandwidth: the number of I/O channels.

• Device bandwidth: the number of disks.

• Device latency: the time elapsed from the initiation of a request to the receipt of the request; latency is part of the "wait time".

I/O problems may result from hardware limitations. Your system needs enough disks and SCSI busses to support the transaction throughput you need. You can evaluate the configuration by calculating the quantity 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 excessive, the most common problem is that wait time has increased (response time = service time + wait time). If wait time increases, then there are too many I/O requests for this device. If service time increases, then the I/O requests are larger, so you write more bytes to disk.

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

Mirroring can also be a cause of I/O bottlenecks, unless the data is mirrored to a destination database that has the same number of disks as the source database.

See Also: For more information on I/O bottlenecks, see Chapter 20, "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, then 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 many small packets.

• Transmission time.

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

See Also: For more information, see Chapter 22, "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 should confirm that the operating system is performing optimally. Work closely with the hardware and 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. See your operating system documentation, as well as your Oracle operating system-specific documentation for more information. In addition, see Chapter 23, "Tuning the Operating System".