AWR、ASH、ADDM和顾问程序

一、自动工作负荷知识库(AWR)

Oracle收集大量有关性能和活动的统计信息，这些信息在内存中累积，并定期写入到称之为自动工作负荷知识库(AWR)的表中。AWR作为数据库SYSAUX表空间的一组表和其他对象而存在，并存在于SYSMAN模式中。

统计信息的收集级别由实例参数statistics_level控制，该参数可以设置为BASIC、TYPICAL(默认)、ALL：

show parameter statistics_level;

NAME TYPE VALUE

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

statistics_level string TYPICAL

TYPICAL收集正常调整所需的所有统计信息，同时不会收集对性能有不利影响的统计信息。BASIC几乎禁用收集统计信息，并且不存在可评估的性能优势。ALL级别会收集与SQL语句执行相关的极其详细的统计信息，如果进行高级的SQL语句调整，可以使用该级别，但在收集统计信息时对性能稍有影响。

统计信息在SGA内存的数据结构中累积，定期(默认每小时一次)写入磁盘，也就是写入AWR，这称为一次AWR快照。AWR快照的采样和将统计信息写入磁盘的操作由后台进程MMON(可管理性监视器进程)完成。11g默认快照会保留存储8天，10g默认保留7天。

可以通过视图v$sysaux_occupants查看sysaux表空间中驻留的组件，可以查看AWR占用的空间大小：

col occupant_name for a30

select occupant_name, occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name='SM/AWR';

OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES

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

SM/AWR Server Manageability - Automatic Workload Repository 101120

AWR收集的信息通过一系列视图展现出来，可以查询这些视图获得数据库的信息采样，这些视图以dba_hist_打头。而这些视图的底层表大致有几类：

WRM$打头的表存储AWR的元数据;

WRH$打头的表存储采样快照的历史数据;

WRI$打头的表存储同数据库建议功能相关的数据;

WRR$打头的表代表的是Oracle 11g新功能Workload Capture以及Workload Replay的相关信息。

AWR的历史数据表主要通过分区表形式存储在SYSAUX表空间中，可以通过dba_tab_partitions视图进行查询。

当MMON进程保存AWR快照时，它会根据统计信息自动生成大量的指标。而创建基准必须由DBA完成。基准是快照的一对或多对，将一直保存到专门删除为止。可以比较从基准派生的指标与当前活动级别派生的指标，从而帮助确定活动和行为中的更改。可以为特定事件和普通运行创建基准。

Database Control在执行操作时需要调用PL/SQL包DBMS_WORKLOAD_REPOSITORY中的过程，这些过程可以调整快照的频率和持久性，生成即席快照，创建和操作基准，并生成任何两个快照之间的活动报告。

创建即席快照：

exec dbms_workload_repository.create_snapshot;

设置快照的保留时间和收集的时间间隔(单位分钟)，保存30天，每半小时收集一次：

exec dbms_workload_repository.modify_snapshot_settings(retention => 43200, interval => 30);

查看快照收集的时间间隔(默认1小时)、保留时间(11g默认8天，10g默认7天)：

col snap_interval for a30

col retention for a30

select dbid, snap_interval, retention from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION

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

2001528686 +00000 01:00:00.0 +00008 00:00:00.0

可以为某个运行良好的时段创建基线，以便和其它报告做对比，基线报告不会因过期而删除：

exec dbms_workload_repository.create_baseline(start_snap_id=>487, end_snap_id=>488, baseline_name=>'FridayPM');

查看AWR基线：

col baseline_name for a30

select dbid,

baseline_id,

baseline_name,

start_snap_id,

to_char(start_snap_time, 'yyyy-mm-dd hh34:mi:ss') start_snap_time,

end_snap_id,

to_char(end_snap_time, 'yyyy-mm-dd hh34:mi:ss') end_snap_time,

creation_time

from dba_hist_baseline;

DBID BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME CREATION_TIME

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

2001528686 1 baseline_214_215 214 2017-02-04 15:00:36 215 2017-02-04 15:41:41 2017-02-04 16:49:19

2001528686 0 SYSTEM_MOVING_WINDOW 155 2017-01-27 18:00:18 216 2017-02-04 17:00:03 2016-07-23 10:05:47

查询历史快照：

select dbid,

instance_number,

snap_id,

to_char(begin_interval_time, 'yyyy-mm-dd hh34:mi:ss') begin_interval_time

from dba_hist_snapshot

order by begin_interval_time desc;

DBID INSTANCE_NUMBER SNAP_ID BEGIN_INTERVAL_TIME

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

1903404692 1 31179 2017-02-04 15:00:22

1903404692 1 31178 2017-02-04 14:00:09

1903404692 1 31177 2017-02-04 13:00:56

1903404692 1 31176 2017-02-04 12:00:43

1903404692 1 31175 2017-02-04 11:00:30

1903404692 1 31174 2017-02-04 10:00:17

1903404692 1 31173 2017-02-04 09:00:04

1903404692 1 31172 2017-02-04 08:00:51

1903404692 1 31171 2017-02-04 07:00:38

可以看到快照默认每小时保存一次。

查询AWR历史快照的数量及其涉及的时间范围：

select dbid,

instance_number,

to_char(min(begin_interval_time), 'yyyy-mm-dd hh34:mi:ss') begin_time,

min(snap_id) begin_id,

to_char(max(begin_interval_time), 'yyyy-mm-dd hh34:mi:ss') end_time,

max(snap_id) end_id,

count(snap_id) amount

from dba_hist_snapshot

group by dbid, instance_number;

DBID INSTANCE_NUMBER BEGIN_TIME BEGIN_ID END_TIME END_ID AMOUNT

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

1903404692 1 2017-01-27 22:00:21 30994 2017-02-04 15:00:22 31179 186

删除基线，连同其快照一并删除：

exec dbms_workload_repository.drop_baseline(baseline_name => 'FridayPM', cascade => true);

删除快照：

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 487, high_snap_id => 488);

命令行生成AWR报告：

@?\rdbms\admin\awrrpt.sql

指定要显示最近几天的快照、选取用来分析的前后两个快照的ID、生成的AWR报告文件格式(默认为html)、报告的路径和文件名，默认的报告文件生成路径就是执行SQL*Plus所在的当前路径。

awrrpt.sql脚本实际上是调用了DBMS_WORKLOAD_REPOSITORY包来生成报表的，这个包中主要有两个函数用于生成报表：

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT，用于生成TEXT格式报表;

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML，用于生成HTML格式报表。

例如，通过以下查询方式也可以生成AWR报告，参数分别为数据库ID、实例编号、起始快照ID和结束快照ID：

select * from table(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(2896903393, 1, 230, 232));

还可以生成两个时段的AWR比较报告，便于对比两个不同时段的性能差异：

@?\rdbms\admin\awrddrpt.sql

Oracle允许将AWR数据导出并迁移到其他数据库便于以后分析。dbms_swrf_internal.awr_extract可以用来导出数据，awrextr.sql脚本就是用来完成该工作的，而导入工作可以通过dbms_swrf_internal.awr_load和dbms_swrf_internal.move_to_awr过程来完成，awrload.sql脚本用于完成该工作。

另外还有一个关于AWR数据存储和分布情况的报告，显示的是AWR自身的使用信息，包括快照信息、SYSAUX空间使用、AWR组件、ASH等信息：

@?\rdbms\admin\awrinfo.sql

还可以生成指定SQL语句的AWR报告，执行时需要提供SQL ID：

@?\rdbms\admin\awrsqrpi.sql

二、活动会话历史(ASH)

作为AWR的补充，还有一个ASH(Active Session History)，即活动会话历史。ASH以V$SESSION为基础，每秒采样一次，记录活动会话等待的历史事件。不活动的会话不会采样，采样工作由新引入的后台进程MMNL来完成，并在ASH Buffers写满之后，由该进程将ASH信息筛选过滤后通过直接路径插入写出到磁盘，从而最小化对数据库性能的影响。

MMNL是否将ASH信息写出磁盘受到一个隐含参数的控制：_ash_disk_write_enable，默认为True。而MMNL对ASH信息写出的比例则受另一个隐含参数控制：_ash_disk_filter_ratio，默认按10%的比例筛选过滤写出。

ASH buffers的最小值为1MB，最大值不超过30MB，在SGA内存中记录数据，期望值是记录一小时的内容。可以看到这部分内存分配在共享池中：

select * from v$sgastat where name = 'ASH buffers';

POOL NAME BYTES

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

shared pool ASH buffers 16252928

ASH功能是否启用，受一个内部隐藏参数的控制：_ash_enable，该参数为true时才能启用，默认为true。

ASH的采样时间同样受一个内部隐藏参数的控制：_ash_sampling_interval，默认为1000毫秒即一秒钟采样一次。

ASH记录的信息可以通过视图v$active_session_history来访问，对于每个活动session，默认每秒采样一次，每次采样会在这个视图中记录一行信息。该部分内容记录在内存中，期望值是记录一个小时的内容。该信息会被定期(每小时一次)刷新到AWR负载库中，并默认保留一周。内部表wrh$_active_session_history是视图v$active_session_history在AWR的存储地。dba_hist_active_sess_history是wrh$_active_session_history和其他几个视图的联合展现，通常可通过该视图进行AWR历史数据的访问。

要生成ASH报告，可在命令行执行以下脚本：

@?\rdbms\admin\ashrpt.sql

ASH报告包括了TOP等待事件、TOP SQL、TOP Sessions等内容，清晰扼要，简明易懂。但ASH内存记录数据始终是有限的，为了保存历史数据，我们需要AWR。

三、数据库顾问框架

1、自动数据库诊断监视器(ADDM)

Oracle数据库预配置了一组顾问，在这些顾问中，首先涉及的是Automatic Database Diagnostic Monitor(自动数据库诊断监视器，ADDM)。ADDM报告在保存AWR快照时自动生成，只要生成快照，MMON进程就会自动运行ADDM。自动生成的ADDM报告总是会包括当前快照与前一个快照之间的时间段，因此在默认情况下可以访问每小时的ADDM报告。也可以手动的调用ADDM生成包括任意两个快照之间时间段的报告。自动快照以及手动的收集快照都会触发ADDM。

ADDM报告默认在30天后清除。ADDM报告以DB时间作为衡量指标，DB时间包括花费在事务计算上的CPU时间和事务等待上的时间，即DBTime = DB CPU + Waiting Time，ADDM的核心就是减少DBTime，提高数据库系统的吞吐率。ADDM报告提示了系统存在的各类等待给数据库造成的时间消耗，并提出一些建议，比较明确直观。

命令行生成ADDM报告：

@?\rdbms\admin\addmrpt.sql

在这其中指定前后两个快照的ID、生成的ADDM报告的路径和文件名，报告是扩展名为LST的文本格式文件。

2、其它顾问程序

许多情况下，ADDM报告会建议运行一个或多个其他顾问。与ADDM相比，这些顾问能给出更准确的诊断信息和建议。这些顾问包括：

内存顾问

SQL访问、调整和修复顾问

自动撤销顾问

平均恢复时间顾问

数据恢复顾问

段顾问

3、自动维护作业

Oracle11g默认在数据库创建后，将在AutoTask系统中配置三项任务。这三项自动任务是：

收集优化器统计信息

运行段顾问

运行SQL调整顾问

AotoTask在调度程序的维护窗口中运行，默认方式下，维护窗口从工作日的22点开始运行4个小时，而在周六和周日，从早上6点开始运行20个小时。调度程序与资源管理器相关联，在维护窗口期间激活的资源管理器计划默认确保分配给AutoTask作业的计算机资源不超过总量的25%，以避免对其他工作造成负面影响。维护窗口的时间范围和最大资源使用量可以根据需要做调整。

要使任何AutoTask运行，必须将STATISTICS_LEVEL参数设置为TYPICAL(默认值)或ALL，设置为BASIC时是不会运行的。

段顾问任务依赖于通过日常运行的优化器统计收集任务构建的对象统计信息历史。SQL调整顾问依赖于MMON进程收集的AWR统计信息。