Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle Database Trend Analysis Using STATSPACK

Oracle Tips by Burleson Consulting
 


Originally published in ORACLE Magazine.

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

 

Ion Oracle tuning tool software Another superb tool for STATSPACK report analysis is the Workload Interface Statistics Engine (Ion), www.ion-dba.com

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.

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

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
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')
;

*To change this script to 'average by hour of the day', simply change 'day' to 'HH24'.

Oracle guru Dan Fink also has some nice examples of using Oracle analytic SQL for STATSPACK trend reports:

Read deltas:

SELECT s1.ucomment,
w1.event,
s1.snap_id,
w1.total_waits,
LAG(w1.total_waits)
OVER (ORDER BY s1.snap_id) prev_val,
w1.total_waits -
LAG(w1.total_waits)
OVER (ORDER BY s1.snap_id) delta_val
FROM stats$snapshot s1,
stats$system_event w1
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:
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 Two. Next we open MS-Excel and paste the data using <CTRL> V.

 

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. 

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.