Listing few online AWR analyzers:
Oracle 10g provides the Automatic Workload Repository. The AWR is a repository of performance information collected by the database to aid
in the tuning process for DBAs. The main focus for the Oracle database in version 10g is self-manageability. AWR is the place where the data to aid in self-management is stored.
The AWR is used to collect performance statistics including:
Identifies the SQL statements that have the
The data is fed from MMON. The information is stored in the sysaux tablespace.
Several of the automatic database tuning features require information from the AWR to function correctly, including:
Snapshots Interval and Retention Time
Check snapshots interval and retention time from DBA_HIST_WR_CONTROL.
SELECT snap_interval, retention FROM dba_hist_wr_control; SNAP_INTERVAL RETENTION -------------------- -------------------- +00000 00:30:00.0 +00007 00:00:00.0
They can be modified through DBMS_WORKLOAD_REPOSITORY. By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the following procedure.
BEGIN dbms_workload_repository.modify_snapshot_settings( interval => 60, retention => 10*24*60 ); END; /
Extra snapshots can be taken and existing snapshots can be removed, as shown below.
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; BEGIN DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range ( low_snap_id => 22, high_snap_id => 32); END; /
Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.
BEGIN DBMS_WORKLOAD_REPOSITORY.create_baseline ( start_snap_id => 210, end_snap_id => 220, baseline_name => 'batch baseline'); END; /
The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted.
BEGIN DBMS_WORKLOAD_REPOSITORY.drop_baseline ( baseline_name => 'batch baseline', cascade => FALSE); -- Deletes associated snapshots if TRUE. END; /
Baseline information can be queried from the DBA_HIST_BASELINE view.
Automatic Workload Repository Reports
Workload Repository Views
The following workload repository views are available:
AWR from Enterprise Manager
The automated workload repository administration tasks have been included in Enterprise Manager. The “Automatic Workload Repository” page is accessed from the main page by clicking on the “Administration” link, then the “Workload Repository” link under the “Workload” section. The page allows you to modify AWR settings or manage snapshots without using the PL/SQL APIs.
Source / Reference Links: