How to Generate an AWR Report and Create Baselines (文档 ID 748642.1)

In this Document

Goal

Solution

Running a Basic Report

Generating Various Types of AWR Reports

Various Useful Operations

How to Modify the AWR SNAPSHOT SETTINGS:

Creating a Baseline:

Dropping an AWR baseline:

Dropping the AWR snaps in range:

We can also specify a template for creating and dropping baseline based on repeating time periods:

Creating a SNAPSHOT Manually:

Workload Repository Views

Automation of AWR reports ?

License Needed to Use AWR

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
Oracle Server Enterprise Edition - From version 10.1.0.2 

GOAL

This article outlines how to generate an AWR report and create baselines.

SOLUTION

Automatic Workload Repository (AWR) is a collection of persistent system performance statistics owned by the SYS user. 
It resides in SYSAUX tablespace. 
By default snapshot are generated once every 60min and maintained for 8 days to ensure the capture of an entire week of performance data (7 days in Oracle 10g). 

An AWR report outputs a series of statistics based on the differences between snapshots that may be used to investigate performance and other issues.

Running a Basic Report

With appropriate licenses for AWR, you may generate an AWR report by executing
the following script and pick the two snapshots you want to use for the sample :

$ORACLE_HOME/rdbms/admin/awrrpt.sql

Depending on the reasons for collecting the report, the default can be used, or for a more focused view, a short 10-15 minute snapshot could be used.

You will also be asked for the format of the report (text or html) along with the report name.

Generating Various Types of AWR Reports

AWR reports can be generated by running various SQL scripts to satisfy various requirements. Each report is available in HTML or TXT format:

• awrrpt.sql 
  Displays various statistics for a range of snapshots Ids.
• awrrpti.sql 
  Displays statistics for a range of snapshot Ids on a specified database and instance.
• awrsqrpt.sql
  Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.
• awrsqrpi.sql 
  Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
• awrddrpt.sql
  Compares detailed performance attributes and configuration settings between two selected time periods.
• awrddrpi.sql 
  Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

 

Various Useful Operations

How to Modify the AWR SNAPSHOT SETTINGS:

BEGIN 
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( 
    retention => 43200,        -- Minutes (43200 = 30 Days). 
                               -- Current value retained if NULL. 
    interval  => 30);          -- Minutes. Current value retained if NULL.
END; 
/

Creating a Baseline:

BEGIN 
  DBMS_WORKLOAD_REPOSITORY.create_baseline ( 
    start_snap_id => 10,  
    end_snap_id   => 100, 
    baseline_name => 'AWR First baseline'); 
END; 
/

In 11g, there is a newly introduced procedure DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE that specifies a template for how baselines should be created for future time periods:

 

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name => 'MORNING',
template_name => 'MORNING',
expiration => NULL ) ;
END;
/

"expiration => NULL" means that this baseline will be kept forever.

Dropping an AWR baseline:

BEGIN
    DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
    baseline_name => 'AWR First baseline');
END;
/

You can also drop baseline from old databases:

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
cascade => FALSE, dbid => 3310949047);
END;
/

Dropping the AWR snaps in range:

BEGIN 
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range( 
(low_snap_id=>40, 
High_snap_id=>80); 
END; 
/

We can also specify a template for creating and dropping baseline based on repeating time periods:

 

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'MONDAY',
hour_in_day => 9,
duration => 3,
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name_prefix => 'MONDAY_MORNING'
template_name => 'MONDAY_MORNING',
expiration => 30 );
END;
/[Insert code here]

The baseline will be generated for the period from '&start_date_time' to '&end_date_time' every Monday.

Creating a SNAPSHOT Manually:

BEGIN 
  DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 
END; 
/

 

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.

Automation of AWR reports ?

There is no product functionality to produce the AWR reports in an automated way. In regards to the Oracle product, the production of a report is a manual step. However, one could use the dbms_workload_repository.awr_report_text in a (bash for instance for Unix like OS's) script. It would be scheduled (using the crontab for Unix like OS's) and be instructed with or determine the variables to provide to the awr_report_text function. There are such scripts available on the web, but not officially provided by Oracle as part of the product.

License Needed to Use AWR

Please remember that in order to use AWR license is needed.  Statspack can be used if AWR license is not acquired. 

REFERENCES

NOTE:228913.1 - Systemwide Tuning using STATSPACK Reports
NOTE:394937.1 - Statistics Package (STATSPACK) Guide

相关内容

产品

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)

关键字

AWR; DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE; MATERIALIZED VIEW; METRIC; PERFORMANCE STATISTICS; RUNS SQL SCRIPTS; SNAPSHOT