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

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

核心作用：监控自动工作负载仓库（AWR）与联机分析处理（OLAP）的集成功能，提供OLAP工作区在AWR中的性能分析、存储使用和操作状态信息。

---

1. 视图核心作用

• OLAP集成监控：跟踪AWR中OLAP工作区的使用情况
• 性能分析：测量OLAP立方体（Cubes）和度量（Measures）的查询性能
• 存储管理：监控OLAP工作区在AWR中的存储消耗
• 操作审计：记录OLAP相关的AWR操作历史
• 资源优化：识别高消耗的OLAP分析操作

📌 注意：此视图仅在安装了OLAP选件并启用了AWR-OLAP集成时有效。

---

2. 关键使用场景

1. OLAP性能诊断：分析OLAP查询在AWR中的执行效率
2. 存储优化：监控OLAP工作区在SYSAUX表空间的使用
3. 容量规划：评估OLAP历史数据的增长趋势
4. 故障排查：诊断OLAP工作区加载/刷新失败
5. 安全审计：验证OLAP操作的授权访问
6. 报表生成：优化包含OLAP数据的AWR报告

---

3. 字段详解 (Oracle 19c)

字段名	数据类型	说明
WORKSPACE_ID	NUMBER	OLAP工作区唯一标识符
WORKSPACE_NAME	VARCHAR2(128)	工作区名称
CUBE_NAME	VARCHAR2(128)	OLAP立方体名称
MEASURE_NAME	VARCHAR2(128)	度量名称
SNAPSHOT_ID	NUMBER	关联的AWR快照ID
OPERATION_TYPE	VARCHAR2(30)	操作类型：LOAD/REFRESH/AGGREGATE/QUERY
START_TIME	TIMESTAMP	操作开始时间
END_TIME	TIMESTAMP	操作结束时间
ELAPSED_TIME	NUMBER	耗时（秒）
ROWS_PROCESSED	NUMBER	处理的行数
STORAGE_USED_MB	NUMBER	存储使用量（MB）
CPU_TIME_SEC	NUMBER	CPU消耗时间（秒）
MEMORY_USED_MB	NUMBER	PGA内存使用（MB）
STATUS	VARCHAR2(15)	操作状态：COMPLETED/RUNNING/FAILED
ERROR_CODE	NUMBER	错误代码（如ORA-错误）
USER_NAME	VARCHAR2(30)	执行操作的用户
DIMENSION_COUNT	NUMBER	维度数量
HIERARCHY_COUNT	NUMBER	层级数量

---

4. 相关视图与基表

关联视图

• GV$AW_OLAP：集群所有实例的OLAP-AWR集成信息
• DBA_OLAP_WORKSPACES：OLAP工作区元数据
• DBA_OLAP_CUBES：OLAP立方体定义
• V$AW_SNAPSHOT：AWR快照信息
• V$ACTIVE_SESSION_HISTORY：OLAP操作期间的会话活动

底层基表

• WRH$_OLAP_OPERATION：OLAP操作历史基表

  SELECT * FROM WRH$_OLAP_OPERATION WHERE workspace_id = 101;
  

• WRH$_OLAP_STORAGE：OLAP存储使用历史表
• OLAP$ 系列内部表（OLAP元数据存储）

---

5. 核心原理

AWR-OLAP集成架构

关键操作流程

1. 数据加载：

   EXEC DBMS_AW.EXECUTE('AW LOAD MY_WORKSPACE');
   

2. 立方体刷新：

   EXEC DBMS_CUBE.REFRESH('SALES_CUBE');
   

3. AWR捕获：
   • MMON进程定期捕获OLAP元数据到AWR仓库
   • 存储在工作区快照WRH$_OLAP_WORKSPACE_SNAP
4. 查询处理：
   • OLAP查询通过DBMS_AW或DBMS_CUBE执行
   • 性能指标记录到V$AW_OLAP

存储管理

• OLAP历史数据存储在SYSAUX表空间
• 保留策略与AWR一致：

  EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 43200);
  

---

6. 常用操作SQL

查看OLAP工作区活动

SELECT 
    workspace_name,
    cube_name,
    operation_type,
    ROUND(elapsed_time) AS sec,
    ROUND(storage_used_mb) AS storage_mb,
    TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') AS start_time
FROM V$AW_OLAP
ORDER BY start_time DESC;

监控OLAP存储增长

SELECT 
    TRUNC(snap.begin_interval_time) AS day,
    o.workspace_name,
    SUM(o.storage_used_mb) AS daily_storage_mb,
    AVG(o.storage_used_mb) AS avg_storage_mb
FROM V$AW_OLAP o
JOIN DBA_HIST_SNAPSHOT snap ON o.snapshot_id = snap.snapshot_id
GROUP BY TRUNC(snap.begin_interval_time), o.workspace_name
ORDER BY day DESC;

诊断失败操作

SELECT 
    workspace_name,
    operation_type,
    error_code,
    status,
    elapsed_time
FROM V$AW_OLAP
WHERE status = 'FAILED'
  AND start_time > SYSDATE - 7;

分析高消耗OLAP查询

SELECT 
    workspace_name,
    cube_name,
    measure_name,
    AVG(elapsed_time) AS avg_time,
    MAX(elapsed_time) AS max_time,
    COUNT(*) AS exec_count
FROM V$AW_OLAP
WHERE operation_type = 'QUERY'
GROUP BY workspace_name, cube_name, measure_name
HAVING AVG(elapsed_time) > 10  -- >10秒
ORDER BY avg_time DESC;

关联ASH数据

SELECT 
    o.operation_type,
    ash.session_id,
    ash.session_serial#,
    ash.event,
    ash.wait_time
FROM V$ACTIVE_SESSION_HISTORY ash
JOIN V$AW_OLAP o 
  ON ash.sql_id = o.sql_id
  AND ash.sample_time BETWEEN o.start_time AND NVL(o.end_time, SYSDATE)
WHERE o.status = 'RUNNING';

清理历史OLAP数据

-- 清理特定工作区历史
BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_OLAP_DATA(
    workspace_name => 'SALES_ANALYSIS',
    before_timestamp => SYSDATE - 90);
END;

---

注意事项

1. 选件依赖：

   • 需安装OLAP选件：

     SELECT * FROM V$OPTION WHERE PARAMETER = 'OLAP';
     

   • 启用AWR-OLAP集成：

     EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_OLAP_SETTINGS(enable => TRUE);
     

2. 存储管理：

   • 监控SYSAUX空间使用：

     SELECT * FROM V$SYSAUX_OCCUPANTS WHERE OCCUPANT_NAME = 'OLAP';
     

   • 调整保留策略：

     EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_OLAP_SETTINGS(retention_days => 60);
     

3. 性能优化：

   • 为OLAP工作区分配专用表空间：

     ALTER WORKSPACE sales_analysis STORAGE (TABLESPACE olap_ts);
     

   • 优化立方体聚合策略：

     EXEC DBMS_CUBE.BUILD('SALES_CUBE', 'AGGREGATE OPTIMAL');
     

4. 安全实践：

   • 限制OLAP管理员权限：

     GRANT OLAP_USER TO analytics_user;
     

   • 审计敏感操作：

     AUDIT EXECUTE ON DBMS_CUBE BY ACCESS;
     

5. RAC环境：

   • OLAP工作区在所有实例共享
   • 使用GV$AW_OLAP监控集群范围活动
   • 确保所有实例的OLAP环境一致

6. 诊断工具：

   -- 生成OLAP-AWR报告
   SELECT * FROM TABLE(
     DBMS_WORKLOAD_REPOSITORY.OLAP_REPORT_HTML(
       workspace => 'SALES_ANALYSIS',
       start_snap => 12345,
       end_snap => 12346));
   

💡 最佳实践：

• 定期维护立方体：

  EXEC DBMS_CUBE.REBUILD('SALES_CUBE', 'COMPLETE');
  

• 设置存储警报：

  BEGIN
    DBMS_SERVER_ALERT.SET_THRESHOLD(
      metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
      warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
      warning_value => '85',
      critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
      critical_value => '95',
      object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
      object_name => 'OLAP_TS');
  END;
  

• 性能基线比较：

  SELECT * FROM TABLE(
    DBMS_WORKLOAD_REPOSITORY.COMPARE_OLAP_REPORT(
      workspace => 'SALES_ANALYSIS',
      baseline1 => 'PEAK_2023',
      baseline2 => 'PEAK_2024'));
  

通过V$AW_OLAP，DBA可以深入监控AWR与OLAP的集成性能，优化多维分析工作负载，确保历史分析数据的高效存储和访问。

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