Oracle AWR(Automatic Workload Repository)使用
一、AWR说明
AWR(Automatic Workload Repository)是Oracle的一个内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。
快照由一个称为MMON的后台进程及其从进程自动地每小时采集一次。为了节省空间，采集的数据在7天后自动清除。快照频率和保留时间都可以由用户修改。它产生两种类型的输出：文本格式和默认的HTML格式，HTML格式拥有到部分和子部分的所有超链接，从而提供了非常友好的用户报表。
AWR使用几个表来存储采集的统计数据。所有的表都存储在SYSAUX表空间中的SYS模式下，并且以WRM$_*和WRH$_*的格式命名。WRM$_*类型的表存储元数据信息（如检查的数据库和采集的快照），WRH$_*类型的表保存实际采集的统计数据。M代表metadata，元数据；H代表historaical，历史数据。
在这些表上构建了几种带前缀 DBA_HIST_ 的视图，这些视图可以用来编写您自己的性能诊断工具。视图的名称直接与表相关，如：视图DBA_HIST_SYSMETRIC_SUMMARY是在WRH$SYSMETRIC_SUMMARY表上构建的。
statistics_level参数：
statistics_level默认是TYPICAL，在10g中表监控是激活的，强烈建议在10g中此参数的值是TYPICAL。如果 statistics_level设置为BASIC，不仅不能监控表，而且将禁掉如下一些10g的功能：
ASH(Active Session History)
ASSM(Automatic Shared Memory Management)
AWR(Automatic Workload Repository)
ADDM(Automatic Database Diagnostic Monitor)
在Oracle 11gR2中禁用的功能如下：
STATICTICS_LEVEL specifies the level of collection for database and operating system statistics.The Oracle Database collects these statistics for a variety of purposes,including making self-management decisions.
The default setting of TYICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance.The default value should be adequate for most environments.
When the STATISTICS_LEVEL parameter is set to ALL,additional statistics are added to the set of statistics collected with the TYPICAL setting.The additional statistics are timed OS statistics and plan execution statistics.
Setting the STATICTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality,including:
l Automatic Workload Repository(AWR) Snapshots
l Automatic Database Diagnostic Monitor(ADDM)
l All server-generated alerts
l Automatic SGA Memory Management
l Automatic optimizer statistics collection
l Object level statics
l End to End Application Tracing (V$CLIENT_STATS)
l Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
l Service level statistics
l Buffer cache advisory
l MTTR advisory
l Shared pool sizing advisory
l Segment level statistics
l PGA Target advisory
l Timed statistics
l Monitorying of statistics
二、AWR使用
产生整个数据库的AWR报告，运行脚本awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
产生某个实例的AWR报告，运行脚本awrrpti.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
产生某条SQL语句的AWR报告，运行脚本awrsqrpt.sql
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
例：
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
823279453 RACDB 1 RACDB1
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 823279453 1 RACDB RACDB1 rac1
823279453 2 RACDB RACDB2 rac2
823279453 3 RACDB RACDB3 rac3
Using 823279453 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being
listed. Pressing
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
RACDB1 RACDB 112 16 Oct 2013 09:56 1
113 16 Oct 2013 11:00 1
114 16 Oct 2013 12:00 1
115 16 Oct 2013 13:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 113
Begin Snapshot Id specified: 113
Enter value for end_snap: 115
End Snapshot Id specified: 115
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_113_115.html. To use this name,
press
Enter value for report_name: /home/oracle/awrrpt_1_113_115.html
Report written to /home/oracle/awrrpt_1_113_115.html
三、AWR操作
1、查看当前的AWR保存策略
SQL> col snap_interval format a20
SQL> col retention format a20
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----
823279453 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
以上结果表示，每小时产生一个snapshot，保留7天。
2、调整AWR配置
AWR配置都是通过dbms_workload_repository包进行配置。
（1）、调整AWR产生snapshot的频率和保留策略。
如，将收集间隔时间改为30分钟一次，并且保留5天时间（单位都是分钟）：
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>5*24*60);
PL/SQL procedure successfully completed.
（2）、关闭AWR，interval设为0则关闭自动捕捉快照
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
PL/SQL procedure successfully completed.
（3）、手工创建一个快照
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
（4）、查看快照
SQL> select * from sys.wrh$_active_session_history;
（5）、手工删除指定范围的快照
（6）、创建baseline，保存这些数据用于将来分析和比较
查看baseline:
select * from dba_hist_baseline
（7）、删除baseline
SQL> exec dbms_workload_repository.drop_baseline(baseline_name => 'oltp_peakload_b1',cascade => false);
cascade指定为false，只删除该baseline；
cascade指定为true，删除该baseline的同时，删除和该baseline相关的snapshots。
（8）、将AWR数据导出并迁移到其它数据库以便以后分析
SQL> exec dbms_swrf_internal.awr_extract(dmpfile => 'awr_data.dmp', dmpdir => 'DATA_PUMP_DIR', bid =
> 70, eid => 80);
其中，dmpdir指定的目录为目录对象。
查看目录对象：select * from dba_directories;
创建目录对象：
SYS@ tsid > create directory dump_dir1 as 'E:\oracle\product\10.2.0\db_1\RDBMS\log';
Directory created.
删除目录对象：
SYS@ tsid > drop directory dump_dir1;
Directory dropped.
（9）、迁移AWR数据文件到其它数据库
SQL> exec dbms_swrf_internal.awr_load(schema => ‘AWR_TEST’, dmpfile => ‘awr_data.dmp’, dmpdif => ‘DATA_PUMP_DIR’);
把AWR数据转移到SYS模式中：
SQL> exec dbms_swrf_internal.move_to_awr(schema => ‘TEST’);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25744374/viewspace-774618/ ，如需转载，请注明出处，否则将追究法律责任。
