DBA_Oracle AWR Report性能监控报表（案例）

2014-08-22 Created By BaoXinjian

一、摘要

Oracle Database 10g 提供了一个显著改进的工具：自动工作负载信息库 (AWR:Automatic Workload Repository)。

Oracle 建议用户用这个取代 Statspack。

1. AWR 实质上是一个 Oracle 的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。

快照由一个称为 MMON 的新的后台进程及其从进程自动采集数据。

       $ ps -ef|grep mmon

       oracle   12861     1  0 Oct09 ?        00:02:08 ora_mmon_survey

2. 10g中一个新视图v$session_wait_history这个视图保存了每个活动session在v$session_wait中最近10次的等待事件。

但这对于一段时期内的数据性能状况的监测是远远不够的，为了解决这个问题，在10g中还新添加了一个视图：v$active_session_history，这就是ASH，ASH缺省每一秒收集一下v$session中活动会话的情况，记录会话等待的事件，不活动的会话不会被采样 ,间隔时间由 _ash_sampling_interval 参数确定 ，由于记录session的活动信息是很费时间和空间的，ASH采用的策略是：保存处于等待状态的活动session的信息，每秒从v$session_wait中采样一次，并将采样信息保存在内存中(ASH的采样数据是保存在内存中)

3. ASH采样

ASH的采样数据是保存在内存中，而分配给ASH的内存空间是有限的，当所分配空间占满后，旧的记录就会被覆盖掉；而且数据库重启后，所有的这些ASH信息都会消失。

这样，对于长期检测oracle的性能是不可能的，在Oracle10g中，提供了永久保留ASH信息的方法，这就是AWR, 由于全部保存ASH中的信息是非常耗费时间和空间的，

所以AWR采用的策略是：MMON进程每小时对ASH 进行采样一次，并将信息保存到磁盘中，如内存不足，ASH BUFFER满的话MMNL进程就会主动写出，并保留7天，7天后旧的记录才会被覆盖。这些采样信息被保存在表

SELECT pool, name, bytes/1024/1024 FROM v$sgastat where name like '%ASH %';

其实，AWR记录的信息不仅是ASH，还可以收集到数据库运行的各方面统计信息和等待信息，用以诊断分析。AWR的采样方式是，以固定的时间间隔为其所有重要的统计信息和负载信息执行一次采样，并将采样信息保存在AWR中。可以这样说：ASH中的信息被保存到了AWR中的视图wrh$_active_session_history中。ASH是AWR的真子集。

AWR 用几个表来存储采集的性能统计数据，所有的表都存储在 SYSAUX 表空间中的 SYS 模式下，并且以 WRM$_*(5个） 和 WRH$_*（94个）的格式名。

WRM$_*这种类型存储元数据信息（如检查的数据库和采集的快照）， WRH$_*这种类型保存实际采集的统计数据。

H代表“历史数据 (historical)”，而 M 代表“元数据 (metadata)”。

在这些表上构建了几种带前缀 DBA_HIST_ 的视图（dba_hist_snapshot,dba_hist_baseline等），这些视图可以用来编写您自己的性能诊断工具。

为了节省空间，系统默认采集的数据在 7 天后自动清除。快照频率和保留时间都可以由用户修改：

4. 生成awr报告($ORACLE_HOME/rdbms/admin/)：

    awrrpt.sql ：生成指定快照区间的统计报表；

    awrrpti.sql ：生成指定数据库实例，并且指定快照区间的统计报表；

    awrsqlrpt.sql ：生成指定快照区间，指定SQL语句(实际指定的是该语句的SQLID)的统计报表；

    awrsqrpi.sql ：生成指定数据库实例，指定快照区间的指定SQL语句的统计报表；

    awrddrpt.sql ：指定两个不同的时间周期，生成这两个周期的统计对比报表；

二.  AWR 操作

2.1. 查看当前的AWR保存策略

SQL> select * from dba_hist_wr_control;

3.2. 调整AWR配置

AWR配置都是通过dbms_workload_repository包进行配置。

2.2.1 调整AWR产生snapshot的频率和保留策略，如将收集间隔时间改为30 分钟一次。并且保留5天时间（单位都是分钟）：

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

2.2.2 关闭AWR，把interval设为0则关闭自动捕捉快照

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

2.2.3 手工创建一个快照

SQL> exec dbms_workload_repository.create_snapshot();

2.2.4 查看快照

SQL> select * from sys.wrh$_active_session_history

2.2.5 手工删除指定范围的快照

SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 973, high_snap_id => 999, dbid => 262089084);

2.2.6 创建baseline，保存这些数据用于将来分析和比较

SQL> exec dbms_workload_repository.create_baseline(start_snap_id => 1003, end_snap_id => 1013, 'apply_interest_1');

2.2.7 删除baseline

SQL> exec dbms_workload_repository.drop_baseline(baseline_name => 'apply_interest_1', cascade => FALSE);

2.2.8 将AWR数据导出并迁移到其它数据库以便于以后分析

SQL> exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(dmpfile => 'awr_data.dmp', mpdir => 'DIR_BDUMP', bid => 1003, eid => 1013);

2.2.9 迁移AWR数据文件到其他数据库

SQL> exec DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME => 'AWR_TEST', dmpfile => 'awr_data.dmp', dmpdir => 'DIR_BDUMP');

把AWR数据转移到SYS模式中：

SQL> exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');

三、案例 - 创建快照信息并导出

案例： 手工创建两个snapshot，并导入这两个节点之间的快照

1. 配置AWR配置信息,调整AWR产生snapshot的频率和保留策略

AWR配置都是通过dbms_workload_repository包进行配置，如将收集间隔时间改为30 分钟一次，并且保留31天时间（单位都是分钟）：

SQL> exec dbms_workload_repository.modify_snapshot_settings (interval=>30, retention=>31*24*60);

2. 关闭AWR:

把interval设为0则关闭自动捕捉快照:

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

3. 手工创建一个快照:

SQL> exec dbms_workload_repository.create_snapshot();

如果数据库的某些参数或应用程序做了调整，可以手工即时的创建一个快照更有利于数据的统计分析。

4. 查看快照：

SQL> select * from sys.wrh$_active_session_history;

5. 手工删除指定范围的快照

SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 4, high_snap_id => 5, dbid => 1368620684);

6. 生成awr报告（$ORACLE_HOME/rdbms/admin）：

    awrrpt.sql：生成指定快照区间的统计报表；

    awrrpti.sql：生成指定数据库实例，并且指定快照区间的统计报表；

    awrsqlrpt.sql：生成指定快照区间，指定SQL语句(实际指定的是该语句的SQLID)的统计报表；

    awrsqrpi.sql：生成指定数据库实例，指定快照区间的指定SQL语句的统计报表；

    awrddrpt.sql：指定两个不同的时间周期，生成这两个周期的统计对比报表；

四. AWR 报告分析

案例：

4.1 SQL ordered by Elapsed Time

记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和，而不是单次SQL执行时间 Elapsed Time = CPU Time + Wait Time)。

Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = CPU Time + Wait Time

CPU Time(s): 为SQL语句执行时CPU占用时间总时长，此时间会小于等于Elapsed Time时间。单位时间为秒。

Executions: SQL语句在监控范围内的执行次数总计。

Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。

% Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。

SQL ID: SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID的地方。

SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。

SQL Text: 简单的sql提示，详细的需要点击SQL ID。

4.2 SQL ordered by CPU Time

记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的执行占CPU时间总和，而不是单次SQL执行时间)。

4.3 SQL ordered by Gets

记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和，而不是单次SQL执行所占的Gets)。

4.4 SQL ordered by Reads:

记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。

4.5 SQL ordered by Executions:

记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。

4.6 SQL ordered by Parse Calls:

记录了SQL的软解析次数的TOP SQL。说到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。

4.7 SQL ordered by Sharable Memory:

记录了SQL占用library cache的大小的TOP SQL。Sharable Mem (b)：占用library cache的大小，单位是byte。

4.8 SQL ordered by Version Count:

记录了SQL的打开子游标的TOP SQL。

4.9 SQL ordered by Cluster Wait Time:

记录了集群的等待时间的TOP SQL

Thanks and Regards