Oracle AWR管理与快照操作完整指南

1. AWR简介与开启方法

AWR（Automatic Workload Repository）是Oracle数据库的性能诊断工具，它能自动收集、处理和维护性能统计信息。AWR默认情况下是开启的，但我们可以通过以下方式确认和管理其状态。

检查AWR状态

-- 检查AWR快照设置
SELECT * FROM DBA_HIST_WR_CONTROL;

-- 检查快照保留策略
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID;

开启/关闭AWR

-- 修改AWR快照设置（开启）
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 11520,    -- 保留时间（分钟），8天
    interval  => 60        -- 快照间隔（分钟）
  );
END;
/

-- 关闭AWR快照（不推荐）
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    interval  => 0         -- 设置为0关闭自动快照
  );
END;
/

2. AWR快照管理

手动创建快照

-- 创建手动快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 创建快照并返回快照ID
DECLARE
  snap_id NUMBER;
BEGIN
  snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
  DBMS_OUTPUT.PUT_LINE('创建的快照ID: ' || snap_id);
END;
/

查看快照详情

-- 查看所有快照信息
SELECT snap_id, 
       begin_interval_time, 
       end_interval_time,
       startup_time
FROM sys.wrm$_snapshot 
ORDER BY snap_id;

-- 查看最近24小时的快照
SELECT snap_id, 
       begin_interval_time, 
       end_interval_time
FROM sys.wrm$_snapshot 
WHERE begin_interval_time >= SYSDATE - 1
ORDER BY snap_id;

删除快照

-- 删除单个快照
EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 612, high_snap_id => 612);

-- 删除快照范围（如示例中的612到700）
EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 612, high_snap_id => 700);

-- 删除特定时间范围的快照
DECLARE
  min_snap_id NUMBER;
  max_snap_id NUMBER;
BEGIN
  SELECT MIN(snap_id), MAX(snap_id)
  INTO min_snap_id, max_snap_id
  FROM sys.wrm$_snapshot
  WHERE begin_interval_time BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') 
                              AND TO_DATE('2024-01-31', 'YYYY-MM-DD');
  
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
    low_snap_id => min_snap_id, 
    high_snap_id => max_snap_id
  );
END;
/

3. 生成AWR报告的方法

方法一：使用awrrpt.sql脚本（最常用）

-- 在SQL*Plus中执行
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- 或者指定完整路径
@?/rdbms/admin/awrrpt.sql

执行步骤：

1. 选择报告类型（HTML或TEXT）
2. 输入快照天数或直接按回车查看所有可用快照
3. 选择开始快照ID
4. 选择结束快照ID
5. 指定报告输出文件名

方法二：使用DBMS_WORKLOAD_REPOSITORY包

-- 生成HTML格式的AWR报告
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
  l_dbid => (SELECT dbid FROM v$database),
  l_inst_num => (SELECT instance_number FROM v$instance),
  l_bid => 612,        -- 开始快照ID
  l_eid => 700         -- 结束快照ID
));

-- 生成TEXT格式的AWR报告
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
  l_dbid => (SELECT dbid FROM v$database),
  l_inst_num => (SELECT instance_number FROM v$instance),
  l_bid => 612,
  l_eid => 700
));

方法三：生成指定时间段的AWR报告

-- 基于时间范围生成AWR报告
VARIABLE report_clob CLOB;

BEGIN
  :report_clob := DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    (SELECT dbid FROM v$database),
    (SELECT instance_number FROM v$instance),
    NULL,  -- 使用时间范围而不是快照ID
    TO_DATE('2024-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    TO_DATE('2024-01-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
  );
END;
/

-- 查看报告内容
SELECT :report_clob FROM DUAL;

方法四：保存AWR报告到文件

-- 使用SPOOL命令保存报告
SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000

SPOOL /tmp/awr_report_612_700.html

SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
  (SELECT dbid FROM v$database),
  (SELECT instance_number FROM v$instance),
  612,
  700
));

SPOOL OFF

4. 其他类型的AWR报告

生成比较AWR报告（AWR Diff Report）

-- 比较两个时间段的性能
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

-- 或者使用包
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(
  (SELECT dbid FROM v$database),
  (SELECT instance_number FROM v$instance),
  600, 610,  -- 第一个时间段
  700, 710   -- 第二个时间段
));

生成SQL报告

-- 针对特定SQL生成报告
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

生成Segment报告

-- 生成段级统计信息报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

5. AWR配置最佳实践

推荐配置

-- 设置合理的AWR参数
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 40320,    -- 28天保留期
    interval  => 60,       -- 每小时快照
    topnsql   => 100       -- 保留TOP 100 SQL
  );
END;
/

-- 对于高负载系统
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 20160,    -- 14天保留期
    interval  => 30,       -- 每30分钟快照
    topnsql   => 200
  );
END;
/

空间管理

-- 检查AWR空间使用情况
SELECT * FROM DBA_HIST_SNAPSHOT 
ORDER BY snap_id DESC;

-- 估算AWR空间需求
SELECT * FROM DBA_HIST_SNAPSHOT 
WHERE begin_interval_time >= SYSDATE - 7;

6. 实用监控脚本

快照监控

-- 监控快照生成频率
SELECT TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') as snapshot_time,
       snap_id,
       (end_interval_time - begin_interval_time) * 24 * 60 as duration_minutes
FROM sys.wrm$_snapshot 
WHERE begin_interval_time >= SYSDATE - 1
ORDER BY snap_id DESC;

-- 验证删除操作后的快照状态
SELECT snap_id, begin_interval_time, end_interval_time 
FROM sys.wrm$_snapshot 
ORDER BY snap_id;

查找可用快照

-- 查找特定时间段的快照
SELECT snap_id, 
       begin_interval_time, 
       end_interval_time
FROM dba_hist_snapshot
WHERE begin_interval_time BETWEEN SYSDATE - 7 AND SYSDATE
ORDER BY snap_id;

7. 注意事项

1. 权限要求：操作AWR需要DBA角色或相应的系统权限
2. 空间考虑：定期清理旧快照以避免SYSAUX表空间膨胀
3. 性能影响：频繁的快照可能对系统性能产生轻微影响
4. 业务时段：避免在业务高峰期进行大量快照删除操作
5. 报告解读：AWR报告包含大量信息，需要熟悉关键指标如：
   • 负载配置文件（Load Profile）
   • 实例效率百分比（Instance Efficiency Percentages）
   • 等待事件（Top 5 Timed Events）
   • SQL统计信息（SQL Statistics）

通过合理配置和管理AWR快照，并熟练掌握AWR报告的生成方法，可以有效地监控和诊断数据库性能问题，同时控制存储空间的合理使用。