Plotting STATSPACK Data Using
Oracle Tips by Burleson Consulting
Once you have implemented hourly STATSPACK collection,
you will develop a great historical database for detailed analysis. In
addition to the alerts that we covered in Chapter 14, you can also run
STATSPACK reports to provide capacity planning and trend analysis.
This can then incorporate linear regression and predictive models so
the Remote DBA can predict when to order more disks, RAM memory, and CPU,
based on prior consumption rates.
Fortunately, it is not necessary that you
purchase an expensive statistical package such as SAS and SPSS. You
can use standard Microsoft Excel spreadsheets with chart wizards to
make acceptable charts and add forecasts. Let's see how this works.
Plotting STATSPACK Data with a Spreadsheet Chart Wizard
While many third-party products are capable of
plotting data for graphical analysis, adequate graphics can be created
using Microsoft Excel spreadsheets. The steps for creating the chart
are as follows:
Run the query in SQL*Plus against the STATSPACK data.
Cut and paste the result into the spreadsheet.
In Excel, with the data you have just pasted highlighted,
choose Data from the drop-down menu and then Text To Columns. This
will separate the columns into distinct cells.
Press the Chart Wizard button and create a line chart.
Choose Chart | Add Trendline to create a forecast line.
To give a simple example, let's take a simple STATSPACK
data extract and plot it using the Chart Wizard in Excel. Let's
perform an actual analysis and see the steps firsthand. We'll assume
that our CIO just contacted us, and he or she wants to know the rate
of increase in disk read activity for the entire database. The CIO
knows that this is not an easy question to answer and gives us two
days to assemble and plot the disk read information. Because we know
STATSPACK, we know that we can collect and plot this information from
the stats$sysstat table in a matter of a few minutes.
Here is a step-by-step description of the
process of getting a forecast from STATSPACK data.
Step 1: Customize the STATSPACK Report
We begin by selecting rpt_io.sql, which
displays hourly physical read and write statistics for the whole
database. Because this script reports on each hourly snapshot, we
modify the script to compute the average read and writes per day. Note
that we have changed the data format string and added the avg
function to the code.
set pages 9999;
column reads format 999,999,999
column writes format 999,999,999
newreads.snap_id = sn.snap_id
newwrites.snap_id = sn.snap_id
oldreads.snap_id = sn.snap_id-1
oldwrites.snap_id = sn.snap_id-1
oldreads.statistic# = 40
newreads.statistic# = 40
oldwrites.statistic# = 41
newwrites.statistic# = 41
(newreads.value-oldreads.value) > 0
(newwrites.value-oldwrites.value) > 0
Computing Averages with STATSPACK Scripts
There is often confusion about the proper way
to compute average values in STATSPACK queries. For example, the
preceding query must subtract the ending snapshot value from the
starting snapshot value in order to get the number of reads during the
one-hour period between snapshots. When we want to get the average
reads per day, we have two options:
In this snippet we see two methods for computing the
average physical reads per day. As it turns out, these variations in
computation return identical results:
TO_CHAR(SN READS READS
---------- ------------ ------------
2001-12-12 193 193
2001-12-13 37 37
2001-12-14 63 63
2001-12-15 100 100
2001-12-16 163 163
2001-12-17 165 165
Step 2: Run the Report in SQL*Plus
Now that we have modified the script to compute daily
averages, we enter SQL*Plus and run the report:
SQL*Plus: Release 184.108.40.206.0 - Production on Mon Feb 5 08:21:56 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Oracle8i Enterprise Edition Release 220.127.116.11.0 - 64bit Production
With the Partitioning option
JServer Release 18.104.22.168.0 - 64bit Production
The report is now displayed on our screen. Now
we are ready to highlight and extract the data to place it in a
Step 3: Highlight and Copy the Results
The first step is to highlight the data and
choose Edit | Copy from the Windows toolbar (see Figure 15-1).
Figure 15-114: Copying data from SQL*Plus
4: Open Excel and Paste the Data
In this step, we start Excel, open a
spreadsheet, and paste our STATSPACK data into the spreadsheet using
Edit | Paste (or ctrl-v).
Note that all of the data still resides in a single column (see Figure
Figure 15-115: Pasting data into a spreadsheet
Please insert figure 15-2 from 007213378-3
Now we need to separate our data into columns
in the spreadsheet.
Step 5: Partition the Data into Columns
With our data
column highlighted, choose Data from the drop-down menu and then Text
to Columns (see Figure 15-3).
Figure 15-116: Choosing Data/Text to Columns
We are now guided through a wizard to column
delimit the values.
Step 6: Column Delimit the Data
Next, we choose Fixed Width in the Text
to Columns Wizard (see Figure 15-4).
Figure 15-117: Choosing fixed-width column
We then accept the defaults for each wizard
step and the data will be placed into separate columns.
Step 7: Start the Chart Wizard
In the next
step, we highlight the physical reads and press the Chart Wizard
button (see Figure 15-5).
Figure 15-118: Choosing the Chart Wizard
Here we see a wizard that will guide us through
the process of creating a graph of our STATSPACK data.
Step 8: Choose a Line Chart
Next, we choose a simple line chart (see Figure
Figure 15-119: Choosing a line chart
Step 9: Complete the Chart Wizard and View the Chart
Now we finish
the Chart Wizard by accepting the defaults, and we get a basic chart
(see Figure 15-7).
Figure 15-120: The basic line chart
Step 10: Add a Trend Line
choose Chart | Add Trendline from Excel to add a forecast line.
At this step you are faced with a choice of
linear regression techniques, including sum of the least squared,
single, double, and triple exponential smoothing methods. In most
cases, you will get the most accurate trend line by choosing the
default linear trend method. Finally, we display the trend line and
the forecast is ready to send to management.
shows our completed forecast, ready for management.
Figure 15-121: A completed STATSPACK chart with
a forecast trend line
Most Remote DBAs will paste the chart in a Microsoft
Word document along with an analysis of the data.
Now that we see how to create graphics from our
STATSPACK data, let's look at other STATSPACK reports that are useful
for forecasting and trend analysis.
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.