BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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.


set pages 9999;

column reads  format 999,999,999
column writes format 999,999,999

   avg(newreads.value-oldreads.value) reads,
   avg(newwrites.value-oldwrites.value) writes
   perfstat.stats$sysstat oldreads,
   perfstat.stats$sysstat newreads,
   perfstat.stats$sysstat oldwrites,
   perfstat.stats$sysstat newwrites,
   perfstat.stats$snapshot   sn
   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
group by

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:

>sqlplus perfstat/perfstat

SQL*Plus: Release - Production on Mon Feb 5 08:21:56 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release - 64bit Production
With the Partitioning option
JServer Release - 64bit Production

SQL> @rpt_io

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.




Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter