The internal structures of the Oracle10g
Automatic Workload Repository (AWR) and the information it stores have already
been presented, but it is important to understand how the AWR evolved from
STATSPACK. The AWR is similar to STATSPACK in that it takes
time-based snapshots of all important performance tuning
v$
dynamic views and stores these snapshots in its repository.
Along with the AWR, Oracle10g delivers a new
version of STATSPACK; the utility that is well known and highly recommended from
previous releases of Oracle starting with 8.1.6.
Introduced in Oracle 10g, the AWR is a more
advanced and convenient feature that has many additional, useful features. It
is much like a next generation of STATSPACK. Of course, AWR is more automated
and stores more information than STATSPACK.
Furthermore, performance data gathered by the
AWR is extensively used by a number of automatic facilities such as the
Automatic Database Diagnostic Monitor
(ADDM), SQL Tuning Advisor, etc. Thus, Oracle
Corporation documentation recommends the use of the AWR functionality over that
of STATSPACK in Oracle10g databases.
The STATSPACK utility is still functional and
can be used by Oracle DBAs the same way it has been used in previous releases. A
look at the structure of the last version of STATSPACK and its general
differences from the AWR is a good place to start.
There is one more significant difference
between the AWR and STATSPACK. The AWR supports the creation and ability to work
with baselines. A baseline is a set of statistics that is defined by a beginning
and ending pair of snapshots. A baseline can be created using the
dbms_workload_repository.create_baseline procedure:
SQL> desc dbms_workload_repository
PROCEDURE CREATE_BASELINE
Argument Name Type
In/Out Default?
----------------- -----------------------
------ --------
START_SNAP_ID NUMBER
IN
END_SNAP_ID NUMBER
IN
BASELINE_NAME VARCHAR2
IN
DBID NUMBER
IN DEFAULT
FUNCTION CREATE_BASELINE RETURNS NUMBER
Argument Name Type
In/Out Default?
----------------- -----------------------
------ --------
START_SNAP_ID NUMBER
IN
END_SNAP_ID NUMBER
IN
BASELINE_NAME VARCHAR2
IN
DBID NUMBER
IN DEFAULT
All of the baselines that are created are
visible in the
dba_hist_baselines view:
SQL> desc dba_hist_baseline
Name Null? Type
---------------- -------- ----
DBID NOT NULL NUMBER
BASELINE_ID NOT NULL NUMBER
BASELINE_NAME VARCHAR2(64)
START_SNAP_ID NUMBER
START_SNAP_TIME TIMESTAMP(3)
END_SNAP_ID NUMBER
END_SNAP_TIME TIMESTAMP(3)
After two baselines have been created,
statistics related to those two baselines can be compared. The AWR automatically
preserves the snapshots, which are part of any existing baseline.
STATSPACK does not support the functionality of
baselines; however, the Ion tool provides Oracle DBAs with the ability to
create and work with baselines. Using the Ion GUI interface, a current
statistic’s behavior can be compared with a baseline.
Conclusion
In this chapter, our comparison of the AWR to
the STATSPACK utility shows that the AWR presents a much more comprehensive and
advanced tool when compared with STATSPACK.
The AWR gathers and stores history for an
extended set of performance data that is available in Oracle10g. The main points
of this chapter include:
§
The great benefit of the AWR is that it requires minimal
administration efforts from the Oracle DBA.
§
The AWR gives Oracle DBAs a powerful tool for performance tuning
and trend analysis. It is simple enough to be used as a monitoring tool by
junior DBAs yet powerful enough to be used as an advanced data mining source for
detailed time-series analysis, trend identification and capacity planning.
§
The AWR forms an analysis base for Oracle10g intelligent
self-tuning features such as ADDM, the SQL Tuning Advisor, Automatic Segment
Management and ASM
Now that
the similarities and differences between the AWR and the STATSPACK utility have
been introduced, the next chapter will provide a detailed look into Oracle10g
v$
views.
