STATSPACK Reports for Forecasting
Oracle Tips by Burleson Consulting
The standard STATSPACK collection mechanism has
a host of great information for trend analysis. The scripts I have
placed on the
companion web site
for this book contain prewritten STATSPACK extract scripts.
There are three types of scripts:
Long-term trend extract scripts These
simply display the hourly observations for the desired metric.
Averages by hour of the day These
reports show averages aggregated by the hour of the day. These
scripts are in the form rpt_avg_xxx_hr.sql and are very
useful for identifying regular trends in hourly processing.
Averages by day of the week These
reports show averages aggregated by day of the week. They are in the
form rpt_avg_xxx_dy.sql and report on regular trends in daily
Basic STATSPACK Metrics for Trend Analysis
The following STATSPACK data are especially
interesting for trend analysis:
Physical reads and physical writes (rpt_io.sql,
rpt_avg_io_dy.sql, rpt_avg_io_hr.sql) These
reports are great for showing trends in the physical read and write
activity within Oracle. Note that an Oracle physical read is not
necessarily an actual disk read because of the RAM caches on most
Data buffer hit ratio (rpt_bhr.sql,
Plotting the data buffer hit ratio over time can give the Remote DBA great
insight into times when the database I/O patterns are changing.
Especially useful are the average data buffer hit ratios by hour of
the day and day of the week. These will often show theretofore
hidden patterns in buffer processing.
I/O waits (rpt_io_wait.sql,
The I/O wait report is useful for showing times when the database
was forced to wait for a latch before granting access to an Oracle
Chained row fetches (rpt_chain.sql,
rpt_avg_chain_dy.sql, rpt_avg_chain_hr.sql) Tracking
the fetching of continued rows can help the Remote DBA decide the optimal
time to reorganize the database.
Full table scans (rpt_fts.sql,
rpt_avg_fts_dy.sql, rpt_avg_fts_hr.sql) Tracking
full table scans can help the Remote DBA understand those time periods when
large aggregation or summarization queries are being run. On poorly
tuned databases, this metric will tell the Remote DBA when to explain the
SQL in stats$sql_summary for SQL tuning.
Cumulative logons (rpt_logon.sql,
rpt_avg_logon_dy.sql, rpt_avg_logon_hr.sql) This
report tracks changes in the cumulative logons statistic in
stats$sesstat. For lone databases, the hourly and daily average
reports can show when the end-user community is most active.
Sorting activity (rpt_sorts.sql,
Tracking disk sorts is essential in setting the appropriate value
for sort_area_size. If the disk sorts are too high,
increasing sort_area_size can greatly improve the performance
of sorting tasks.
STATSPACK Extensions for Database Server Trend Analysis
If you implement the STATSPACK extensions for
server statistics (see Chapter 5), you also have scripts to track the
following server metrics. The scripts are called rpt_vmstat.sql,
rpt_vmstat_dy.sql, and rpt_vmstat_hr.sql. These scripts
allow you to choose the metric that you are interested in tracking.
NOTE: This script requires you to specify the name
of the server. Since the stats$vmstat table collects data from the
database server and the web servers, you need to filter the report for
the server you are interested in getting a trend analysis for.
The database server and web server reports are
especially useful for hardware trend analysis. These metrics include
Run queue trends The CPU run queue is a
great way to measure the load average for the CPU. This data is
great for load balancing and forecasting when to add more CPUs.
Page-in trends Any time of nonzero
page-in activity indicates a shortage of RAM memory. These reports
can forecast when the server will exceed RAM capacity.
CPU trends Plotting the average user CPU
plus system CPU values can tell the Remote DBA the times when the server is
Let's start by examining the generic STATSPACK
script and see how it is used
to track CPU usage. In the next example, we choose to track the
average user CPU plus system CPU values.
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
-- 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,
server_name = 'prodb1'
The following data was generated from running
2001-12-20 6 0
2001-12-21 6 0
2001-12-22 4 0
2001-12-23 6 0
2001-12-24 4 0
2001-12-25 7 0
2001-12-26 4 0
2001-12-27 4 0
2001-12-28 5 0
2001-12-29 5 0
2001-12-30 4 0
2001-12-31 4 0
2001-01-01 3 0
2001-01-02 4 0
2001-01-03 24 0
2001-01-04 33 0
2001-01-05 23 0
2001-01-06 14 0
2001-01-07 13 0
2001-01-08 19 0
2001-01-09 22 0
2001-01-10 21 0
2001-01-11 13 0
2001-01-12 13 0
2001-01-13 10 0
2001-01-14 9 0
2001-01-15 21 0
2001-01-16 20 0
2001-01-17 32 0
2001-01-18 25 0
2001-01-19 27 0
2001-01-20 24 0
2001-01-21 24 0
2001-01-22 40 0
2001-01-23 27 0
2001-01-24 25 0
2001-01-25 23 0
2001-01-26 21 0
2001-01-27 18 0
2001-01-28 20 0
2001-01-29 40 0
2001-01-30 29 0
2001-01-31 21 0
2001-02-01 30 0
2001-02-02 33 0
2001-02-03 25 0
2001-02-04 17 0
2001-02-05 27 0
As is, this data is not
particularly meaningful, but when plotted, we see a clear trend, as
shown in Figure 15-9).
Figure 15-122: A plot of CPU usage over time
Here we see that the average CPU usage is
increasing at a rate of 20 percent per month. At existing rates, the
CPU subsystem will reach the maximum capacity in five months. This
information can be critical to top IS management who must plan for
computer hardware upgrades.
We can quickly
modify this script to display page-in values, and we see that this
database server is in need of additional RAM. Remember, any nonzero
values for RAM page-in indicate that the RAM capacity has been
Now, let's look at tracking server trends by
hour of the day and day of the week.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.