The Relationship Between the Database Administrator and the Systems Administrator Oracle Database Tips by Donald Burleson

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 invoked 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.

Figure 6-28: A sample screen from glance

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.

Using top to Monitor the Server

The top utility is used to show CPU consumption, RAM memory consumption, and the top sessions on a UNIX server. The top utility is invoked by entering top from the UNIX prompt. The output from top is displayed in three sections.

The top Load Averages

At the very beginning of the following top output we see a series of three numbers. These are the called load average metrics. The load average is an arbitrary number that describes the load on the system. The first load average value is the immediate load for the past minute. The next value represents the load average from 5 minutes in the past. The third value is the load average from 15 minutes ago. Whenever the load average rises above 1, you can assume that the processors are fully burdened and you should immediately run vmstat to check the run queue values.

L 6-1

The top CPU Summary

The first output from top shows the load on each processor and the current top sessions in terms of CPU utilization. Top gives details on each CPU on the server, and we can immediately see from the listing here that this server has six CPUs, numbered 0?5:

L 6-2

Top Sessions

Now let's look at the second section from the top command. The second section of top output details the current top sessions in terms of CPU utilization, and appears as follows:

L 6-3

In this section of the top output, we see the process ID (PID), username, the dispatching priority (PRI), the nice value (NI), the size of each task's memory (SIZE), the state, the execution time, and the percentage of CPU being used by each process.

While top has many columns of information, there are only a few columns that are of interest to you as the Oracle DBA:

• Load averages These are the load averages for the entire server. Values greater than 1 may indicate an overload problem on the server.

• CPU The first section of the top output shows a load summary for each CPU. The CPU column in the detailed listing shows which CPU is servicing each individual task.

• LOAD The LOAD column shows the load on each of the CPUs.

• NI The NI (nice) value is the dispatching priority of the task, and refers to the rate that the task receives services from the CPUs.

• IDLE This shows the percentage of time that each CPU has been idle.

In sum, the top and glance utilities are reliable reactive utilities that allow the DBA to see what is happening on the database server. Now let's take a look at a more detailed online monitor, the System Activity Reporter, or sar for short.

 

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.