Category: Automatic Workload Repository

Automatic Workload Repository

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.

AWR Features

The AWR is used to collect performance statistics including:

  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  • Object usage statistics.
  • Resource intensive SQL statements

Identifies the SQL statements that have the

  • largest CPU consumption
  • most buffer gets
  • disk reads
  • most parse calls
  • shared memory

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:

  • Automatic Database Diagnostic Monitor
  • SQL Tuning Advisor
  • Undo Advisor
  • Segment Advisor

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.

Baselines

  • Pair of snapshots that represents a specific period of usage.
  • Compare current performance against similar periods in the past.
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

  • Generated in text or HTML
  • Database/Grid Control
  • Package DBMS_WORKLOAD_REPOSITORY
  • Scripts awrrpt.sql (current DBID and instance) or awrrpti.sql (DBID and instance are requested)
  • Very similar to Statspack reports
  • The script awrinfo.sql generates general information about AWR such as the size and data distribution
  •  awrrpti.sql allows you to select a single instance
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.
Time Model Statistics
  • High-level information about the used CPU and elapsed time for different database operations obtained from V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
  • Amount of time is given in microseconds
  • The statistic “DB time” represents the total time spent in database calls; this doesn’t include the time spent by background processes
  • Since waits are part of the elapsed time, “DB time” can be much larger that the elapsed time since instance startup
  • Some of the information provided by these views is already available in V$SYSTAT and V$SESSTA

Workload Repository Views

The following workload repository views are available:

  • V$ACTIVE_SESSION_HISTORY – Displays the active session history (ASH) sampled every second.
  • V$METRIC – Displays metric information.
  • V$METRICNAME – Displays the metrics associated with each metric group.
  • V$METRIC_HISTORY – Displays historical metrics.
  • V$METRICGROUP – Displays all metrics groups.
  • DBA_HIST_ACTIVE_SESS_HISTORY – Displays the history contents of the active session history.
  • DBA_HIST_BASELINE – Displays baseline information.
  • DBA_HIST_DATABASE_INSTANCE – Displays database environment information.
  • DBA_HIST_SNAPSHOT – Displays snapshot information.
  • DBA_HIST_SQL_PLAN – Displays SQL execution plans.
  • DBA_HIST_WR_CONTROL – Displays AWR settings.

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:

trivadis.com

OracleBase

OracleUtilities.com

Jonathan Lewis