IN THE PREVIOUS chapter, you learned how to tune an application by writing efficient SQL in order to maximize its performance. The use of optimal SQL, efficient design of the layout of the database objects, and so on are all part of a planned or proactive tuning effort. This chapter focuses on the efficient use of the resources Oracle works with: memory, CPU, and storage disks.

The chapter discusses how to monitor and optimize memory allocation for the Oracle instance. In this context, you'll learn about the traditional database hit ratios, such as the buffer cache hit ratios. However, focusing on the hit ratios isn't the smartest way to maintain efficient Oracle databases, as you need to focus on the user's response time. Investigating factors that are causing processes to spend excessive time waiting for resources is a better approach to performance tuning. This chapter provides you with a solid introduction to Oracle wait events and tells you how to interpret them and reduce the incidence of these wait events in your system.

A fairly common problem in many production systems is that of a database hang, when things seem to come to a standstill for some reason. The chapter explores several potential showstoppers and shows you ways to avoid the occurrence of database hangs.

To tune the instance, you need to collect the relevant instance statistics. In this chapter you'll learn how to use the useful Oracle Statspack to collect performance data. The chapter explains the key dynamic performance tables that you need to be familiar with to understand instance performance issues. You'll also see how the OEM Diagnostics Pack can help you monitor and tune instance performance. Although it's nice to be able to proactively design a system for high performance, more often than not, the DBA has to deal with proactive tuning when performance is unsatisfactory and a fix needs to be found right away. The final part of this chapter deals with a simple methodology to follow when your system performance deteriorates and you need to fine-tune the Oracle instance.

I begin this chapter with a short introduction to instance tuning and then turn to cover in detail the tuning of crucial resources such as memory, disk, and CPU usage. Later on in the chapter, I review the important Oracle wait events, which will help you get a handle on several kinds of database performance issues.

An Introduction to Instance Tuning

Oracle doesn't give anything but minimal and casual advice regarding the appropriate settings of key resources such as total memory allocation or the sizes of the components of memory. Oracle has some general guidelines about the correct settings for several key initialization parameters that have a bearing on performance. However, beyond specifying wide ranges for the parameters, the company's guidelines aren't very helpful to DBAs deciding on the "optimal" levels for these parameters.

Oracle says this is because all these parameters are heavily application dependent. All of this means that you as a DBA have to find out what the optimal sizes of resource allocations and the ideal settings of key initialization parameters are, through trial and error. You have to be resourceful when it comes to memory sizing: If you need more shared pool memory, first see if you can drop the buffer cache some, and vice versa. In this chapter, I provide some guidelines for figuring out the optimal sizes of memory and other configuration parameters.

As a DBA, you're often called in to tune the instance when users perceive a slow response, which is in turn caused by a bottleneck somewhere in the system. This bottleneck is the result of either an excessive use of or insufficient provision of one of the resources such as memory, CPU, or disks. In addition, database locks and latches may cause a slowdown. You have to remember, though, that in most cases, the solution isn't to simply increase the resource that seems to be getting hit hard-that may be the symptom, not the cause of a problem. If you address the performance slowdown by "fixing" the symptoms, the root causes will remain potential troublemakers.

Performance tuning an Oracle database instance involves tuning memory and I/O, as well as operating system resources such as CPU, the operating system kernel, and the operating system memory allocation. In the previous chapter, you saw how tuning the application leads to faster response times and more throughput. Application code changes, however, are incremental, and once the application is in production, instance tuning takes center stage in the tuning efforts.

When you receive calls from the help desk or other users of the system complaining that the system is running slowly, you can only change what is under your direct control-mainly, the allocation of memory and its components, and some dynamic initialization parameters that have a bearing on instance performance. Depending on what the various indicators tell you, you may adjust the shared pool and other components of memory to improve performance. You can also change the operating system priority of some processes, or quickly add some disk drives to your system.

One of the main reasons for a slow response time in a production system is the waiting time incurred by user processes. Oracle provides several ways of monitoring these waits, but you need to understand their significance in your system. Long wait times are not the problem themselves; they're symptoms of deep-seated problems. The DBA should be able to connect different types of waits with possible causes in the application or in the instance.

Although some manuals will tell you that you should do performance tuning before application tuning-before you proceed to tuning areas such as memory, I/O, and contention-real life isn't so orderly. Most of the time, you don't have the opportunity to have the code revised, even if there are indications that it isn't optimal. Instead of being an orderly process, tuning databases is an iterative process, where you may have to go back and forth between stages.

More often than not, DBAs are forced to do what they can to fix the performanceproblem that is besetting them at that moment. In this sense, most performance tuning is a reactive kind of tuning. Nevertheless, DBAs should endeavor to understand the innards of wait issues and seek to be proactive in their outlooks.

There are two big advantages to being in a proactive mode of tuning. First, you have fewer sudden performance problems that force hurried reactions. Second, as your understanding of your system increases, so does your familiarity with the various indicators of poor performance and the likely causes for them, so you can resolve problems that do occur much faster.

If you're fortunate enough to be with an application during its design stages, you can improve performance by performing several steps, including choosing automatic space management and setting correct storage options such as PCTFREE for your tables and indexes. Sizing the table and indexes correctly doesn't hurt, either. If you're stuck with a database that has a poor design, however, all is not lost. You can still tune the instance using techniques that I show later in this chapter to improve performance.

When response time is slower than usual, or when throughput falls, you'll notice that the Oracle instance isn't performing at its usual level. If response times are higher, obviously there's a problem somewhere in one of the critical resources Oracle uses. If you can rule out any network slowdowns, that leaves you with memory (Oracle's memory and the system's memory), the I/O system, and CPUs. One of these resources is usually the bottleneck that's slowing down your system. In the next few sections, you'll learn how to tune key system resources such as memory and CPU to improve performance. You'll also see how to measure performance, detect inefficient waits in the system, and resolve various types of contention in an Oracle database. The next section presents a discussion of how tuning Oracle's memory can help improve database performance.