Oracle Server Monitoring Utilities

Oracle Tips by Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.

Server Monitoring Utilities

There are many utilities available for monitoring the Oracle server, both supplied by Oracle and supplied by the operating system. The Oracle DBA should make herself very familiar with each of these utilities and understand how to use various utilities for managing and optimizing the Oracle database.

Using vmstat

Before Oracle incorporated server-side statistics into STATSPACK, AWR and ASH, the most common way of monitoring Oracle server-side data was with the UNIX/Linux utilities such as vmstat, top, and glance, and sar.

In every case, the goal is to ensure that the database server has enough disk, CPU and RAM resources at all times in order to service the Oracle requests in an adequate time frame.

Server load typically changes radically over time. It is not at all unusual for an Oracle server to be CPU-boundin the morning, network-bound in the afternoon, and I/O-bound overnight.

The job of the DBA is to identify server stress over time and learn to interpret any trends in hardware consumption. For example, Oracle10g Enterprise Managertracks server run queue waits over time and combines the CPU and paging display into a single OEM screen so that DBAs can tell when the system is experiencing server-side waits on hardware resources.

Figure 4.1 shows the CPU run queue trends over time.

Figure 4.1: Server CPU run queue and RAM paging values over time

This time-based display is important because it illustrates how Oracle performance issues can be transient with short spikes of excessive hardware consumption. Due to the super-fast nature of CPU dispatching, a database might be CPU constrained for a few minutes at a time several times per day. The time series OEM display gives a quick visual clue about those times when the system is experiencing a CPU or RAM bottleneck. It is the nature of the job of a DBA to tune the system for best performance at maximum usage, even though this maximum usage may only be reached a few times in the course of a month.

Server RAM and Oracle

Traditionally, the Oracle DBA measured RAM page-in operations to judge RAM utilization on the database server as shown in Figure 4.2. All virtual memory servers (VM) anticipate RAM shortages and asynchronously page-out RAM frames in case the RAM is required for an upcoming task.

Figure 4.2: Long-term Measurements of Oracle Server RAM Page-in Operations

When the real RAM on the server is exceeded, the OS will overlay the RAM and must then page-in the saved memory frames from the swap disk on the Oracle server. However, measuring RAM usage based solely on page-ins is a mistake because the page-ins are a normal part of program startup.

To be effective as an Oracle metric, the page-in operations (from vmstat, glance) must be correlated with the OS scan rate. When an Oracle server begins to run low on RAM, the page-stealing daemonprocess awakens and UNIX begins to treat the RAM memory as a sharable resource by moving memory frames to the swap disk with paging operations.

In most UNIX and Linux implementations, the page-stealing daemonoperates in two modes. When the real RAM capacity is exceeded, the page-stealing daemon will steal small chunks of the least recently used RAM memory from a program. If RAM resource demands continue to increase beyond the real capacity of the Oracle server, the daemon escalates and begins to page-out entire programs’ RAM regions. Unfortunately, on Linux kernel 2.6 users have no control over it. Every parameter available for tuning kernel has been taken away from the system administrators.

Because of this, it is not always clear if the page-in operations are normal housekeeping or a serious memory shortage unless the activity of the page-stealing daemonis correlated with the page-in output. Paging occurs in kernel mode. Generally speaking, if the system exhibits more then 10% of kernel mode CPU usage, for a prolonged period of time, there is a problem with paging.

To aid in measuring real page-ins, the UNIX and Linux vmstatutility yields the scan rate (sr) column which designates the memory page scan rate. If the scan rate rises steadily, the page-stealing daemon’s first threshold will be identified, indicating that that particular program’s entire RAM memory regions are being paged-out to the swap disk. This behavior can then be correlated with the vmstat page-in (pi) metric.

The following is an example from a vmstatoutput. The spike in the scan rate immediately precedes an increase in page-in operations.

oracle > vmstat 2

procs memory page

r b w avm free re at pi po fr de sr