Plotting STATSPACK Data Using Microsoft Excel 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:

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

2.                  Cut and paste the result into the spreadsheet.

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

4.                  Press the Chart Wizard button and create a line chart.

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

rpt_io.sql

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:

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:

The report is now displayed on our screen. Now we are ready to highlight and extract the data to place it in a spreadsheet.

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

Step 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 15-2).

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 separation

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

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

Finally, we choose Chart | Add Trendline from Excel to add a forecast line.

Illustration 15-1

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.

Figure 15-8 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.