Oracle AWR管理与维护

左手的ㄟ右手 2022-06-17 10:44 271阅读 0赞

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

## 一、快照管理 ##

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

### 1. 手动创建快照 ###

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

BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
    END;
    /
    
    SELECT snap_id, dbid, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY end_interval_time DESC;
    
       SNAP_ID       DBID BEGIN_INTERVAL_TIME          END_INTERVAL_TIME
    ---------- ---------- ---------------------------- --------------------------
           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 DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 162, high_snap_id => 164, dbid => 41924548);
    END;
    /

### 3. 修改快照的保留间隔 ###

--查看当前的保留策略，如下，为缺省值，采集间隔为1小时，保留8天
    SQL> select * from dba_hist_wr_control;
    
          DBID SNAP_INTERVAL        RETENTION          TOPNSQL
    ---------- -------------------- ------------------ ----------
      41924548 +00000 01:00:00.0    +00008 00:00:00.0  DEFAULT
    
    --下面将其修改为保留5天，采集间隔为30分钟,topnsql为50条
    BEGIN
      DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 10*24*60,
                     interval => 30, topnsql => 50, dbid => 41924548);
    END;
    /
    
    --查看修改后的结果
    SQL> select * from dba_hist_wr_control;
    
          DBID SNAP_INTERVAL        RETENTION          TOPNSQL
    ---------- -------------------- ------------------ ----------
      41924548 +00000 00:30:00.0    +00010 00:00:00.0  50

## 二、基线管理 ##

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

### 1. 创建基线 ###

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

BEGIN
        DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 160,
                       end_snap_id => 161, baseline_name => 'peak_baseline',
                       dbid => 41924548, expiration => 10);
    END;
    /
    
    SQL> SELECT baseline_id,
      2         baseline_name,
      3         baseline_type,
      4         expiration,
      5         creation_time
      6  FROM   dba_hist_baseline;
    
    BASELINE_ID BASELINE_NAME           BASELINE_TYPE EXPIRATION CREATION_
    ----------- ----------------------- ------------- ---------- ---------
              1 peak_baseline           STATIC                10 14-APR-17
              0 SYSTEM_MOVING_WINDOW    MOVING_WINDOW            07-APR-17
    
    --基于特定时间创建基线，如下示例
    BEGIN
       DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
          start_time      => TO_DATE ('2017-04-14 6:00:00', 'yyyy-mm-dd hh24:mi:ss'),
          end_time        => TO_DATE ('2017-04-14 8:00:00', 'yyyy-mm-dd hh24:mi:ss'),
          baseline_name   => 'peak_baseline2',
          expiration      => 10);
    END;
    /

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

### 2. 删除基线 ###

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

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

### 3. 基线重命名 ###

BEGIN
        DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
                       old_baseline_name => 'peak_baseline',
                       new_baseline_name => 'peak_mondays',
                       dbid => 41924548);
    END;
    /
    
    --验证结果
    SQL> SELECT baseline_id,
      2         baseline_name,
      3         baseline_type,
      4         expiration,
      5         creation_time
      6  FROM   dba_hist_baseline;
    
    BASELINE_ID BASELINE_NAME           BASELINE_TYPE EXPIRATION CREATION_
    ----------- ----------------------- ------------- ---------- ---------
              1 peak_mondays            STATIC                10 14-APR-17
              0 SYSTEM_MOVING_WINDOW    MOVING_WINDOW            07-APR-17

在上面的示例中，将基线的名称由peak\_baseline修改成peak\_mondays。

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

--查看缺省的window_size
    SELECT baseline_name, baseline_type, moving_window_size FROM dba_hist_baseline WHERE baseline_name = 'SYSTEM_MOVING_WINDOW';
    
    BASELINE_NAME            BASELINE_TYPE MOVING_WINDOW_SIZE
    ------------------------ ------------- ------------------
    SYSTEM_MOVING_WINDOW     MOVING_WINDOW                  8
    
    BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE ( window_size => 7, dbid => 41924548);
    END;
    /
    
    --window_size为天，只能够小于等于当前快照保留时间，否则报错，如下：
    
    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 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( start_time => TO_DATE ('2017-04-14 17:00:00', 'yyyy-mm-dd hh24:mi:ss'), end_time => TO_DATE ('2017-04-14 19:00:00', 'yyyy-mm-dd hh24:mi:ss'), baseline_name => 'baseline_140414', template_name => 'template_140414', expiration => 10, dbid => 41924548);
    END;
    /
    
    --如果创建基线样本时间小于当前时间，则收到如下错误
    ERROR at line 1:
    ORA-13537: invalid input for create baseline template (end_time, end_time is less than SYSDATE) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 768 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 818 ORA-06512: at line 2

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

*  创建重复基线样本

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

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
    
    BEGIN
       DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
          day_of_week            => 'monday',
          hour_in_day            => 9,
          duration               => 2,
          expiration             => 30,
          start_time             => '2017-04-14 09:00:00',
          end_time               => '2017-12-31 11:00:00',
          baseline_name_prefix   => 'baseline_2017_mondays_',
          template_name          => 'template_2017_mondays',
          dbid                   => 41924548);
    END;
    /
    
    -- Author     : Leshami
    -- Blog       : http://blog.csdn.net/leshami
    -- QQ(Weixin) : 645746311
    
    --查看已经创建的基线样本
    SQL> select t.template_name,
      2       t.template_type,
      3       t.start_time,
      4       t.end_time,
      5       t.day_of_week,
      6       t.hour_in_day,
      7       t.duration
      8  from dba_hist_baseline_template t;
    
    TEMPLATE_NAME         TEMPLATE_ START_TIME          END_TIME            DAY_OF_WE HOUR_IN_DAY DURATION
    --------------------- --------- ------------------- ------------------- --------- ----------- --------
    template_140414       SINGLE    2017-04-14 17:00:00 2017-04-14 19:00:00
    template_2017_mondays REPEATING 2017-04-14 09:00:00 2017-12-31 11:00:00 MONDAY             17        3

在上面的示例中我们创建了一个重复从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 DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE ( template_name => 'template_140414', dbid => 41924548);
    END;
    /

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

## 三、AWR空间占用 ##

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

SQL> @?/rdbms/admin/awrinfo.sql
    
    This script will report general AWR information ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    
    Specify the Report File Name ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    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: Using the report name awrinfo.txt
    No errors.                 -- Author : Leshami
    No errors.                 -- Blog   : http://blog.csdn.net/leshami
    ~~~~~~~~~~~~~~~            -- QQ     : 645746311 
    AWR INFO Report ~~~~~~~~~~~~~~~
    
    Report generated at
    19:48:53 on Apr 14, 2017 ( Friday ) in Timezone +08:00
    
    
    Warning: Non Default AWR Setting! --------------------------------------------------------------------------------
    Snapshot interval is 60 minutes and Retention is 8 days
    
    
     DB_ID DB Name HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR
    ------------ ------- ----------------------- ---- ----------------- ------------ ---
    * 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% )

\#\# 四、生成AWR报告

--单实例下生成AWR报告 SQL> @?/rdbms/admin/awrrpt.sql
    
    --RAC环境下生成AWR报告 SQL> @$ORACLE_HOME/rdbms/admin/awrgrpt.sql
    
    --指定数据库实例生成AWR报告 SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql
    
    --生成SQL语句AWR报告 SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
    
    --指定实例生成SQL语句AWR报告 SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
    
    --生成比较的AWR报告 SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql
    
    --RAC环境下生成比较的AWR报告 @$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

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

v$active\_session\_history : 显示活跃的数据库会话的活动，每秒采样一次

v$metric和v$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控制信息

## 六、更多参考 ##

[Oracle AWR特性描述][Oracle AWR]  
[Linux/Unix shell 自动发送AWR report][Linux_Unix shell _AWR report]  
[Linux/Unix shell 自动发送AWR report(二)][Linux_Unix shell _AWR report 1]

[![DBA牛鹏社(SQL/NOSQL/LINUX)][DBA_SQL_NOSQL_LINUX]][DBA_SQL_NOSQL_LINUX_DBA_SQL_NOSQL_LINUX]

--------------------

![这里写图片描述][1493001734_6055.jpg]

[Oracle AWR]: http://blog.csdn.net/leshami/article/details/70569966
[Linux_Unix shell _AWR report]: http://blog.csdn.net/leshami/article/details/8655991
[Linux_Unix shell _AWR report 1]: http://blog.csdn.net/leshami/article/details/8687690
[DBA_SQL_NOSQL_LINUX]: http://pub.idqqimg.com/wpa/images/group.png
[DBA_SQL_NOSQL_LINUX_DBA_SQL_NOSQL_LINUX]: /images/20220617/29850bc5b16a40c088c6ad0b6a4ab380.png
[1493001734_6055.jpg]: https://img-my.csdn.net/uploads/201704/24/1493001734_6055.jpg