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

🗃️ Oracle 19C V$SQL_WORKAREA_ACTIVE 动态性能视图详解

1. 概述与核心作用

V$SQL_WORKAREA_ACTIVE 是 Oracle 数据库中一个关键的性能视图，用于实时监控当前正在使用的 SQL 工作区内存。与 V$SQL_WORKAREA（显示历史信息）不同，此视图只显示当前活跃的工作区，为实时性能诊断提供关键数据。

核心作用：

• 实时监控：显示当前正在执行的 SQL 工作区内存使用情况
• 性能问题即时诊断：实时识别正在发生的内存密集型操作问题
• 资源使用分析：监控 PGA 内存的实时分配和使用情况
• 执行模式跟踪：实时跟踪工作区的执行模式（最优、单遍、多遍）

2. 使用场景

• 实时性能诊断：当数据库出现性能问题时，立即查看当前正在执行的内存密集型操作
• PGA内存压力分析：监控实时的PGA内存使用情况，识别内存压力
• 长时间运行操作监控：跟踪可能消耗大量资源的排序、哈希连接等操作
• 并行查询监控：监控并行查询操作中的工作区使用情况
• 临时表空间I/O问题诊断：实时识别导致临时表空间大量I/O的操作

3. 字段含义详解

以下是 V$SQL_WORKAREA_ACTIVE 视图的主要字段及其含义：

字段名称	数据类型	含义说明
SQL_ID	VARCHAR2(13)	SQL语句的唯一标识符
SQL_EXEC_ID	NUMBER	SQL执行标识符，与V$SQL_MONITOR关联
INST_ID	NUMBER	实例标识符（RAC环境）
SID	NUMBER	会话标识符
WORKAREA_ADDRESS	RAW(8)	工作区的内存地址
OPERATION_TYPE	VARCHAR2(40)	操作类型： • SORT - 排序操作 • HASH-JOIN - 哈希连接 • BITMAP MERGE - 位图合并 • BITMAP CREATE - 位图创建 • GROUP BY - 分组操作 • WINDOW BUFFER - 窗口函数缓冲 • WRITE BUFFER - 写缓冲
OPERATION_ID	NUMBER	在执行计划中的操作ID
POLICY	VARCHAR2(10)	内存管理策略：AUTO或MANUAL
QC_SESSION_ID	NUMBER	查询协调器会话ID（并行查询时）
QC_INSTANCE_ID	NUMBER	查询协调器实例ID（RAC环境）
ACTIVE_TIME	NUMBER	工作区活动总时间（微秒）
WORK_AREA_SIZE	NUMBER	工作区大小（字节）
EXPECTED_SIZE	NUMBER	期望的工作区大小（字节）
ACTUAL_MEM_USED	NUMBER	实际使用的内存量（字节）
MAX_MEM_USED	NUMBER	最大使用过的内存量（字节）
NUMBER_PASSES	NUMBER	处理所需的遍数： • 0 - 最优执行 • 1 - 单遍执行 • >1 - 多遍执行
TEMPSEG_SIZE	NUMBER	使用的临时段大小（字节）
TABLESPACE	VARCHAR2(30)	临时表空间名称
SEGMENT_FILE	NUMBER	临时段文件号
SEGMENT_BLOCK	NUMBER	临时段块号
CON_ID	NUMBER	容器ID（多租户环境）
IS_LAST	VARCHAR2(1)	是否是最后的工作区操作
LAST_MEMORY_USED	NUMBER	上一次使用的内存量
LAST_EXECUTION	VARCHAR2(10)	上一次执行模式
LAST_DEGREE	NUMBER	上一次并行度

4. 相关视图与基表

相关视图：

• V$SQL_WORKAREA：显示历史工作区信息
• V$SQL_WORKAREA_HISTOGRAM：工作区内存使用直方图统计
• V$PGASTAT：PGA内存使用统计信息
• V$PROCESS：进程内存使用信息
• V$SQL：SQL语句的基本信息
• V$SQL_MONITOR：SQL实时监控信息
• V$TEMPORARY_SEGMENTS：临时段信息
• V$SESSION：会话信息

基表：

V$SQL_WORKAREA_ACTIVE 是基于内存结构的视图，其底层基表是 X$KQLFSQA，这是Oracle内部的内存结构，存储当前活动工作区的实时信息。

5. 底层原理与工作机制

实时监控机制：

1. 工作区激活跟踪：当SQL开始执行需要工作区的操作时，Oracle在PGA中分配工作区并开始跟踪
2. 实时数据收集：Oracle内核实时更新工作区的使用情况，包括内存使用量、执行模式等
3. 内存结构映射：V$SQL_WORKAREA_ACTIVE 视图直接映射到内部内存结构X$KQLFSQA
4. 动态更新：随着操作的执行，视图中的数据实时更新

执行模式转换：

1. 初始分配：根据优化器估算分配初始工作区
2. 内存压力检测：如果PGA内存不足，Oracle可能减少工作区大小
3. 执行模式转换：工作区可能从最优模式转为单遍或多遍模式
4. 实时调整：根据数据量和可用内存，Oracle实时调整工作区大小

内存管理流程：

SQL执行 → 工作区分配 → 内存使用监控 → 执行模式确定 → 
结果生成 → 工作区释放 → 信息记录到历史视图

6. 常用查询 SQL

1. 查看当前活动的工作区操作

SELECT sid, 
       sql_id,
       operation_type,
       ROUND(actual_mem_used/1024/1024, 2) current_mem_mb,
       ROUND(max_mem_used/1024/1024, 2) max_mem_mb,
       number_passes,
       ROUND(tempseg_size/1024/1024, 2) temp_mb,
       tablespace
FROM v$sql_workarea_active
ORDER BY actual_mem_used DESC;

2. 识别当前多遍执行的操作（实时性能问题）

SELECT s.username,
       s.sid,
       s.serial#,
       w.sql_id,
       w.operation_type,
       w.number_passes,
       ROUND(w.actual_mem_used/1024/1024, 2) current_mem_mb,
       ROUND(w.tempseg_size/1024/1024, 2) temp_mb,
       sql.sql_text
FROM v$sql_workarea_active w,
     v$session s,
     v$sql sql
WHERE w.sid = s.sid
AND w.sql_id = sql.sql_id
AND w.number_passes > 0
ORDER BY w.number_passes DESC, w.tempseg_size DESC;

3. 监控并行查询的工作区使用

SELECT sql_id,
       operation_type,
       qc_instance_id,
       qc_session_id,
       COUNT(*) parallel_operations,
       SUM(actual_mem_used)/1024/1024 total_mem_mb,
       MAX(number_passes) max_passes
FROM v$sql_workarea_active
WHERE qc_session_id IS NOT NULL
GROUP BY sql_id, operation_type, qc_instance_id, qc_session_id
ORDER BY total_mem_mb DESC;

4. 实时PGA内存压力分析

SELECT operation_type,
       COUNT(*) active_operations,
       SUM(actual_mem_used)/1024/1024 total_current_mem_mb,
       SUM(max_mem_used)/1024/1024 total_max_mem_mb,
       SUM(CASE WHEN number_passes = 0 THEN 1 ELSE 0 END) optimal_count,
       SUM(CASE WHEN number_passes = 1 THEN 1 ELSE 0 END) onepass_count,
       SUM(CASE WHEN number_passes > 1 THEN 1 ELSE 0 END) multipass_count
FROM v$sql_workarea_active
GROUP BY operation_type
ORDER BY total_current_mem_mb DESC;

5. 结合SQL监控查看详细信息

SELECT w.sid,
       w.sql_id,
       w.operation_type,
       m.sql_text,
       m.ela_per_sec,
       ROUND(w.actual_mem_used/1024/1024, 2) current_mem_mb,
       w.number_passes,
       ROUND(w.tempseg_size/1024/1024, 2) temp_mb
FROM v$sql_workarea_active w,
     v$sql_monitor m
WHERE w.sql_id = m.sql_id
AND w.sql_exec_id = m.sql_exec_id
ORDER BY w.actual_mem_used DESC;

6. 查找消耗最多临时表空间的操作

SELECT sid,
       sql_id,
       operation_type,
       ROUND(tempseg_size/1024/1024, 2) temp_mb,
       tablespace,
       segment_file,
       segment_block
FROM v$sql_workarea_active
WHERE tempseg_size > 0
ORDER BY tempseg_size DESC;

7. 关键知识点

执行模式的重要性：

• 最优模式（0遍）：所有操作在内存中完成，性能最佳
• 单遍模式（1遍）：需要一次磁盘I/O，性能中等
• 多遍模式（>1遍）：需要多次磁盘I/O，性能最差

实时监控价值：

• 即时问题检测：无需等待AWR或历史数据，立即发现问题
• 资源使用趋势：观察内存使用随时间的变化趋势
• 干预机会：在操作执行过程中可能进行干预

性能调优指导：

1. 高多遍执行率表明PGA内存可能不足
2. 大临时段使用表明需要优化SQL或增加临时表空间
3. 长时间活跃的工作区可能需要SQL优化

多租户环境考虑：

• 在CDB环境中，可以按CON_ID分析各PDB的工作区使用
• 需要考虑PDB间的PGA资源竞争

通过深入理解和使用 V$SQL_WORKAREA_ACTIVE 视图，DBA可以实时监控数据库中的内存密集型操作，及时发现性能问题并进行干预，确保数据库的高效运行。

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