Concept:
Automatic Workload Repository (AWR) is a repository of historical performance data that includescumulative statistics for the system, sessions, individual SQL statements, segments, and services.These statistics are the foundation of performance tuning. By automating thegathering of database statistics for problem detection and tuning, AWR servesas the foundation for database self-management.
Thoery:
As shown in Figure 18–8, the databasestores recent AWR statistics in the SGA. By default, theMMON process gathers statistics every hour and creates an AWR snapshot .A snapshot is a set of performance statistics captured at a specific time. The database writes snapshots to the SYSAUX tablespace. AWR manages snapshot space, purgingolder snapshots according to a configurable snapshot retention policy.
An AWR baseline is a collection ofstatistic rates usually taken over a period when the system is performing wellat peak load. You can specify a pair or range of AWR snapshots as a baseline.By using an AWR report to compare statistics captured during a period of badperformance to a baseline, you can diagnose problems.
An automated maintenance infrastructureknown as AutoTask illustrates how Oracle Database uses AWR for self-management.By analyzing AWR data, AutoTask can determine the need for maintenance tasksand schedule them to run in Oracle Scheduler maintenance windows. Examples oftasks include gathering statistics for the optimizer and running the AutomaticSegment Advisor.
目前Oracle10g之后，AWR报告取代了原先的Statspack报告成为一个主流性能分析报告。通常可以从OEM（Oracle Enterprise Manager Console）平台上生成查看AWR报告。在OEM中，使用图形化方法更加容易。本篇中介绍使用手工脚本方式生成AWR的方法，脱离OEM的限制。
生成awr报告时需要注意的问题：
1.可能会出现ora-20200错误，因为数据库中途被关闭过，这时候需要找到中途没有被关闭的时间段（从日志文件中可以找出）
2.生成的文件位于当前的操作目录下，以我的为例就是C:\Users\lenovo
Start:
C:\Users\lenovo>sqlplus/ as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 12月 18 20:13:572013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
sys@ORCL>@D:\app\lenovo\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrrpt.sql
(awrrpt.sql位于
Current Instance
~~~~~~~~~~~~~~~~
DBId DB Name Inst Num Instance
----------- ------------ --------------------
1350043201 ORCL 1 orcl
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plaintext report
Enter 'html' for an HTML report, or 'text'for plain text
Defaults to 'html'
输入 report_type 的值:
Type Specified: html
Instances in this Workload Repositoryschema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DBId Inst Num DB Name Instance Host
------------ -------- ------------------------ ------------
* 1350043201 1 ORCL orcl LENOVO-PC
Using 1350043201 for database Id
Using 1 for instance number
Specify the number of days of snapshots tochoose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will resultin the most recent
(n) days of snapshots being listed. Pressing
specifying a number lists all completedsnapshots.
输入 num_days 的值: 7
Listing the last 7 days of CompletedSnapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------------------------- -----
orcl ORCL 856 12 12月 201300:00 1
857 13 12月 201316:43 1
858 13 12月 201318:00 1
859 13 12月 201319