Oracle Database Trend Analysis Using STATSPACK
Oracle Tips by Burleson Consulting
Originally published in ORACLE
I've written both Oracle Press
books on STATSPACK analysis, and my latest
Tuning: The Definitive Reference", has over 900 pages
of my best STATSPACK and AWR 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.
My latest effort, in conjunction with other Oracle tuning experts) is called
AWR Analyzer , a new tool to
help automate the analysis of Oracle STATSPACK and AWR reports.
For professional STATSPACK analysis, out BC Oracle health
check can certify that all of your global settings are fully
Another superb tool for STATSPACK
report analysis is the Workload Interface Statistics Engine (Ion),
This article below is from 1999, and lots has changed
since it's original publication. Please see
AWR Analyzer (an expert system for
STATSPACK and AWR analysis), plus these related articles:
In order to
accurately tune any Oracle database, you need a
historical data collection mechanism and the ability to
translate the data into reports that show the changes
affecting database performance over time. Oracle's new
STATSPACK utility collects the necessary data along with
the capability of generating trend analysis reports
against the STATSPACK tables.
utility is a set of scripts that runs a special version
of the Oracle Begin-Statistics (BSTAT) and End-Statistics
(ESTAT) utilities. These utilities capture elapsed time
statistics for over 100 performance metrics. However,
unlike the BSTAT/ESTAT utilities, STATSPACK captures the
performance data and stores the data in special Oracle
All of the
STATSPACK tables are owned by the PERFSTAT user, and all of the tables
begin with STATS$.
Here we see that
v$buffer_pool_statistics contains great information,
including the total physical reads and writes for the
entire database. Once we understand the table structures
we are ready to look at customizing trend reports for
capacity planning. Let's begin with a general discussion
of trend analysis and then look at how these reports are
STATSPACK is so new, Oracle only supports a single report
called 'STATSREP80.SQL'. While this is a great report for
viewing the changes between two specific points in time,
it does not provide the data needed to create trend
reports. Experienced DBA's know that measuring
performance data over time can provide great insights
into hourly, daily, and weekly "signatures".
Just like every person has a unique signature, each
database had a unique signature for I/O, sorting, data
buffer hit ratio, and so on. These signatures, in turn,
tell the DBA when specific database tasks are stressing
The purpose of
time series plotting is to develop signatures for various
database metrics. For example, we might note that our I/O
is always high on Tuesday mornings at 9:00 AM, or that
our data buffer hit ratio always drops low on Wednesday
afternoons. By having this signature information the DBA
can properly plan for the peaks and valleys in the
database performance. Let's explore this concept by
looking at some actual STATSPACK plots.
show the overall trend of the database over a specific
time period. In the example in Figure 1 we see a report
on the total bytes for a database that has been plotted
in MS-Excel with a linear regression line added to show
the database growth rate.
1 - Database Size Time Series Report
hourly trends, grouped by hour of day. In the example in
Figure 2, we see a signature for average read I/O and
write I/O, averaged by the hour of the day. Here we can
clearly see that this database has a read I/O signature
with a peak at 7:00 AM and again at 8:00 PM. We also see
that this database has a relatively constant write
2 - Plot for Average I/O by Hour of Day
In Figure 3 we
see disk sorts averaged by day of week. Here we see a
clear signature where disk sorts are high on Monday, peak
on Tuesdays, high on Wednesdays and decline on Thursdays
and Fridays. This can give the DBAs a heads-up about
potential high impact times for the TEMP tablespace.
3 - Daily Trend Report
Now that we are
convinced about the usefulness of trend reports, let's
examine some of the common metrics that are extracted
So how do we
average by hour or day? Actually it us quite simple.
Consider Listing 3 which prints average disk I/O by day
of the week
Listing 3 - STATSPACK
Query to Average by 'day of the week'*
set pages 9999;
column reads format 999,999,999
column writes format 999,999,999
fs.snap_id = sn.snap_id
change this script to 'average by hour of the
day', simply change 'day' to 'HH24'.
Dan Fink also has some nice examples of using Oracle analytic SQL for
STATSPACK trend reports:
OVER (ORDER BY s1.snap_id) prev_val,
OVER (ORDER BY s1.snap_id) delta_val
FROM stats$snapshot s1,
WHERE s1.snap_id BETWEEN 313 AND 320
AND s1.snap_id = w1.snap_id
AND w1.event = 'db file sequential read'
ORDER BY w1.event, s1.snap_id;
Comparing two periods:
sy.value - (LAG(sy.value)
OVER (PARTITION BY sy.name
ORDER BY sy.snap_id)) statdelta
FROM stats$sysstat sy
WHERE sy.snap_id IN (12208,12599,13480,13843)
AND sy.name IN
('consistent gets','consistent changes',
'db block gets', 'db block changes')
ORDER BY sy.name, sy.snap_id;
Data is Best for Trend Analysis?
With hundreds of
statistics available in STATSPACK, the question arises as
to what metrics are the most useful?
- It is
important to monitor the amount of sorting in the
database, especially the amount of sorting that
is done in the TEMP tablespace (sorts (disk)).
reduction of I/O is the primary goal of Oracle
tuning, so a trend report showing hourly disk
reads can be very useful for measuring the effect
of table reorganizations the re-sequence rows
into index order.
- The amount
of disk writes has a great impact on the overall
throughput of the Oracle database, and is
especially important when rollback segment
activity is critical.
- I/O waits
- This is a
very important metric that can be used to
identify and correct I/O contention. High I/O
waits occur when two or more data files are being
accessed simultaneously and the read-write head
movement of the DASD causes the disk to wait for
the completion of I/O. For details, see
Tables on Disk I/O (Oracle Magazine,
January, 2000), and
Tuning Disk I/O
in Oracle8 (Oracle Magazine,
- Buffer Busy
- A buffer
bust wait occurs when a Oracle data block resides
in a buffer, but the requesting program must wait
to retrieve the data block. Buffer Busy Waits can
commonly occur when a table has concurrent
'UPDATE' or 'INSERT DML' and only one freelist is
defined for the table.
- Redo log
- Redo Log
space requests occur when the log buffer is too
small to hold the volume of redo log activity.
- Latch pin
- The pin hit
ratios for the database instance give a good idea
of the sizing of the shared_pool_size parameter
in the init.ora file.
- Table Fetch
- This metric
give a general indication of database chained
rows. When chaining becomes excessive, additional
I/O is required to service a query, and the DBA
should increase PCTFREE for the table and
reorganize the table to remove the chains.
understanding of the structure of the STATSPACK tables
and a little practice, customized reports can easily be
of Oracle Trends Using Spreadsheets
performance data has been captured, the STATSPACK reports
can be run against the tables to provide capacity
planning and trend analysis. This can also incorporate
linear regression and predictive models so the DBA can
predict when to order more disk memory based on prior
To give a simple
example, let's take a simple extract and plot it using
the chart wizard in MS-Excel. The steps are:
Run the query in SQL*Plus against the
Cut and paste the result into the
In MS-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
You can also use
Excel-DB to get STATSPACK data into a
Here are the
steps in detail:
One. First we copy the data using <CTRL>
Two. Next we open MS-Excel and paste the data
using <CTRL> V.
Three. With our data column highlighted,
choose 'DATA' from the drop-down menu and then
'Text to Columns'. Run the 'Text Wizard' by
Four. Next we press the chart wizard button
and choose a line chart.
Five. Now we have the graph ready to
Now that we have
covered the graphing tools, let's examine a generic alert
script that uses STATSPACK data to automatically alert
the DBA to impending problems.
Exception Alert Reports for the DBA
One of the most
effective reports against the STATSPACK data is the
ability to create out-of-bounds alerts whenever a
statistic exceeds a pre-defined threshold. The script
'alert.sql' in the appendix can be easily scheduled to
run daily and provide the DBA with a report showing all
of the exceeded thresholds.
Here are the
thresholds that are measured by 'ALERT.SQL':
* Buffer hit ratio < 90%
* Redo log space requests > 20/hr
* Disk sorts > 20/hr
* Buffer bus waits > 10/hr
* Table fetch continued rows > 10,000/hr
* High I/O waits > 5,000/hr
Some shops also
extend STATSPACK and store UNIX performance metrics for
alert reports. See the article
Automating Oracle Tuning (Oracle Magazine -
July, 1996), for details on this technique. Of
course, the DBA can easily customize the 'ALERT.SQL'
script according to their specific needs. In some shops,
this script is installed and used by management to
monitor the effectiveness of the DBA in keeping the
database running properly.
In shops where
the UNIX server has connectivity to e-mail, the daily
alert reports are mailed to the DBA. This will free up
the DBA from the tedious chore of constant monitoring and
allow them to concentrate on more complex DBA tasks.
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!