Oracle Database - AWR (Advanced|Automatic) - Workload Repository - Performance Statistics Gathering Tool - DBA_HIST views

> Database > Oracle Database

1 - About

(available since Oracle 10g)

AWR collects (through snapshots), processes, and maintains performance statistics.

Several tools such as ADDM (Automated Database Diagnostic Monitor), AWR Reports analyses the snapshots to perform performance analysis against a baseline snapshot.

Advertising

3 - Application Dependency

Most of the Oracle Database self-management functionality depend of AWR:

  • Automatic Database Diagnostic Monitor (ADDM)
  • SQL Tuning Advisor
  • Undo Advisor
  • Segment Advisor

4 - Configurations Parameters

4.1 - Snapshot

4.1.1 - Interval

Snapshots are made once every hour (snapshot interval) by default.

4.1.2 - Retention period

Snapshots are retained by default for 8 days (retention period) as you have normally two different workloads between the week and the weekend due to batch job.

The retention period must be large enough to capture at least one complete workload cycle. If there is a monthly workload peak, a retention period of one month may be necessary.

4.1.3 - TOPNSQL

TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, sharable Memory, and Version Count).

4.1.4 - Container Id

From 12g.

The ID of the container to which the data pertains. Possible values include:

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
  • 1: This value is used for rows containing data that pertain to only the root
  • n: Where n is the applicable container ID for the rows containing data
Advertising

4.2 - Baseline

4.2.1 - Window

The default window baseline value is the current AWR retention period.

5 - Space Consideration

With the default settings, a typical system with an average of 10 concurrent active sessions can require approximately 200 to 300 MB of space for its AWR data.

6 - How to

6.1 - Control

The details of the control view can be seen in the Oracle Database Reference

6.1.1 - see the snapshot parameters values

SELECT * FROM DBA_HIST_WR_CONTROL;
DBID SNAP_INTERVAL RETENTION TOPNSQL
3001565062 0 1:0:0.0 7 0:0:0.0 DEFAULT

6.1.2 - see the snapshots

SELECT * FROM DBA_HIST_SNAPSHOT

More details: DBA_HIST_SNAPSHOT

Advertising

6.1.3 - see baseline informations

  • baseline
SELECT * FROM DBA_HIST_BASELINE 
  • Baseline Metrics
DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRICS (
    baseline_name IN VARCHAR2,
             dbid IN NUMBER DEFAULT NULL,
     instance_num IN NUMBER DEFAULT NULL)
RETURN awr_baseline_metric_type_table PIPELINED;
  • Baseline template

See the table DBA_HIST_BASELINE_TEMPLATE

6.1.4 - DBA_HIST views

Historical data (snapshot) stored in AWR can be seen using the following DBA_HIST views:

  • DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history for recent system activity.
  • DBA_HIST_DATABASE_INSTANCE displays information about the database environment.
  • DBA_HIST_DB_CACHE_ADVICE displays historical predictions of the number of physical reads for the cache size corresponding to each row.
  • DBA_HIST_DISPATCHER displays historical information for each dispatcher process at the time of the snapshot.
  • DBA_HIST_DYN_REMASTER_STATS displays statistical information about the dynamic remastering process.
  • DBA_HIST_IOSTAT_DETAIL displays historical I/O statistics aggregated by file type and function.
  • DBA_HIST_SHARED_SERVER_SUMMARY displays historical information for shared servers, such as shared server activity, common queues and dispatcher queues.
  • DBA_HIST_SQL_PLAN displays the SQL execution plans.
6.1.4.1 - Get Sql Id
SELECT
        DBID
      , SQL_ID
      , SQL_TEXT
    FROM
        dba_hist_sqltext
    WHERE
        sql_text LIKE ‘%your query%’;
6.1.4.2 - DBA_HIST_SQLSTAT (Snaphost of SQL runtime statistics)
SELECT
        -- Snapshot Id
        SNAP_ID
        -- Sql Id
      , SQL_ID
      , SUM( CPU_TIME_DELTA ) CPU_TIME
      , SUM( ELAPSED_TIME_DELTA ) ELAPSED_TIME
      , SUM( EXECUTIONS_DELTA ) EXECUTIONS_DELTA
    FROM
        DBA_HIST_SQLSTAT
    GROUP BY
        SNAP_ID
      , SQL_ID;

where:

  • The total value is the value of the statistics since instance startup.
  • The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

6.2 - Manage

The DBMS_WORKLOAD_REPOSITORY package lets you manage the Workload Repository, performing operations such as managing snapshots and baselines.

The DBA role is required to invoke the DBMS_WORKLOAD_REPOSITORY procedures.

6.2.1 - Snapshot

  • Modify the snapshots parameters
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( 
    retention => 11520,
    INTERVAL => 60, 
    topnsql => 100, 
    dbid => 3310949047,
    CON_ID =>   ); -- From 12c
END;
/

where:

  • retention: is the retention period specified as minutes (8 days)
  • interval: is the interval between each snapshot specified as minutes (1 hour)
  • topnsql is the number of Top SQL to flush for each SQL criteria
  • dbid: is the database identifier
  • con_id: is the container id

To disable automatic snapshot collection, the snapshot interval must be set to 0.

  • create snapshots manually
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
-- sqlplus
exec dbms_workload_repository.create_snapshot;
  • delete snapshots:
BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (
                           low_snap_id => 22, 
                           high_snap_id => 32, 
                           dbid => 3310949047);
END;
/

6.2.2 - Baseline

  • Create Now
BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
                   start_snap_id => 270, 
                   end_snap_id => 280,
                   baseline_name => 'peak baseline', 
                   dbid => 3310949047,
                   expiration => 30);
END;
/
  • Create a baseline in the future with a baseline template (Once, no schedule)
BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
                   start_time => '2012-04-02 17:00:00 PST', 
                   end_time => '2012-04-02 20:00:00 PST', 
                   baseline_name => 'baseline_120402', 
                   template_name => 'template_120402', 
                   expiration => 30, 
                   dbid => 3310949047);
END;
/
-- a baseline template named template_120402 is created that will generate a baseline named baseline_120402 
-- for the time period from 5:00 p.m. to 8:00 p.m. on April 2, 2012 
-- on the database with a database ID of 3310949047. The baseline will expire after 30 days.
  • Create baseline in the future with a scheduled baseline template (repeating)
BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
                   day_of_week => 'monday', 
                   hour_in_day => 17,
                   duration => 3, 
                   expiration => 30,
                   start_time => '2012-04-02 17:00:00 PST', 
                   end_time => '2012-12-31 20:00:00 PST', 
                   baseline_name_prefix => 'baseline_2012_mondays_', 
                   template_name => 'template_2012_mondays',
                   dbid => 3310949047);
END;
/
  • Drop a baseline
BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
                  baseline_name => 'peak baseline',
                  cascade => FALSE, 
                  dbid => 3310949047);
END;
/
-- To drop the associated snapshots along with the baseline, set the cascade parameter to TRUE
  • Drop a baseline template
BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (
                   template_name => 'template_2012_mondays',
                   dbid => 3310949047);
END;
/
  • Renaming
BEGIN
    DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
                   old_baseline_name => 'peak baseline', 
                   new_baseline_name => 'peak mondays', 
                   dbid => 3310949047);
END;
/
  • Modify windows settings (The window size must be set to a value that is equal to or less than the value of the AWR retention setting.)
BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
                   window_size => 30, 
                   dbid => 3310949047);
END;
/

6.3 - Generate Report

You can either use OEM, which is the Enterprise Database Control to view the AWR snapshots, or generate AWR reports.

You can run awrrpti.sql script to generate the report.

%sqlplus / AS sysdba
SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

7 - Documentation / Reference