Oracle AWR管理与维护

AWR是Automatic Workload Repository的简称，中文叫着自动工作量资料档案库。对于AWR的管理，主要是针对快照和基线的管理而言。比如设定快照的间隔，删除快照，设定快照的保留时间。那对于基线而言则是基线的创建，删除，自动创建等。本文主要描述这2部分的内容。

一、快照管理

默认情况下，Oracle数据库每小时生成一次快照，并在工作负载库中保留8天的统计信息。必要时，你可以使用dbms_workload_repository程序手动创建，删除或者修改快照等。可以使用OEM以及dbms_workload_repository包管理快照。

1. 手动创建快照

使用以下过程手工创建快照，创建完成后可在数据字典DBA_HIST_SNAPSHOT查看所有快照相关信息。

BEGIN@H_404_13@ DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();@H_404_13@
END@H_404_13@;@H_404_13@
/

SELECT@H_404_13@ snap_id,dbid,begin_interval_time,end_interval_time FROM@H_404_13@ dba_hist_snapshot ORDER@H_404_13@ BY@H_404_13@ end_interval_time DESC@H_404_13@;@H_404_13@

   SNAP_ID       DBID BEGIN_INTERVAL_TIME          END_INTERVAL_TIME
---------- ---------- ---------------------------- --------------------------@H_404_13@
       164   41924548 14-APR-17 09.38.19.467 AM    14-APR-17 10.51.21.886 AM
       163   41924548 14-APR-17 09.00.10.470 AM    14-APR-17 09.38.19.467 AM
       162   41924548 14-APR-17 08.00.07.242 AM    14-APR-17 09.00.10.470 AM
       161   41924548 14-APR-17 07.00.04.120 AM    14-APR-17 08.00.07.242 AM

2. 删除快照

以下过程将删除snap_id从162到164的快照。在未指定41924548时，使用当前缺省的数据库

BEGIN@H_404_13@ DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 162@H_404_13@,high_snap_id => 164@H_404_13@,dbid => 41924548@H_404_13@);@H_404_13@
END@H_404_13@;@H_404_13@
/

3. 修改快照的保留间隔

-@H_404_13@-@H_404_13@查看当前的保留策略，如下，为缺省值，采集间隔为1小时，保留8天@H_404_13@
sql@H_404_13@> select@H_404_13@ *@H_404_13@ from@H_404_13@ dba_hist_wr_control;@H_404_13@

      DBID@H_404_13@ SNAP_INTERVAL@H_404_13@        RETENTION@H_404_13@          TOPNsql@H_404_13@
-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@ -@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@ -@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@ -@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@
  41924548@H_404_13@ +@H_404_13@00000@H_404_13@ 01:00:00@H_404_13@.@H_404_13@0@H_404_13@    +@H_404_13@00008@H_404_13@ 00:00:00@H_404_13@.@H_404_13@0@H_404_13@  DEFAULT@H_404_13@

-@H_404_13@-@H_404_13@下面将其修改为保留5天，采集间隔为30分钟@H_404_13@,@H_404_13@topnsql为50条@H_404_13@
BEGIN@H_404_13@
  DBMS_WORKLOAD_REPOSITORY@H_404_13@.@H_404_13@MODIFY_SNAPSHOT_SETTINGS(@H_404_13@ retention@H_404_13@ =@H_404_13@> 10*24*60@H_404_13@,@H_404_13@
                 interval@H_404_13@ =@H_404_13@> 30@H_404_13@,@H_404_13@ topnsql@H_404_13@ =@H_404_13@> 50@H_404_13@,@H_404_13@ dbid@H_404_13@ =@H_404_13@> 41924548);@H_404_13@
END;@H_404_13@
/@H_404_13@

-@H_404_13@-@H_404_13@查看修改后的结果@H_404_13@
sql@H_404_13@> select@H_404_13@ *@H_404_13@ from@H_404_13@ dba_hist_wr_control;@H_404_13@

      DBID@H_404_13@ SNAP_INTERVAL@H_404_13@        RETENTION@H_404_13@          TOPNsql@H_404_13@
-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@ -@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@ -@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@ -@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@-@H_404_13@
  41924548@H_404_13@ +@H_404_13@00000@H_404_13@ 00:30:00@H_404_13@.@H_404_13@0@H_404_13@    +@H_404_13@00010@H_404_13@ 00:00:00@H_404_13@.@H_404_13@0@H_404_13@  50@H_404_13@

二、基线管理

AWR基线可以手工创建和删除，也可以自动创建和删除。对于自动创建基线则是首先要创建基线样本才能实现自动创建基线。主要描述如下。

1. 创建基线

基线的创建依赖于快照，因此在创建之前应该得到所需时间范围的snap_id，可以通过查询数据字典DBA_HIST_SNAPSHOT获得，同时在基线创建完成后可以从数据字典DBA_HIST_BASELINE查询基线相关信息。

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 160@H_404_13@,end_snap_id => 161@H_404_13@,baseline_name => 'peak_baseline'@H_404_13@,dbid => 41924548@H_404_13@,expiration => 10@H_404_13@);
END;
/

sql> SELECT baseline_id,2@H_404_13@         baseline_name,3@H_404_13@         baseline_type,4@H_404_13@         expiration,5@H_404_13@         creation_time
  6@H_404_13@  FROM   dba_hist_baseline;

BASELINE_ID BASELINE_NAME           BASELINE_TYPE EXPIRATION CREATION_
----------- ----------------------- ------------- ---------- ---------
          1@H_404_13@ peak_baseline           STATIC@H_404_13@                10@H_404_13@ 14@H_404_13@-APR-17@H_404_13@
          0@H_404_13@ SYSTEM_MOVING_WINDOW    MOVING_WINDOW            07@H_404_13@-APR-17@H_404_13@

--基于特定时间创建基线，如下示例
BEGIN
   DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
      start_time      => TO_DATE ('2017-04-14 6:00:00'@H_404_13@,'yyyy-mm-dd hh24:mi:ss'@H_404_13@),end_time        => TO_DATE ('2017-04-14 8:00:00'@H_404_13@,baseline_name   => 'peak_baseline2'@H_404_13@,expiration      => 10@H_404_13@);
END;
/

在上面的示例中，创建了一个范围为160-161的，名字为peak_baseline的基线，保留时间为10天。在超过10天后，该基线会被删除，相应的快照也被删除。如果为指定失效期，则该基线以及相应的快照被永久保留。

2. 删除基线

BEGIN@H_404_13@ DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak_baseline'@H_404_13@,cascade@H_404_13@ => FALSE@H_404_13@,dbid => 41924548@H_404_13@);@H_404_13@
END@H_404_13@;@H_404_13@
/

在上面的示例中将删除名为peak_baseline的基线，同时指定了cascade为false，也就是说在删除基线的同时，不删除相应的快照。

3. 基线重命名

BEGIN@H_404_13@
    DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
                   old_baseline_name => 'peak_baseline@H_404_13@',new_baseline_name => 'peak_mondays@H_404_13@',dbid => 41924548@H_404_13@);
END@H_404_13@;
/

--验证结果@H_404_13@
sql> SELECT@H_404_13@ baseline_id,5@H_404_13@         creation_time
  6@H_404_13@  FROM   dba_hist_baseline;

BASELINE_ID BASELINE_NAME           BASELINE_TYPE EXPIRATION CREATION_
----------- ----------------------- ------------- ---------- ---------@H_404_13@
          1@H_404_13@ peak_mondays            STATIC                10@H_404_13@ 14@H_404_13@-APR-17@H_404_13@
          0@H_404_13@ SYSTEM_MOVING_WINDOW    MOVING_WINDOW            07@H_404_13@-APR-17@H_404_13@

在上面的示例中，将基线的名称由peak_baseline修改成peak_mondays。

4. 修改缺省移动窗口基线保留值

--查看缺省的window_size@H_404_13@
SELECT@H_404_13@ baseline_name,baseline_type,moving_window_size FROM@H_404_13@ dba_hist_baseline WHERE@H_404_13@ baseline_name = 'SYSTEM_MOVING_WINDOW'@H_404_13@;@H_404_13@

BASELINE_NAME            BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------ ------------- ------------------@H_404_13@
SYSTEM_MOVING_WINDOW     MOVING_WINDOW                  8

BEGIN@H_404_13@ DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE ( window_size => 7@H_404_13@,dbid => 41924548@H_404_13@);@H_404_13@
END@H_404_13@;@H_404_13@
/

--window_size为天，只能够小于等于当前快照保留时间，否则报错，如下：@H_404_13@

ERROR at line 1:
ORA-13541: system moving window baseline size (864000)
greater than retention (691200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY",line 686
ORA-06512: at line 2

5. 管理基线样本

创建单个基线模板

BEGIN@H_404_13@ DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( start_time => TO_DATE ('2017-04-14 17:00:00'@H_404_13@,end_time => TO_DATE ('2017-04-14 19:00:00'@H_404_13@,baseline_name => 'baseline_140414'@H_404_13@,template_name => 'template_140414'@H_404_13@,expiration => 10@H_404_13@,dbid => 41924548@H_404_13@);@H_404_13@
END@H_404_13@;@H_404_13@
/

--如果创建基线样本时间小于当前时间，则收到如下错误@H_404_13@
ERROR at line 1:
ORA-13537: invalid input for create@H_404_13@ baseline template (end_time,end_time is@H_404_13@ less than SYSDATE) ORA-06512@H_404_13@: at@H_404_13@ "SYS.DBMS_WORKLOAD_REPOSITORY"@H_404_13@,line 768@H_404_13@ ORA-06512@H_404_13@: at@H_404_13@ "SYS.DBMS_WORKLOAD_REPOSITORY"@H_404_13@,line 818@H_404_13@ ORA-06512@H_404_13@: at@H_404_13@ line 2@H_404_13@@H_404_13@

在上面的示例中，我们创建了一个单一的基线样本，并且指定了相应的时间范围，基线的名称及保留期限等。那么在这个时间范围内的相应的快照会被保留，同时这个基线可以用于后续在发现性能问题的时候进行比对。

创建重复基线样本

重复的基线样本指的是在将来某个特定的时间范围内，Oracle会参照这个设定的样本自动为我们创建基线。比如，可以创建一个重复的基线样本，使得在2017年每周一9:00-11:00自动生成基线。

sql> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'@H_404_13@;

BEGIN
   DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
      day_of_week            => 'monday'@H_404_13@,hour_in_day            => 9@H_404_13@,duration               => 2@H_404_13@,expiration             => 30@H_404_13@,start_time             => '2017-04-14 09:00:00'@H_404_13@,end_time               => '2017-12-31 11:00:00'@H_404_13@,baseline_name_prefix   => 'baseline_2017_mondays_'@H_404_13@,template_name          => 'template_2017_mondays'@H_404_13@,dbid                   => 41924548@H_404_13@);
END;
/

-- Author     : Leshami
-- Blog       : http://blog.csdn.net/leshami@H_404_13@
-- QQ(Weixin) : 645746311@H_404_13@

--查看已经创建的基线样本
sql> select t.template_name,2@H_404_13@       t.template_type,3@H_404_13@       t.start_time,4@H_404_13@       t.end_time,5@H_404_13@       t.day_of_week,6@H_404_13@       t.hour_in_day,7@H_404_13@       t.duration
  8@H_404_13@  from dba_hist_baseline_template t;

TEMPLATE_NAME         TEMPLATE_ START_TIME          END_TIME            DAY_OF_WE HOUR_IN_DAY DURATION
--------------------- --------- ------------------- ------------------- --------- ----------- --------
template_140414       SINGLE    2017@H_404_13@-04@H_404_13@-14@H_404_13@ 17@H_404_13@:00@H_404_13@:00@H_404_13@ 2017@H_404_13@-04@H_404_13@-14@H_404_13@ 19@H_404_13@:00@H_404_13@:00@H_404_13@
template_2017_mondays REPEATING 2017@H_404_13@-04@H_404_13@-14@H_404_13@ 09@H_404_13@:00@H_404_13@:00@H_404_13@ 2017@H_404_13@-12@H_404_13@-31@H_404_13@ 11@H_404_13@:00@H_404_13@:00@H_404_13@ MONDAY             17@H_404_13@        3@H_404_13@

在上面的示例中我们创建了一个重复从2017年4月14日起的每周一(day_of_week)会自动生成一个基线，其开始时间为9点(hour_in_day)，其持续时间为2小时(duration)，有效期为30天(expiration)，整个基线的起止时间范围为：2017-04-14 09:00:00至2017-12-31 11:00:00，同时也指定了基线样本的名称以及基线前缀名称。

基线样本的删除

BEGIN@H_404_13@ DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE ( template_name => 'template_140414'@H_404_13@,dbid => 41924548@H_404_13@);@H_404_13@
END@H_404_13@;@H_404_13@
/

在上面的示例中我们删除之前创建的基线样本，通过指定样本名称即可。

三、AWR空间占用

AWR快照的数据全部填充在SYSAUX表空间。因此如果要保留的快照越久，意味着需要占用的SYSAUX空间越大。同时可以通过awrinfo.sql查看各个对象占用SYSAUX的详细信息。

sql> @?/rdbms/admin/awrinfo.sql

This script will report general AWR information ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~@H_404_13@


Specify the Report File Name ~~~~~~~~~~~~~~~~~~~~~~~~~~~~@H_404_13@
The default report file name is awrinfo.txt.  To use this name,press <return> to continue,otherwise enter an alternative.

Enter value for report_name: @H_404_13@Using the report name awrinfo.txt
No errors.                 -- Author : Leshami
No errors.                 -- Blog   : http://blog.csdn.net/leshami
~~~~~~~~~~~~~~~            -- QQ     : 645746311 
AWR INFO Report ~~~~~~~~~~~~~~~@H_404_13@

Report generated at
19:48:53 on Apr 14,2017 ( Friday ) in Timezone +08:00


Warning: Non Default AWR Setting! --------------------------------------------------------------------------------@H_404_13@
Snapshot interval is 60 minutes and Retention is 8 days


 DB_ID DB Name HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR@H_404_13@
------------ @H_404_13@------- ----------------------- ---- ----------------- ------------ ---
* @H_404_13@41924548   ORA11G  ydq - Linux x86 64-bit     1 15:18:26 (04/07)        617410 NO

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size                        617.4 MB ( 2% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema  SYS          occupies            206.6 MB (  33.5% )
| Schema  XDB          occupies            157.7 MB (  25.5% )
| Schema  APEX_030200 occupies 85.4 MB ( 13.8% ) | Schema MDSYS occupies 73.9 MB ( 12.0% )@H_404_13@

## 四、生成AWR报告

--单实例下生成AWR@H_404_13@报告 @H_404_13@sql> @?/rdbms/admin/awrrpt.sql

--RAC@H_404_13@环境下生成AWR@H_404_13@报告 @H_404_13@sql> @$ORACLE_HOME/rdbms/admin/awrgrpt.sql

--指定数据库实例生成AWR@H_404_13@报告 @H_404_13@sql> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

--生成sql@H_404_13@语句AWR@H_404_13@报告 @H_404_13@sql> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

--指定实例生成sql@H_404_13@语句AWR@H_404_13@报告 @H_404_13@sql> @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

--生成比较的AWR@H_404_13@报告 @H_404_13@sql> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql

--RAC@H_404_13@环境下生成比较的AWR@H_404_13@报告 @H_404_13@@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

五、AWR相关的重要视图和数据字典

v$@H_404_13@active_session_history : 显示活跃的数据库会话的活动，每秒采样一次

v$@H_404_13@metric和v$@H_404_13@metric_history：

提供度量数据来跟踪系统性能。视图被组织成好几个组，这些组定义在v$metricgroup视图中

DBA_HIST_ACTIVE_SESS_HISTORY

展示内存中活动会话历史信息

DBA_HIST_BASELINE
展示捕获的基线的信息

DBA_HIST_BASELINE_DETAILS

展示特定基线的明细信息

DBA_HIST_BASELINE_TEMPLATE

基线模板相关信息

DBA_HIST_DATABASE_INSTANCE

数据库环境

DBA_HIST_DB_CACHE_ADVICE

根据历史数据预测在不同的cache size下的物理读

DBA_HIST_DISPATCHER

每个snapshot下调度进程的信息

DBA_HIST_DYN_REMASTER_STATS

动态remastering进程的统计信息

DBA_HIST_IOSTAT_DETAIL
按未见类型和功能来统计的历史I/O信息

DBA_HIST_SHARED_SERVER_SUMMARY

共享服务器的统计信息

DBA_HIST_SNAPSHOT

快照信息

DBA_HIST_sql_PLAN

执行计划

DBA_HIST_WR_CONTROL

AWR控制信息