|
|
Using STATSPACK to monitor
Oracle performance
Apr 11, 2001
- Updated Feb 11 2015 Oracle Tips by Burleson Consulting |
The Oracle STATSPACK utility (and it's
successor, AWR), is one of the most
exciting Oracle enhancements. With STATSPACK and AWR, the Oracle professional now has
an easy-to-use tool that can be quickly installed and provides a great deal of
performance information about an Oracle database. This article will introduce
you to the STATSPACK and AWR utility and show you how to put it to work.
Also, see these related notes on STATSPACK and AWR in
my latest Oracle book
Oracle Tuning: The Definitive Reference by Rampant TechPress. The online code depot is
available immediately.
Automated STATSPACK analysis
|
If you are learning to to read a
STATSPACK or AWR report, and want to test your skills,
see
http://burleson-dba.com/SP/
a free tool to help automate the analysis of
Oracle STATSPACK and AWR reports. |
Understanding the STATSPACK features
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 tablesThese tables contain information about system,
session, and idle events within the Oracle region.
- Parallel server tablesThese 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 tablesThese tables store information about latches,
SGA statistics, SQL statements, and the background events within Oracle.
- System tablesThe 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 tablesThe 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.sqlThis 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.sqlThis 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 statisticsThis 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 StatementsThis level includes all level 0
statistics plus SQL statements in the stats$sql_summary table.
- Level 10: Add child latch statisticsThis 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;
SEE CODE DEPOT FOR FULL SCRIPTS
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.
SEE CODE DEPOT FOR FULL SCRIPTS
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.
SEE
CODE DEPOT FOR FULL SCRIPTS
Next, let's look at the other area of STATSPACK reportingthe 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:
- Monday0.98
- Tuesday0.93
- Wednesday0.91
- Thursday0.96
- Friday0.89
- Saturday0.92
- Sunday0.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. If you want to
delve a little deeper, I go into a great deal of detail in my latest book,
"Oracle
Tuning: The Definitive Reference",
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 burdenand you
can create excellent trend reports to both measure and predict Oracle database
performance.
|
|
|
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!
|
|
|
|