本文详细介绍了如何生成AWR报告和创建基线，包括自动工作负载仓库（AWR）的基本概念、报告生成、基线创建与管理、调整快照设置等操作。
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 laterInformation 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 :
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:
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:
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 10,
end_snap_id => 100,
baseline_name => 'AWR First baseline');
END;
/
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:
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:
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:
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:
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 ReportsNOTE:394937.1 - Statistics Package (STATSPACK) Guide
扫一扫
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
