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 Signature & Trend Analysis

Oracle Tips by Burleson Consulting

Originally reproduced from "Oracle Magazine", and updated 29 March 2007

For complete details on STATSPACK and AWR in my latest Oracle book Oracle Tuning: The Definitive Reference Also see ION for Oracle, a new tool to help automate the analysis of Oracle STATSPACK and AWR reports.


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 STATSPACK and AWR utilities collect the necessary data along with the capability of generating trend analysis reports against the time-series performance tables.

The STATSPACK utility was first introduced in Oracle8i (release 8.1.6) and was superseded by the AWR in 10g, although you can still use STATSPACK if you are not licensed for AWR.

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$. The appendix provides a complete list of STATSPACK table names

The STATSPACK Tables
STATS$BG_EVENT_SUMMARY 

STATS$BUFFER_POOL

STATS$BUFFER_POOL_STATISTICS 

STATS$DATABASE_INSTANCE

STATS$ENQUEUESTAT

STATS$FILESTATXS 

STATS$IDLE_EVENT

STATS$LATCH 

STATS$LATCH_CHILDREN

STATS$LATCH_MISSES_SUMMARY 

STATS$LEVEL_DESCRIPTION 

STATS$LIBRARYCACHE 

STATS$PARAMETER 

STATS$ROLLSTAT 

STATS$ROWCACHE_SUMMARY 

STATS$SESSION_EVENT 

STATS$SESSTAT 

STATS$SGASTAT_SUMMARY 

STATS$SGAXS 

STATS$SNAPSHOT 

STATS$SQL_SUMMARY 

STATS$STATSPACK_PARAMETER 

STATS$SYSSTAT 

STATS$SYSTEM_EVENT 

STATS$WAITSTAT

To see the columns in these tables, we can simply choose a table name and use the SQL*Plus 'DESCRIBE' command as shown in here.


SQL> desc STATS$BUFFER_POOL_STATISTICS;



Name Null? Type

------------------------------- -------- ----

SNAP_ID NOT NULL NUMBER(6)

DBID NOT NULL NUMBER

INSTANCE_NUMBER NOT NULL NUMBER

ID NOT NULL NUMBER

NAME VARCHAR2(20)

SET_MSIZE NUMBER

CNUM_REPL NUMBER

CNUM_WRITE NUMBER

CNUM_SET NUMBER

BUF_GOT NUMBER

SUM_WRITE NUMBER

SUM_SCAN NUMBER

FREE_BUFFER_WAIT NUMBER

WRITE_COMPLETE_WAIT NUMBER

BUFFER_BUSY_WAIT NUMBER

FREE_BUFFER_INSPECTED NUMBER

DIRTY_BUFFERS_INSPECTED NUMBER

DB_BLOCK_CHANGE NUMBER

DB_BLOCK_GETS NUMBER

CONSISTENT_GETS NUMBER

PHYSICAL_READS NUMBER

PHYSICAL_WRITES NUMBER

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

Oracle only supports a single report called 'STATSREP.SQL' for STATSPACK and AWRRPT.QL for the 10g AWR. 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.

You can paste any STATSPACK or AWR report into www.statspackanalyzer.com, to get expert analysis of any STATSPACK or AWR report.

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.

Daily Reports

These show hourly trends, grouped by hour of day. In the example below 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.

Weekly Reports

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

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 this script which prints average disk I/O by day of the week

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

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.
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 migrated/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.

The Ion tool is the easiest way to plat STATSPACK data for graphical analysis.

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

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.

The Ion tool is the easiest way to display STATSPACK and AWR data in Oracle and Ion is inexpensive (starting at $41.95) and allows you to spot hidden STATSPACK trends.

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

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of 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.


 

 

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

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational