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

原创于 2025-09-05 12:15:58 发布 · 607 阅读

19 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述

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

1. 概述与核心作用

V$SQL_WORKAREA 是 Oracle 数据库中一个重要的动态性能视图，用于监控和管理 SQL 语句执行时使用的内存工作区。这些工作区主要用于内存密集型操作，如排序、哈希连接、位图操作和批量加载等。

核心作用：

内存使用监控：跟踪每个 SQL 游标的工作区内存使用情况
性能诊断：识别哪些 SQL 操作可能导致临时表空间磁盘 I/O
优化决策：为 PGA（Program Global Area）内存配置提供依据
效率分析：评估工作区内存分配的效率（最优、单遍、多遍执行）

2. 使用场景

诊断临时表空间性能问题：当发现大量临时表空间磁盘活动时，使用此视图识别问题SQL
PGA内存优化：调整 PGA_AGGREGATE_TARGET 参数前，分析当前工作区内存使用情况
SQL性能调优：识别需要优化的内存密集型SQL操作
容量规划：评估系统内存需求，特别是PGA内存的合理分配
监控哈希连接和排序操作：跟踪大量使用内存的操作的性能特征

3. 字段含义详解

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

字段名称	数据类型	含义说明
SQL_ID	VARCHAR2(13)	SQL语句的唯一标识符
SQL_EXEC_ID	NUMBER	SQL执行标识符，与V$SQL_MONITOR关联
SQL_PLAN_HASH_VALUE	NUMBER	SQL执行计划的哈希值
SQL_CHILD_ADDRESS	RAW(8)	子游标的内存地址
SQL_CHILD_NUMBER	NUMBER	子游标编号
WORKAREA_ADDRESS	RAW(8)	工作区的内存地址
OPERATION_TYPE	VARCHAR2(40)	操作类型： • SORT • HASH-JOIN • BITMAP MERGE • BITMAP CREATE • GROUP BY • ROLLUP • WINDOW BUFFER • WRITE BUFFER
OPERATION_ID	NUMBER	在执行计划中的操作ID
POLICY	VARCHAR2(10)	内存管理策略：AUTO或MANUAL
SID	NUMBER	会话标识符
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	处理所需的遍数
TEMPSEG_SIZE	NUMBER	使用的临时段大小（字节）
TABLESPACE	VARCHAR2(30)	临时表空间名称
SEGMENT_FILE	NUMBER	临时段文件号
SEGMENT_BLOCK	NUMBER	临时段块号
CON_ID	NUMBER	容器ID（多租户环境）

4. 相关视图与基表

基表：

V$SQL_WORKAREA 是基于内存结构的视图，其底层基表是 X $K Q L FSQ * * 和 * * X$ KQLFSQT，这些是Oracle内部的内存结构，不直接对外提供查询接口。

5. 底层原理与工作机制

工作区内存管理原理：

PGA内存结构：工作区内存分配在PGA中，每个服务器进程有自己独立的PGA
自动内存管理：当 WORKAREA_SIZE_POLICY = AUTO 时，Oracle自动管理PGA内存分配
工作区操作类型：
- 排序操作：ORDER BY、GROUP BY、窗口函数等需要的排序工作区
- 哈希操作：哈希连接、哈希聚合等需要的哈希工作区
- 位图操作：位图索引相关操作需要的工作区

执行模式：

最优模式（OPTIMAL）：工作区完全在内存中执行，效率最高
单遍模式（ONEPASS）：部分数据在内存，部分在磁盘，需要一次磁盘I/O
多遍模式（MULTIPASS）：需要多次磁盘I/O，性能最差

内存分配流程：

SQL解析时估算需要的工作区大小
根据当前PGA内存情况分配工作区
执行过程中根据需要调整工作区大小
操作完成后释放工作区内存

6. 常用查询 SQL

1. 查找使用最多工作区内存的SQL

SELECT sql_id, 
       operation_type,
       SUM(max_mem_used)/1024/1024 total_max_mem_mb,
       SUM(tempseg_size)/1024/1024 total_temp_mb,
       COUNT(*) operations
FROM v$sql_workarea
GROUP BY sql_id, operation_type
HAVING SUM(max_mem_used) > 100*1024*1024 -- 大于100MB
ORDER BY total_max_mem_mb DESC;

2. 识别多遍执行的SQL操作（性能问题）

SELECT sql_id, 
       operation_type,
       number_passes,
       MAX(max_mem_used)/1024/1024 max_mem_mb,
       SUM(tempseg_size)/1024/1024 total_temp_mb,
       COUNT(*) occurrences
FROM v$sql_workarea
WHERE number_passes > 0
GROUP BY sql_id, operation_type, number_passes
ORDER BY number_passes DESC, total_temp_mb DESC;

3. 查看当前活动的工作区

SELECT s.sid,
       s.username,
       w.operation_type,
       w.actual_mem_used/1024/1024 current_mem_mb,
       w.max_mem_used/1024/1024 max_mem_mb,
       w.number_passes,
       w.tempseg_size/1024/1024 temp_mb,
       sql.sql_text
FROM v$sql_workarea w,
     v$session s,
     v$sql sql
WHERE w.sid = s.sid
AND w.sql_id = sql.sql_id
AND w.workarea_address = sql.child_address
ORDER BY w.actual_mem_used DESC;

4. 分析特定SQL的工作区使用情况

SELECT operation_type,
       operation_id,
       policy,
       ROUND(expected_size/1024/1024, 2) expected_mb,
       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
WHERE sql_id = '&sql_id'
ORDER BY operation_id;

5. 工作区效率统计（最优 vs 非最优执行）

SELECT operation_type,
       COUNT(*) total_operations,
       SUM(CASE WHEN number_passes = 0 THEN 1 ELSE 0 END) optimal_executions,
       SUM(CASE WHEN number_passes = 1 THEN 1 ELSE 0 END) onepass_executions,
       SUM(CASE WHEN number_passes > 1 THEN 1 ELSE 0 END) multipass_executions,
       ROUND(SUM(CASE WHEN number_passes = 0 THEN 1 ELSE 0 END) * 100 / COUNT(*), 2) optimal_pct
FROM v$sql_workarea
GROUP BY operation_type
ORDER BY total_operations DESC;