Oracle 诊断包使用

诊断包概述

诊断包组件

Oracle Diagnostic Pack

包含以下核心组件：

Automatic Workload Repository (AWR)
Active Session History (ASH)
Automatic Database Diagnostic Monitor (ADDM)
SQL Tuning Advisor
SQL Access Advisor
Segment Advisor
Memory Advisor
Undo Advisor

Oracle Tuning Pack

包含以下核心组件：

SQL Tuning Advisor
SQL Access Advisor
SQL Performance Analyzer
Database Replay
Real Application Testing

许可证要求

重要注意事项：

诊断包和调优包需要额外的许可证
未授权使用会违反Oracle许可证协议
可以通过DBA_FEATURE_USAGE_STATISTICS视图检查使用情况

许可证合规性检查：

sql

SELECT * FROM DBA_FEATURE_USAGE_STATISTICS 
WHERE NAME IN ('Automatic Workload Repository', 'Active Session History', 
               'Automatic Database Diagnostic Monitor', 'SQL Tuning Advisor');

AWR使用详解

AWR配置

快照设置

sql

-- 查看当前设置
SELECT * FROM DBA_HIST_WR_CONTROL;

-- 修改快照间隔和保留时间
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  interval => 60, -- 快照间隔（分钟）
  retention => 43200, -- 保留时间（分钟，30天）
  topnsql => 100 -- 每个快照收集的top SQL数量
);

19c vs 21c差异：

21c默认快照间隔为30分钟，保留时间为8天
21c支持更细粒度的快照设置

AWR报告生成

生成HTML格式报告

sql

-- 使用DBMS_WORKLOAD_REPOSITORY包生成报告
DECLARE
  l_report CLOB;
BEGIN
  l_report := DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid => (SELECT DBID FROM V$DATABASE),
    l_inst_num => 1,
    l_bid => (SELECT MAX(snap_id)-1 FROM DBA_HIST_SNAPSHOT),
    l_eid => (SELECT MAX(snap_id) FROM DBA_HIST_SNAPSHOT),
    l_options => 0
  );
  
  -- 将报告保存到文件
  DBMS_XSLPROCESSOR.CLOB2FILE(l_report, 'DATA_PUMP_DIR', 'awr_report.html');
END;
/

生成文本格式报告

bash

# 使用awrrpt.sql脚本生成报告
sqlplus / as sysdba @?/rdbms/admin/awrrpt.sql

AWR基线管理

创建基线

sql

-- 创建静态基线
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
  start_snap_id => 100,
  end_snap_id => 120,
  baseline_name => 'NORMAL_LOAD_BASELINE',
  description => 'Baseline for normal workload'
);

-- 创建动态基线
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
  start_time => SYSTIMESTAMP,
  duration => 1440, -- 1天
  baseline_name_prefix => 'WEEKLY_BASELINE_',
  template_name => 'WEEKLY_BASELINE_TEMPLATE',
  expiration => 365, -- 保留365天
  repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=0;BYMINUTE=0;BYSECOND=0'
);

使用基线进行比较

sql

-- 生成基线比较报告
SELECT DBMS_WORKLOAD_REPOSITORY.AWR_COMPARE_REPORT_HTML(
  l_dbid1 => (SELECT DBID FROM V$DATABASE),
  l_inst_num1 => 1,
  l_bid1 => 100,
  l_eid1 => 120,
  l_dbid2 => (SELECT DBID FROM V$DATABASE),
  l_inst_num2 => 1,
  l_bid2 => 200,
  l_eid2 => 220
) FROM DUAL;

ASH使用详解

ASH数据查看

实时ASH数据

sql

-- 查看当前活跃会话
SELECT * FROM V$ACTIVE_SESSION_HISTORY WHERE sample_time > SYSTIMESTAMP - INTERVAL '10' MINUTE;

-- 查看等待事件分布
SELECT event, COUNT(*) FROM V$ACTIVE_SESSION_HISTORY 
WHERE sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR 
GROUP BY event ORDER BY 2 DESC;

历史ASH数据

sql

-- 从AWR中查询历史ASH数据
SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY 
WHERE snap_id BETWEEN (SELECT MAX(snap_id)-1 FROM DBA_HIST_SNAPSHOT) AND (SELECT MAX(snap_id) FROM DBA_HIST_SNAPSHOT);

ASH报告生成

使用ashrpt.sql脚本

bash

sqlplus / as sysdba @?/rdbms/admin/ashrpt.sql

使用DBMS_WORKLOAD_REPOSITORY包

sql

-- 生成ASH报告
DECLARE
  l_report CLOB;
BEGIN
  l_report := DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
    l_dbid => (SELECT DBID FROM V$DATABASE),
    l_inst_num => 1,
    l_bid => (SELECT MAX(snap_id)-1 FROM DBA_HIST_SNAPSHOT),
    l_eid => (SELECT MAX(snap_id) FROM DBA_HIST_SNAPSHOT)
  );
  
  DBMS_XSLPROCESSOR.CLOB2FILE(l_report, 'DATA_PUMP_DIR', 'ash_report.html');
END;
/

ADDM使用详解

ADDM分析

自动运行

ADDM默认在每次AWR快照后自动运行
结果存储在DBA_ADDM_FINDINGS视图中

手动运行

sql

-- 手动运行ADDM分析
DECLARE
  l_task_id VARCHAR2(30);
BEGIN
  l_task_id := DBMS_ADDM.ANALYZE(
    task_name => 'CUSTOM_ADDM_ANALYSIS',
    begin_snap => (SELECT MAX(snap_id)-1 FROM DBA_HIST_SNAPSHOT),
    end_snap => (SELECT MAX(snap_id) FROM DBA_HIST_SNAPSHOT),
    dbid => (SELECT DBID FROM V$DATABASE)
  );
END;
/

ADDM报告查看

sql

-- 查看ADDM任务
SELECT * FROM DBA_ADDM_TASKS ORDER BY TASK_ID DESC;

-- 生成ADDM报告
SELECT DBMS_ADDM.GET_REPORT('CUSTOM_ADDM_ANALYSIS') FROM DUAL;

-- 查看ADDM发现的问题
SELECT * FROM DBA_ADDM_FINDINGS WHERE TASK_NAME = 'CUSTOM_ADDM_ANALYSIS';

SQL Tuning Advisor使用

单SQL调优

sql

-- 创建SQL调优任务
DECLARE
  l_task_id VARCHAR2(30);
BEGIN
  l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => '6t8b8x09yf7k2', -- 从V$SQL或AWR报告中获取
    scope => 'COMPREHENSIVE',
    time_limit => 60,
    task_name => 'SQL_TUNING_TASK_6t8b8x09yf7k2',
    description => 'Tune specific SQL statement'
  );
  
  -- 执行调优任务
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'SQL_TUNING_TASK_6t8b8x09yf7k2');
END;
/

查看调优建议

sql

-- 查看调优建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK_6t8b8x09yf7k2') FROM DUAL;

-- 查看建议详情
SELECT * FROM DBA_SQLTUNE_ADVISOR_FINDINGS WHERE TASK_NAME = 'SQL_TUNING_TASK_6t8b8x09yf7k2';
SELECT * FROM DBA_SQLTUNE_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME = 'SQL_TUNING_TASK_6t8b8x09yf7k2';

诊断包最佳实践

性能监控最佳实践

设置合理的快照间隔：
- OLTP系统：15-30分钟
- OLAP系统：60分钟
- 问题排查期间：5-10分钟
保留时间设置：
- 生产系统：30-90天
- 测试系统：7-14天
- 关键基线：永久保留
TOP SQL设置：
- 考虑设置为100-200，以捕获足够的SQL信息
- 避免设置过大导致性能开销

诊断包使用建议

定期生成和分析报告：
- 每日：简要查看关键指标
- 每周：详细分析AWR报告
- 每月：进行全面性能评估
建立性能基线：
- 正常业务负载基线
- 峰值负载基线
- 重要业务流程基线
结合多种工具分析：
- AWR：长期趋势分析
- ASH：实时问题诊断
- ADDM：自动问题识别
- SQL Tuning Advisor：SQL优化

性能问题排查流程

收集信息：
- 生成AWR和ASH报告
- 运行ADDM分析
- 收集相关SQL信息
识别瓶颈：
- 查看Top 5 Timed Events
- 分析SQL Statistics
- 检查Segment Statistics
深入分析：
- 针对具体等待事件分析
- 查看执行计划
- 检查相关指标
制定解决方案：
- SQL优化
- 索引调整
- 参数修改
- 硬件升级
实施和验证：
- 实施解决方案
- 验证效果
- 生成对比报告

常见问题（FAQ）

Q: 如何检查诊断包的使用情况？

sql

SELECT * FROM DBA_FEATURE_USAGE_STATISTICS 
WHERE NAME LIKE '%Diagnostic%' OR NAME LIKE '%Tuning%';

Q: 如何禁用诊断包组件？

sql

-- 禁用ADDM
EXEC DBMS_ADDM.DISABLE;

-- 禁用自动SQL调优
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(
  client_name => 'sql tuning advisor',
  operation => NULL,
  window_name => NULL);

Q: AWR快照生成失败怎么办？

检查MMON进程状态
检查表空间空间是否充足
检查权限问题
手动生成快照测试

sql

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

Q: 如何减少诊断包的性能开销？

调整快照间隔，避免过于频繁
减少TOP SQL的数量
缩短保留时间
只在必要时使用全面分析

Q: 如何将AWR报告导出到文件？

sql

DECLARE
  l_report CLOB;
BEGIN
  l_report := DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid => (SELECT DBID FROM V$DATABASE),
    l_inst_num => 1,
    l_bid => (SELECT MAX(snap_id)-1 FROM DBA_HIST_SNAPSHOT),
    l_eid => (SELECT MAX(snap_id) FROM DBA_HIST_SNAPSHOT)
  );
  
  -- 使用UTL_FILE包导出
  DECLARE
    l_file UTL_FILE.FILE_TYPE;
  BEGIN
    l_file := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'awr_report.html', 'W', 32767);
    UTL_FILE.PUT_LINE(l_file, l_report);
    UTL_FILE.FCLOSE(l_file);
  EXCEPTION
    WHEN OTHERS THEN
      UTL_FILE.FCLOSE_ALL;
      RAISE;
  END;
END;
/

许可证合规性

合规性最佳实践

定期审计：
- 每季度检查诊断包使用情况
- 确保只在授权系统上使用
- 记录所有使用情况
权限控制：
- 限制诊断包相关包的访问权限
- 只授予DBA角色必要的权限
- 监控特权用户的活动
文档管理：
- 保存许可证文档
- 记录授权使用的系统
- 跟踪许可证到期日期

违规风险

法律责任：违反许可证协议可能导致法律诉讼
财务风险：可能面临高额罚款
业务风险：可能影响Oracle支持服务

总结

Oracle诊断包是强大的性能监控和优化工具集，能够帮助DBA高效管理和优化数据库性能。在使用诊断包时，需要注意许可证合规性，合理配置和使用各组件，并结合实际业务需求进行分析和优化。

通过定期生成和分析AWR、ASH报告，运行ADDM分析，以及使用SQL Tuning Advisor等工具，可以及时发现和解决性能问题，确保数据库系统的高效稳定运行。

在Oracle 19c和21c中，诊断包的功能不断增强，提供了更多自动化和智能化的性能管理能力，DBA可以充分利用这些功能来提高管理效率和优化效果。

Oracle 诊断包使用 ​

诊断包概述 ​

诊断包组件 ​

许可证要求 ​

AWR使用详解 ​

AWR配置 ​

AWR报告生成 ​

AWR基线管理 ​

ASH使用详解 ​

ASH数据查看 ​

ASH报告生成 ​

ADDM使用详解 ​

ADDM分析 ​

ADDM报告查看 ​

SQL Tuning Advisor使用 ​

单SQL调优 ​

查看调优建议 ​

诊断包最佳实践 ​

性能监控最佳实践 ​

诊断包使用建议 ​

性能问题排查流程 ​

常见问题（FAQ） ​

Q: 如何检查诊断包的使用情况？ ​

Q: 如何禁用诊断包组件？ ​

Q: AWR快照生成失败怎么办？ ​

Q: 如何减少诊断包的性能开销？ ​

Q: 如何将AWR报告导出到文件？ ​

许可证合规性 ​

合规性最佳实践 ​

违规风险 ​

总结 ​