|
One of the nice features of Oracle 10g that I really like
is the Automatic Workload Repository (AWR). This new tool is kind of
replacement of STATSPACK. AWR takes snapshots of the system every 60
minutes. You can also create manual snapshots like in statspack. At the end
this tool give you feature to generate txt or HTML (I like it) report.
The article below will explain how to create manual snapshots.
You can manually create snapshots with the CREATE_SNAPSHOT procedure if you
want to capture statistics at times different than those of the
automatically generated snapshots.
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/ |
You can also drop a range of snapshots using the DROP_SNAPSHOT_RANGE
procedure. To view a list of the snapshot Ids along with database Ids, check
the DBA_HIST_SNAPSHOT view. For example, you can drop the following range of
snapshots:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 1,
high_snap_id => 10 dbid => 131045908);
END;
/ |
If you like, you can also adjust the interval and retention of snapshot
generation for a specified database id, but note that this can affect the
precision of the Oracle diagnostic tools.
The INTERVAL setting affects how often in minutes that snapshots are
automatically generated. The RETENTION setting affects how long in minutes
that snapshots are stored in the workload repository. To adjust the
settings, use the MODIFY_SNAPSHOT_SETTINGS procedure. For example:
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 14400,
interval => 15, dbid => 131045908);
END;
/ |
In this example, the retention period is specified as 14400 minutes (10
days) and the interval between each snapshot is specified as 15 minutes. You
can also specify NULL to preserved existing value. If you don’t specify
optional database identifier then local database is used.is 3310949047. If
you do not specify a value for dbid, the local database identifier is used
as the default value. You can check the current settings for your database
instance with the DBA_HIST_WR_CONTROL view.
SQL> col RETENTION format a20
SQL> col SNAP_INTERVAL format a30
SQL> set linesize 120
SQL> select * from DBA_HIST_WR_CONTROL;
DBID SNAP_INTERVAL RETENTION
---------- ------------------------------ --------------------
131045908 +00000 01:00:00.0 +00007 00:00:00.0 |
In above example, snapshot interval is 1 hr and retention period is 7 days.
Running the awrrpt.sql Report
To run an AWR report, a user must be granted the DBA role. You can view the
AWR reports with Oracle Enterprise Manager or by running the following SQL
scripts:
- The awrrpt.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids
.
- The awrrpti.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids for a specified database and
instance.
For example:
To generate a text report for a range of snapshot ids, run the awrrpt.sql
script at the SQL prompt:
| @$ORACLE_HOME/rdbms/admin/awrrpt.sql |
Reference: Oracle®
Database Performance Tuning Guide 10g Release 1 (10.1)
|
