Oracle UNIX Administration vmstat Trend Reports Oracle UNIX/Linux Tips by Burleson Consulting

Daily vmstat Trend Reports

One of the jobs of the Oracle tuning expert is to monitor the database and the server for regular trends. This is not just an exercise in searching for trends because every database will exhibit regular patterns of CPU and memory consumption.

Using the stats$vmstat table, it is very easy to write a query that will aggregate the CPU and memory. Below is a sample SQL script that aggregates server values:

connect perfstat/perfstat;
set pages 9999;

set feedback off;
set verify off;

column my_date heading 'date' format a20
column c2      heading runq   format 999
column c3      heading pg_in  format 999
column c4      heading pg_ot  format 999
column c5      heading usr    format 999
column c6      heading sys    format 999
column c7      heading idl    format 999
column c8      heading wt     format 999

select
 to_char(start_date,'day') my_date,
-- avg(runque_waits)       c2
-- avg(page_in)            c3,
-- avg(page_out)           c4,
avg(user_cpu + system_cpu)           c5,
-- avg(system_cpu)         c6,
-- avg(idle_cpu)           c7,
avg(wait_cpu)           c8
from
   stats$vmstat
group  BY
 to_char(start_date,'day')
order by
 to_char(start_date,'day')
;

Here we can see that we can easily get any of the vmstat values aggregated by day. In the output below we see the average user and wait CPU times for each day of the week:

SQL> @rpt_vmstat_dy
Connected.

date                  usr   wt
-------------------- ---- ----
friday                  8    0
monday                 10    0
saturday                1    0
sunday                  1    0
thursday                6    0
tuesday                15    0
wednesday              11    0

This data can be extracted into MS-Excel and quickly plotted for graphical reference, as shown in Figure 3-5. Please note that my book Oracle High Performance Tuning with STATSPACK covers a method of plotting STATSPACK data in MS-Excel.

Figure 5: A daily report of vmstat metrics

Hourly vmstat Trend Reports

We can use the same techniques to average vmstat information by the hour of the day. An average by hour of the day can provide very valuable information regarding times when the server is experiencing stress:

connect perfstat/perfstat;
set pages 9999;

set feedback off;
set verify off;

column my_date heading 'date' format a20
column c2      heading runq   format 999
column c3      heading pg_in  format 999
column c4      heading pg_ot  format 999
column c5      heading cpu    format 999
column c6      heading sys    format 999
column c7      heading idl    format 999
column c8      heading wt     format 999

select
 to_char(start_date,'day') my_date,
-- avg(runque_waits)       c2
-- avg(page_in)            c3,
-- avg(page_out)           c4,
avg(user_cpu + system_cpu)           c5,
-- avg(system_cpu)         c6,
-- avg(idle_cpu)           c7,
avg(wait_cpu)           c8
from
   stats$vmstat
group  BY
 to_char(start_date,'day')
order by
 to_char(start_date,'day')
;

Here we see the output from this script and we get the average runqueue and user + system CPU values and wait CPU values, aggregated by hour of the day:

SQL> @rpt_vmstat_hr
Connected.

date                 runq  cpu   wt
-------------------- ---- ---- ----
00                      0    4    0
01                      0    5    0
02                      0    3    0
03                      0    1    0
04                      0    1    0
05                      0    1    0
06                      0    1    0
07                      0    1    0
08                      0    1    0
09                      0    1    0
10                      0    1    0
11                      0    1    0
12                      0   11    0
13                      0   21    0
14                      0   23    0
15                      0   20    0
16                      0   15    0
17                      0   20    0
18                      0   12    0
19                      0   10    0
20                      0    5    0
21                      0    1    0
22                      0    1    0
23                      0    1    0

This hourly information can also be extracted into MS-Excel for graphical plotting charts that show trends that may not be evident from a raw observation.

Long-Term Server Analysis and Trending

You can also use the data from stats$vmstat to gather information for long-term trend analysis, as shown in Figure 3-6. The nature of the vmstat tables allows the DBA to extract an ongoing average and then chart the data in MS-Excel. This Excel chart can also be enhanced to add a linear regression that can be used to predict future usage.

Figure 6: A long-term hardware resource predictive report

This long-term trend analysis is very useful for IT managers who must plan for additional server resources. For these managers, knowing the rate at which CPU and memory are being consumed on the server is critical, since there is often a lag time of several weeks between ordering and installing new hardware resources. If you want more detail on using STATSPACK information for management planning, please see my book Oracle High Performance Tuning with STATSPACK by Oracle Press.

Daily Server Alert Report

As we have repeatedly noted, the Oracle DBA is very interested in monitoring conditions on the Oracle database servers and Web servers. This script is generally run daily to report on exceptional conditions within any server in the Oracle environment. The data is collected in five-minute intervals and reported with hourly averages. When the DBAs find an out-of-bounds server condition, they can run detailed reports that display the data in five-minute intervals.

run_vmstat.ksh

This is the driver script that submits the vmstat_alert report and e-mails the output to the appropriate staff members.

vmstat_alert.sql

This report provides information on the server conditions that may contribute to poor performance.

This report gathers the following server information.

Runqueue waits

When the runqueue exceeds the number of CPUs, the server is experiencing CPU bottlenecks:

Fri Dec 29                                                             page    1
                                 run queue > 2
                         May indicate an overloaded CPU

SERVER_NAME       date       hour      runq pg_in pg_ot  usr  sys  idl      
----------------- -------------------- ---- ----- ----- ---- ---- ----      
BAD-01            00/12/22    13          6     0     0   62    7   32      
BAD-01            00/12/22    15          3     0     0   82   18    0      
BAD-01            00/12/22    17          3     0     0   76   16    8      
BAD-01            00/12/27    11          3     0     0   77    5   20      

RAM swapping

When page-in operations exist, the maximum RAM capacity of the server has been exceeded:

Fri Dec 29                                                             page    1
                                  page_in > 1
                         May indicate overloaded memory

SERVER_NAME       date       hour      runq pg_in pg_ot  usr  sys  idl      
----------------- -------------------- ---- ----- ----- ---- ---- ----      
AD-01             00/12/22    14          0    19     0    1    1   97      
AD-01             00/12/26    11          0    32     0    0    0   99      
AD-01             00/12/28    17          0     5     0    0    1   99      
JANETDB           00/12/22    13          0     3     0    1    3   96      
JANETDB           00/12/22    14          0    27     1    6   17   77      
JANETDB           00/12/22    15          0     3     0    1    3   96       JANETDB           00/12/22    16          0     7     0    3    9   88       JANETDB           00/12/22    17          0    10     0    4   10   86       JANETDB           00/12/22    18          0     2     1    1    3   96       JANETDB           00/12/23    09          0     2     0    1    3   96       JANETDB           00/12/24    03          0     4     0    1    3   96       JANETDB           00/12/26    10          0     3     0    1    3   96       JANETDB           00/12/26    11          0     2    21    8   17   75       JANETDB           00/12/26    12          0    10    10   13   27   60       JANETDB           00/12/27    09          0    10     0    1    3   96       JANETDB           00/12/27    10          0     5     0    1    3   96       JANETDB           00/12/27    11          0     6     0    1    3   95       JANETDB           00/12/28    03          0     2     0    1    3   96       JANETDB           00/12/28    11          0     2     0    1    3   95       JANETDB           00/12/28    21          0     3     1    2    4   95      

High CPU

The DBA is often interested in times when the database CPU utilization is greater than 95 percent.

Fri Dec 29                                                             page    1
                               user+system > 70%
                          Indicates an overloaded CPU

SERVER_NAME       date       hour      runq pg_in pg_ot  usr  sys  idl      
----------------- -------------------- ---- ----- ----- ---- ---- ----      
AD-01             00/12/22    08          2     0     0   69    3   28      
AD-01             00/12/22    13         12     0     0   89   11    1      
AD-01             00/12/22    15          0     0     0   63   29    8      
AD-01             00/12/22    17          1     0     0   53   27   20      
AD-01             00/12/26    12          1     0     0   77    4   19      
AD-01             00/12/27    11          3     0     0   86    6    9      

      Now let?s wrap-up this chapter with a review of the major points.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.