Oracle Database Trend Analysis Using STATSPACK

I've written both Oracle Press books on STATSPACK analysis, and my latest book "Oracle 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 www.statspackanalyzer.com , 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 optimized.

 

Another superb tool for STATSPACK report analysis is the Workload Interface Statistics Engine (WISE), www.wise-oracle.com

This article below is from 1999, and lots has changed since it's original publication.  Please see www.statspackanalyzer.com (an expert system for STATSPACK and AWR analysis), plus these related articles:

• STATSPACK to monitor disk IO

• Using STATSPACK to monitor Oracle performance

• Oracle 10g AWR script for physical disk reads

• Oracle STATSPACK installation steps

• Oracle STATSPACK Reporting Tools

• Installing Oracle STATSPACK

• Overview of Oracle STATSPACK Scripts

• Oracle Statspack Analyzer

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.

STATSPACK Utility For Oracle

The STATSPACK utility was first introduced in Oracle8i (release 8.1.6), however, Oracle provides a patch available for download whereby STATSPACK can be used with any Oracle8 database.

There is an excellent multi-part article beginning in the March 2000 issue of Oracle Magazine which discusses the pre-Oracle8i versions of STATSPACK. Once installed on Oracle8, the DBA is ready to begin collecting and analyzing useful performance data.

How STATSPACK Works

The STATSPACK 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 tables.

All of the STATSPACK tables are owned by the PERFSTAT user, and all of the tables begin with STATS$.

Here we see that the 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 generated.

Trend Analysis Using STATSPACK

Because 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 database.

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.

Total Time Series Reports

These reports 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.

Figure 1 - Database Size Time Series Report

Daily Reports

These show 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 signature.

Figure 2 - Plot for Average I/O by Hour of Day

Weekly Reports

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.

Figure 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 from STATSPACK.

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

set pages 9999;
column reads format 999,999,999
column writes format 999,999,999
select 
to_char(snap_time,'day'),
avg(physical_reads) reads,
avg(physical_writes) writes
from
perfstat.stats$buffer_pool_statistics fs,
perfstat.stats$snapshot sn
where
fs.snap_id = sn.snap_id
group by
to_char(snap_time,'day')
;

SELECT sy.snap_id,

sy.statistic# statistic#,

sy.name statname,

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;

What STATSPACK Data is Best for Trend Analysis?

With hundreds of statistics available in STATSPACK, the question arises as to what metrics are the most useful?

Sorts

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)).

Physical disk reads

The 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.

Physical disk writes

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 Turning the Tables on Disk I/O (Oracle Magazine, January, 2000), and Tuning Disk I/O in Oracle8 (Oracle Magazine, November, 1999).

Buffer Busy Waits

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 space requests

Redo Log space requests occur when the log buffer is too small to hold the volume of redo log activity.

Latch pin hit ratio

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 Continued Row

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.

With an understanding of the structure of the STATSPACK tables and a little practice, customized reports can easily be created.

Plotting Graphs of Oracle Trends Using Spreadsheets

Once the 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 consumption rates.

To give a simple example, let's take a simple extract and plot it using the chart wizard in MS-Excel. The steps are:

1. Run the query in SQL*Plus against the STATSPACK data

2. Cut and paste the result into the spreadsheet

3. 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.

4. Press the chart wizard button and create a line chart

You can also use Excel-DB to get STATSPACK data into a spreadsheet.

Here are the steps in detail:

Step One. First we copy the data using <CTRL> C.

Step Three. With our data column highlighted, choose 'DATA' from the drop-down menu and then 'Text to Columns'. Run the 'Text Wizard' by fixed-width separation.

Step Four. Next we press the chart wizard button and choose a line chart.

Step Five. Now we have the graph ready to complete.

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.

Customized 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.