面试宝典:介绍下Oracle数据库动态性能视图 V$AW_AGGREGATE_OP

Oracle 19c: V$AW_AGGREGATE_OP 动态性能视图详解

核心作用：监控自动工作负载仓库（AWR）的聚合操作性能，提供AWR数据处理过程中的资源消耗和执行效率指标。

---

1. 视图核心作用

• 性能监控：跟踪AWR快照生成过程中的SQL聚合操作
• 资源分析：测量聚合操作消耗的CPU、I/O和时间资源
• 瓶颈识别：定位AWR处理中的性能瓶颈
• 调优依据：优化AWR收集效率和数据库诊断基础架构
• 容量规划：评估AWR处理对系统资源的影响

📌 注意：此视图仅当AWR快照处理期间有聚合操作时产生数据，日常查询可能为空。

---

2. 关键使用场景

1. AWR性能诊断：分析快照生成时间过长问题
2. 系统优化：识别高代价的AWR聚合SQL
3. 资源规划：评估AWR处理对主机资源的压力
4. 故障排查：诊断AWR快照失败或数据缺失问题
5. 安全审计：监控AWR数据收集过程中的异常操作
6. 基线调整：优化AWR保留策略和聚合级别

---

3. 字段详解 (Oracle 19c)

字段名	数据类型	说明
OPERATION_ID	NUMBER	聚合操作唯一标识符
OPERATION_TYPE	VARCHAR2(30)	操作类型：AGGREGATE/PURGE/BASELINE等
START_TIME	TIMESTAMP	操作开始时间
END_TIME	TIMESTAMP	操作结束时间
ELAPSED_TIME	NUMBER	耗时（秒）
CPU_TIME	NUMBER	CPU消耗时间（厘秒）
DB_TIME	NUMBER	数据库时间（厘秒）
BUFFER_GETS	NUMBER	逻辑读次数
DISK_READS	NUMBER	物理读次数
ROWS_PROCESSED	NUMBER	处理的行数
SQL_ID	VARCHAR2(13)	执行的SQL ID（关联V$SQL）
SNAP_ID	NUMBER	关联的AWR快照ID（关联DBA_HIST_SNAPSHOT）
DBID	NUMBER	数据库标识符
INSTANCE_NUMBER	NUMBER	实例编号
STATUS	VARCHAR2(10)	操作状态：COMPLETED/RUNNING/FAILED
ERROR_MESSAGE	VARCHAR2(4000)	错误信息（若失败）

---

4. 相关视图与基表

关联视图

• DBA_HIST_SNAPSHOT：AWR快照元数据（SNAP_ID关联）
• V$SQL：SQL执行细节（SQL_ID关联）
• V$ACTIVE_SESSION_HISTORY：ASH数据（包含AWR操作会话）
• DBA_HIST_SQLSTAT：历史SQL统计（聚合SQL性能）
• V$SYSMETRIC_HISTORY：系统指标（AWR操作期间资源使用）

底层基表

• WRH$_OPERATION：AWR操作历史基表（AWR仓库存储）

  SELECT * FROM WRH$_OPERATION WHERE operation_type = 'AGGREGATE';
  

• WRM$_SNAPSHOT：快照控制表

---

5. 核心原理

AWR聚合处理流程

聚合操作类型

操作类型	描述
AGGREGATE	汇总原始性能数据生成历史记录（如DBA_HIST_SYSSTAT）
PURGE	清理过期AWR数据
BASELINE	创建/管理性能基线
MOVE	迁移AWR数据到其他表空间
EXPORT/IMPORT	AWR数据导入导出

资源消耗机制

1. CPU密集型：统计计算、数据聚合
2. I/O密集型：读写SYSAUX表空间的AWR表
3. 内存密集型：PGA内存用于排序和哈希操作

---

6. 常用操作SQL

查看最近的聚合操作

SELECT 
    operation_id,
    operation_type,
    ROUND(elapsed_time) AS sec,
    ROWS_PROCESSED,
    BUFFER_GETS,
    DISK_READS,
    TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') AS start_time
FROM V$AW_AGGREGATE_OP
ORDER BY start_time DESC;

识别高资源消耗操作

SELECT 
    operation_id,
    sql_id,
    ROUND(elapsed_time) AS elapsed_sec,
    ROUND(cpu_time/100) AS cpu_sec,
    ROUND(db_time/100) AS db_sec,
    buffer_gets,
    disk_reads
FROM V$AW_AGGREGATE_OP
WHERE elapsed_time > 60  -- >60秒
ORDER BY elapsed_time DESC;

关联问题SQL

SELECT 
    o.operation_id,
    o.sql_id,
    s.sql_text,
    o.rows_processed,
    o.elapsed_time
FROM V$AW_AGGREGATE_OP o
JOIN V$SQL s ON o.sql_id = s.sql_id
WHERE o.status = 'FAILED';

分析AWR快照处理时间

SELECT 
    s.snap_id,
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS snap_start,
    o.operation_type,
    o.elapsed_time AS op_sec,
    ROUND((s.end_interval_time - s.begin_interval_time)*86400) AS snap_sec
FROM V$AW_AGGREGATE_OP o
JOIN DBA_HIST_SNAPSHOT s 
  ON o.snap_id = s.snap_id 
 AND o.dbid = s.dbid
ORDER BY s.snap_id DESC;

监控运行中的聚合操作

SELECT 
    operation_id,
    operation_type,
    ROUND((SYSTIMESTAMP - start_time)*86400) AS running_sec,
    rows_processed
FROM V$AW_AGGREGATE_OP
WHERE status = 'RUNNING';

诊断失败操作

SELECT 
    operation_id,
    start_time,
    error_message
FROM V$AW_AGGREGATE_OP
WHERE status = 'FAILED'
  AND start_time > SYSDATE - 7;

---

注意事项

1. 数据生命周期：

   • 操作记录在内存中维护
   • 实例重启后数据丢失
   • 持久化数据在WRH$_OPERATION中

2. 性能影响：

   • 避免在快照期间频繁查询该视图
   • 使用AWR报告分析历史聚合性能：

     SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(dbid, inst_num, start_snap, end_snap));
     

3. 调优建议：

   • 减少高频SQL统计：

     EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60);
     

   • 调整AWR保留策略：

     EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 43200); -- 30天
     

4. 资源限制：

   • 监控SYSAUX表空间使用：

     SELECT * FROM V$SYSAUX_OCCUPANTS WHERE occupant_name = 'SM/AWR';
     

   • 控制AWR大小：

     EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(topnsql => 'MAXIMUM');
     

5. RAC环境：

   • 每个实例维护自己的操作记录
   • 全局视图需查询GV$AW_AGGREGATE_OP
   • 快照由主实例的MMON进程协调

6. 错误处理：

   -- 强制清理卡住的AWR操作
   EXEC DBMS_WORKLOAD_REPOSITORY.CANCEL_OPERATION(operation_id);
   

💡 最佳实践：

• 定期检查长时间聚合操作：

  SELECT * FROM V$AW_AGGREGATE_OP WHERE elapsed_time > 300; -- >5分钟
  

• 优化高负载SQL：

  SELECT * FROM TABLE(DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id'));
  

• 设置警报阈值：

  BEGIN
    DBMS_SERVER_ALERT.SET_THRESHOLD(
      metrics_id => DBMS_SERVER_ALERT.ELAPSED_TIME_PER_CALL,
      warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
      warning_value => '60',  -- 60秒
      object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,
      object_name => 'SYS$BACKGROUND');
  END;
  

通过V$AW_AGGREGATE_OP，DBA可以深入洞察AWR后台处理的性能特征，确保数据库诊断基础设施的高效运行，为性能优化提供可靠的数据基础。

欢迎关注我的公众号《IT小Chen》