Oracle High-Performance Tuning with STATSPACK

Chapter 6: Tuning the Server Environment

It should now be clear that the tuning of the server environment is a prerequisite to tuning the Oracle database. As we repeatedly demonstrated in earlier chapters and in our discussion in Chapter 5, no amount of Oracle tuning is going to help a performance problem if the CPU or lit memory on the server is experiencing an overload or a bottleneck.

When tuning an Oracle database server, we must always remember the goal of fully loading the CPUs and RAM on the server. Unused processing and RAM power can never be reclaimed, and with the significant depreciation of the value of most servers, maximizing the utilization is a noble goal. On any server that is dedicated to an Oracle database, we want to dedicate as much hardware resources to Oracle as possible without causing a server-related slowdown. This chapter will look at the details of Oracle database server tuning and will be divided into several sections:

• Online server monitor tools This will look at ancillaries to vmstat and will show the top and sar utilities to see details about CPU and memory activity.
• Tuning CPU consumption This will describe the basic causes of CPU overload and explore remedies for excessive CPU consumption. When you experience a shortage of CPU, you will need to add more processors or reduce the amount of work on the database server.
• Tuning memory consumption This section will explore basic memory management and show some tricks for partitioning memory on the server. When an Oracle server runs short of real memory, segments of RAM are swapped out to a swap disk. Such page-out operations happen frequently, but a page-in indicates that the Oracle server is exceeding the amount of RAM memory. The usual remedies for swapping are to reduce the size of the SGA and/or to buy more RAM for the database server.
• Reporting on server statistics This section will look at some handy scripts that will alert you to server exceptions and show you how to create trend and usage reports for your server.

Let's begin with a review of some online tools for server monitoring. We already, covered vmstat in the last chapter, but we also need to understand the glance, top, and sar utilities.

The Relationship Between the Database Administrator and the Systems Administrator

In many Oracle shops, computer professionals called systems administrators are responsible for the setup, configuration, and tuning of the Oracle database server. The abilities of system administrators vary widely, from excellent support and cooperation to neophyte support and complete noncooperation.

Because of the tight coupling between database performance and server performance, it is imperative that the Oracle DBA have access to the tools that we describe here. By themselves, the systems administrators will not have enough information about what is happening inside the database to properly tune the server. Conversely, the DBAs cannot get the information they need to properly configure Oracle if they cannot get access to the server monitor utilities.

Many Oracle shops give root access to the DBA so they will have full control over their database server. The system administrator continues to be responsible for the configuration and system software on the server, but the DBA accepts responsibility for setting the kernel parameters and managing the interface layer between Oracle and the operating system environment.

A small minority of shops will restrict access to the root account, but these shops will provide the Oracle DBA with access to all of the server monitor tools and system utilities. In either case, it is imperative that the Oracle DBA have access to the system monitor tools.

Online Server Monitor Tools

Before we address the specific tuning techniques for your database server, let's begin with a brief tour of several online tools that can help us tune the server. There are many companies that provide third-party server performance monitors, but there are several "freeware" server monitors that can help you see what is happening on the server.

Using glance

The glance utility is provided on HP/UX systems to provide a graphical display Of server performance. Glance displays current CPU, memory, and disk and swap consumption, and also reports on the top processes. The glance utility is involved by entering glance from the UNIX prompt. The name for glance is quite appropriate because it gives you a complete glance at the whole server. Figure 6-1 shows an example glance screen.

The histograms at the top of the glance screen show the amount of consumption on CPU, disk, memory, and swap. The output within each histogram shows the high-water mark, and the amount of the resource in system and user mode. For example, from Figure 6-1 we see that this server is only using 4 percent of the CPU capacity, but the RAM memory is at 90-percent capacity.

The second part of the glance utility shows the most intensive tasks on the database server. The glance screen in Figure 6-1 was for an Oracle server running Oracle Applications, and we see that the Oracle Forms 4.5 runtime task (f45runw) is the most intensive task on the server. For each task displayed, we also can see the amount of disk I/O currently being consumed by the task. The glance screen will refresh every few seconds, so we get a continual picture of the load on the Oracle server. To exit glance, you enter CTRL-C. Now let's look at another server monitor tool that is quite similar to glance....