 |
|
Daily vmstat Trend Reports
Oracle Database Tips by Donald Burleson |
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. Here is a sample SQL script that
aggregates server values:
L 6-15
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 here we see the average user and wait
CPU times for each day of the week:
L 6-16
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 6-7. We will cover the method of plotting STATSPACK data in MS-Excel in
Chapter 15.
Figure 6-34: 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:
L 6-17
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 run queue and user + system CPU values and wait CPU values, aggregated
by hour of the day:
L 6-18
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 6-8. 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-35: A long-term handware 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. We will go into more detail on using
STATSPACK information for management planning in Chapter 15.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
and include the URL for the page.
