Understanding the STATSPACK

The best feature of STATSPACK is that it stores Oracle performance information in a set of 25 tables that can be used to develop historical trends. By interrogating these tables, Oracle professionals can gain tremendous insight into the relative performance of their databases.
The STATSPACK utility is the evolution of Oracle's utlbstat.sql and utlestat.sql utilities. In Oracle7, these utilities were used to gather an elapsed time report of Oracle performance. In Oracle8, this concept has been enhanced by STATSPACK to allow for the capture of the elapsed time report into a set of tables. As seem below, the 25 STATSPACK tables provide a complete picture of everything that's going on within the Oracle database.
 



The STATSPACK schema contains several control tables. The stats$parameter tables controls the thresholds for collection of detailed information, and a table called stats$level_description provides information regarding the level of detail collected with a snapshot.

Within a STATSPACK installation, the stats$sql_summary table will grow very rapidly because STATSPACK will extract SQL from the library cache every time a snapshot is executed. Hence, the Oracle administrator has to be careful to set the appropriate threshold values for stats$sql_summary data collection to ensure that the database doesn't run wild, consuming multiple megabytes of information every day.

The main anchor for STATSPACK is the table called stats$snapshot. This table contains the snapshot ID for all of the subordinate tables and the snap_time indicating when the snapshot was taken. Oracle also implements all of the subordinate tables with referential integrity, using the on cascade delete option. 

This means that the stats$snapshot table can be deleted in order to delete rows from all of the subordinate tables after they have passed their useful lives within the database. Underneath the stats$snapshot table, we see several categories of system tables. These categories include event tables, parallel server tables, SGA summary tables, system tables, and transaction tables:

• Event tables—These tables contain information about system, session, and idle events within the Oracle region.
• Parallel server tables—These tables are used in an OPS environment to store information about row caching in the Integrated Distributed Lock Manager (IDLM), as well as SGA information.
• SGA summary tables—These tables store information about latches, SGA statistics, SQL statements, and the background events within Oracle.
• System tables—The system table section of the STATSPACK utility contains information on enqueue stats, waits stats, latch stats, as well as system and session statistics, including information on the library cache and rollback statistics.
• Transaction tables—The STATSPACK transaction tables contain information about the buffer pool, the buffer pool statistics, and most importantly, the I/O activity against every file within the system.

Taken together, these 25 STATSPACK tables provide a huge amount of information regarding the performance of the Oracle database. It is the challenge of the Oracle administrator to understand these tables and the value of the information they contain and then to understand how to apply this information to their own performance-tuning needs.
How STATSPACK works
The Oracle STATSPACK utility was the natural outgrowth of Oracle's earlier utilities that compared beginning snapshots with ending snapshots. The original script called for utlbstat.sql and utlestat.sql. The only shortcoming to using these utilities was that the output from the elapsed time report was not stored in any type of Oracle table, and it was cumbersome to compare elapsed-time reports. Starting with STATSPACK in Oracle 8.1.6, the STATSPACK utility can take the output of elapsed-time reports and store the results in Oracle tables, where they can be used for time-series analysis. The STATSPACK tables are easy to create and define, and it's also easy to set up collection mechanisms for your Oracle system. Once in place, you can use the STATSPACK data to provide alert reports of out-of-the-ordinary conditions within your Oracle database; STATSPACK data can also produce wonderful trend reports.

STATSPACK was officially introduced with Oracle 8.1.6, but it can be back-ported to run on Oracle 8.0 through Oracle 8.1.5. If you are planning to use STATSPACK with pre-8.1.6 versions of Oracle, you will need the following modifications:

• statscbps.sql—This script adds a v$buffer_pool_statistics view that is required for pre-8.1.6 versions of Oracle. This script should be run after CONNECT INTERNAL and before running the statscre.sql script.
• statsrep80.sql—This is the only STATSPACK-supplied report for Oracle 8.0 through Oracle 8.1.5.

Installing the STATSPACK utility is simple and straightforward. You just go to the $ORACLEHOME/rdbms/admin directory and run scripts beginning with stats. The scripts create a set of 25 STATSPACK tables and also install several PL/SQL packages and procedures that are used by the STATSPACK utility in order to run elapsed-time reports and store the resulting information into these Oracle tables.

There are three snapshot levels used in STATSPACK, with level 5 being the default:

• Level 0: General performance statistics—This level collects general performance statistics, such as wait statistics, system events, system statistics, rollback segment data, row cache, SGA, background events, session events, lock statistics, buffer pool statistics, and parent latch statistics. 
   
• Level 5: Add SQL Statements—This level includes all level 0 statistics plus SQL statements in the stats$sql_summary table. 
   
• Level 10: Add child latch statistics—This level includes everything in the level 5 statistics plus child latches in the stats$latch_children table. You rarely, if ever, need this level of detail, and you should do a level 10 snapshot only when directed by Oracle technical support.

Once installed, you can activate STATSPACK in several ways. The most common way is to go into SQL*Plus as the PERFSTAT user and execute statsauto.sql. This will schedule a STATSPACK data collection every hour.

The Ion tool is the easiest way to display STATSPACK and AWR data in Oracle and Ion is inexpensive and allows you to spot hidden STATSPACK trends.
Most Oracle professionals can install and configure STATSPACK in a matter of a few hours. The real challenge is deciding how to use the information you've collected. You can use the information gathered in the STATSPACK tables in several general areas of Oracle reporting.
Alert reports
The first area of Oracle reporting is Oracle exception reports, whereby the STATSPACK utility can e-mail Oracle professionals notifying them of any out-of-the-ordinary conditions. For example, you might want to be notified for those hours when the data buffer cache hit ratio drops below 90 percent.  The script below can be running in AWR to produce a disk read alert.

break on begin_interval_time skip 2


column phyrds format 999,999,999
column begin_interval_time format a25

select 
   begin_interval_time, 
   filename,
   phyrds
from
   dba_hist_filestatxs 
natural join
   dba_hist_snapshot;
 
BEGIN_INTERVAL_TIME       FILENAME                                PHYRDS
------------------------- ------------------------------         -------
24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF  164,700
                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF  26,082
                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF  472,008
                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF     1,794

In STATSPACK, you can run similar scripts to alert for exceptional conditions.

Another interesting STATSPACK report is one that identifies any Oracle data files with a disproportionate amount of system I/O. You can run the STATSPACK utility against I/O file statistics to produce alerts when any Oracle data file consumes more than 50 percent of the total I/O for the entire database. In cases like these, you'll want to take a close look at that data file and possibly strike it for reorganizing across several disk spindles in order to balance the I/O load for your database. \
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;

If you're running UNIX, it is easy to encapsulate this alert script into a shell script that will e-mail you all exceptional conditions. Below is a script called statspack_alert.sql that will e-mail the output to the DBA.


Next, let's look at the other area of STATSPACK reporting—the use of STATSPACK for long-term trend analysis.

Trend reports
The STATSPACK reports are even more interesting for doing long-term trend analysis. Because STATSPACK can be configured to take hourly reports of the entire Oracle database, it is extremely useful for doing long-term planning in trend analysis and developing predictive models for future resource consumption. Predictive modeling is easy when running STATSPACK reports to summarize information by overall trends, the day of the week, or the hour of the day.

Unlike any other Oracle tool, you can use the STATSPACK utility to provide tremendous insight into both hourly and daily trends that may have gone unnoticed within an Oracle database. Almost all Oracle databases have some kind of ongoing trend patterns, and it's the job of the Oracle DBA to identify those patterns and plan for the changes in database activity. By knowing those repeating periods when their database is undergoing stress, they can take appropriate action in order to alleviate the problem.
The most common uses of STATSPACK reports for trend analysis have to do with creating predictive models to determine when their Oracle databases are going to run short of CPU, memory, or disk hardware resources. These types of resources can usually be predicted for several different kinds of STATSPACK reports, such as the one shown below.

Also see Ion for STATSPACK/AWR Plotting

This report, created by querying the stats$filestatxs table, shows how you can develop a long-term trend report showing the activity within your data buffers. You can easily extract this information, paste it into a Microsoft Excel spreadsheet, and use the chart wizard to create a graph with a linear regression analysis, predicting future needs of your Oracle database. These types of reports are indispensable for the Oracle managers charged with ordering hardware resources before the Oracle database suffers any significant performance degradation.
This query from the Oracle Sponge shows a query that computes wait times for sequential and scattered reads:

select to_char(snap_time,'mm/dd/yyyy hh24:mi:ss') snaptime
, max(decode(event,'db file scattered read', nvl(wait_ms,0), null)) wait_ms_dbfscatrd
, max(decode(event,'db file sequential read',nvl(wait_ms,0), null)) wait_ms_dbfseqrd
, max(decode(event,'db file scattered read', nvl(waits,0), null)) waits_dbfscatrd
, max(decode(event,'db file sequential read',nvl(waits,0), null)) waits_dbfseqrd
from
(
select ps.snap_time
, event
, case
when (total_waits - lag_total_waits > 0)
then round(( (time_waited_micro - lag_time_waited_micro) / (total_waits - lag_total_waits)) / 1000)
else -1
end wait_ms
, (total_waits - lag_total_waits) waits
, (time_waited_micro - lag_time_waited_micro) time_waited
from (
select se.snap_id
, event
, se.total_waits
, se.total_timeouts
, se.time_waited_micro
, lag(se.event) over (order by snap_id, event) lag_event
, lag(se.snap_id) over (order by snap_id, event) lag_snap_id
, lag(se.total_waits) over (order by snap_id, event) lag_total_waits
, lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts
, lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro
from perfstat.stats$system_event se
where event = 'db file sequential read'
and snap_id in (select snap_id from stats$snapshot
where snap_time > trunc(sysdate) - 1
)
union all
select se.snap_id
, event
, se.total_waits
, se.total_timeouts
, se.time_waited_micro
, lag(se.event) over (order by snap_id, event) lag_event
, lag(se.snap_id) over (order by snap_id, event) lag_snap_id
, lag(se.total_waits) over (order by snap_id, event) lag_total_waits
, lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts
, lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro
from perfstat.stats$system_event se
where event = 'db file scattered read'
and snap_id in (select snap_id from stats$snapshot
where snap_time > trunc(sysdate) -1
)
order by event, snap_id
) a
, perfstat.stats$snapshot ss
, perfstat.stats$snapshot ps
where a.lag_snap_id = ps.snap_id
and a.snap_id = ss.snap_id
and a.lag_total_waits != a.total_waits
and a.event = a.lag_event
order by a.snap_id, event
)
group by snap_time
;

The STATSPACK data can also be summarized by day of the week to show overall trend on a daily basis. The script shown below to aggregate the average daily data buffer hit ratio.

SEE CODE DEPOT FOR FULL SCRIPTS

The output from this script will display the average data buffer hit ratio for each day of the week:

• Monday—0.98
• Tuesday—0.93
• Wednesday—0.91
• Thursday—0.96
• Friday—0.89
• Saturday—0.92
• Sunday—0.91

You can easily plot this data using the Excel spreadsheet to indicate the average daily trends, as shown below:

Also see Ion for STATSPACK/AWR Plotting

You can also modify the script to collect the data by hour of the day by replacing the "day" string with "hh24." Below, see a STATSPACK trend report that displays the averages by hour of the day.

Also see Ion for STATSPACK/AWR Plotting

Conclusion
While it is beyond the scope of this article to go into all of the possible uses of the STATSPACK utility, suffice it to say that STATSPACK can be used to measure every area within the Oracle database. Even more exciting, you can easily create STATSPACK extension tables to monitor the behavior of the database server, the network, and the disk I/O subsystem, thereby providing a complete picture of the performance of the entire Oracle environment. 

One of the most tedious and time-consuming jobs for Oracle DBAs is monitoring the performance of their Oracle databases. By using STATSPACK to automate the collection of performance information, you're relieved of this burden—and you can create excellent trend reports to both measure and predict Oracle database performance.