AWR vs. STATSPACK
The first effective proactive time-series method for Oracle
performance appeared in Oracle7 and used begin and end snapshots,
using the
utlbstat and
utlestat utilities, and the data was stored inside
temporary DBA-defined storage tables. Staring with Oracle8i and
back-portable to Oracle8, Oracle Corporation codified the snapshot
approach with the STATSPACK utility.
While many important time-series reports are now instantly created
within Oracle10g Enterprise Manager the senior Oracle DBA may want
to go beyond the recommendations of ADDM and the SQL Tuning Advisor. Complex time series analysis, such as hypothesis
testing and correlation analysis, still require that custom queries
be written against the
wrh$ tables. Table
3.1 below shows the comparison of Oracle8i and Oracle9i STATSPACK
tables to their AWR equivalents. Fortunately, many of the names of
the
wrh$ tables are
identical to their
stats$ equivalents
making it easy to migrate STATSPACk scripts to AWR.
|
DBA HIST VIEW |
WRH$TABLE |
STATSPACK TABLE |
|
dba_hist_event_summary
|
wrh$_bg_event_summary |
stats$bg_event_summary
|
|
dba_hist_buffer_pool_statistics
|
wrh$_buffer_pool_statistics
|
stats$buffer_pool_statistics
|
|
dba_hist_filestatxs
|
wrh$_filestatxs
|
stats$filestatxs
|
|
dba_hist_latch
|
wrh$_latch
|
stats$latch
|
|
dba_hist_latch_children
|
wrh$_latch_children
|
stats$latch_children
|
|
dba_hist_librarycache
|
wrh$_librarycache
|
stats$librarycache
|
|
dba_hist_rowcache_summary
|
wrh$_rowcache_summary
|
stats$rowcache_summary
|
|
dba_hist_sgastat |
wrh$_sgastat |
stats$sgastat |
|
dba_hist_sql_summary |
wrh$_sql_summary |
stats$sql_summary |
|
dba_hist_sysstat |
wrh$_sysstat |
stats$sysstat |
|
dba_hist_system_event |
wrh$_system_event |
stats$system_event |
|
dba_hist_waitstat |
wrh$_waitstat |
stats$waitstat |
Table 3.1:
STATSPACK, DBA HIST and wrh$
equivalencies
It is fortunate for the seasoned DBA that the column definitions and
contents of these tables are almost identical. This allows easy
porting of the STATSPACK time-series scripts to be run against the
wrh$ tables with a minimum of modification.
The
reads_10g.sql script
below gathers physical disk read counts, the
phyrds column of
dba_hist_filestatxs .
It then joins this data into the
dba_hist_snapshot
view to get the
begin_interval_time
column.
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;
When the
reads.sql script is
executed, a display of the running total of physical reads,
organized by datafile is shown below. In this case, the AWR
snapshots are collected every half-hour, and the DBA is free to
adjust the snapshot collection interval depending on data needs.
SQL> @reads
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
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 2,123
24-FEB-04 11.30.18.296 PM
E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF 167,809
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,248
E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 476,616
E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF 1,795
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 2,244
SEE CODE DEPOT FOR FULL SCRIPTS
Starting from the
reads_10g.sql script,
a where clause criteria can easily be added to create a unique
time-series exception report on specific data file or specific time
periods.
Of course, with a few minor adjustments to this script, physical
writes, read time, write time, single block reads, and a host of
other neat metrics from the
dba_hist_filestatxs
view can also be displayed.
Now that general information has been presented on the AWR concept,
the following section will provide details about the new AWR table
contents.
