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

原创于 2025-09-02 12:28:54 发布 · 963 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述
Oracle 19C 数据库中的 V$SORT_USAGE 视图是监控和管理临时表空间使用的关键工具。它能帮你快速定位哪些会话和 SQL 语句正在消耗大量的临时空间，这对于诊断性能问题和优化查询至关重要。由于临时表空间主要处理排序、哈希连接等操作，管理好它对数据库性能提升很有帮助。

下面我将为你详细解释这个视图的各个方面。

🧠 Oracle 19C V$SORT_USAGE 视图详解

1. 视图概述与作用

V$SORT_USAGE 视图用于显示当前正在使用临时段（Temporary Segments）的会话的详细信息。临时段主要存储在临时表空间中，用于支持各种需要临时存储数据的数据库操作。当这些操作需要的内存（PGA）不足时，就会使用磁盘上的临时表空间。

该视图的主要作用包括：

实时监控临时空间使用：识别正在使用临时表空间的会话及其使用的空间量。
诊断性能问题：帮助诊断因磁盘排序（Disk Sorts）、哈希连接（Hash Joins）或临时表操作导致的性能下降。
空间问题排查：当临时表空间空间不足或使用率过高时，定位问题源头。
SQL调优：找出哪些SQL语句产生了大量的临时空间使用，从而进行优化。

请注意：在 Oracle 12c 及更高版本中，V$TEMPSEG_USAGE 是 V$SORT_USAGE 的同义词，两者查询的是相同的数据。因此，本详解同样适用于 V$TEMPSEG_USAGE。

2. 字段含义详解

下表详细说明了 V$SORT_USAGE 视图中的各个字段。这些信息综合了 Oracle 19c 官方文档的概念和实际使用经验。

字段名 (Column Name)	数据类型 (Datatype)	描述 (Description)
USERNAME	VARCHAR2(128)	使用临时段的数据库用户名。
SESSION_ADDR	RAW(8)	会话的内存地址。可用于与 `V$SESSION` 视图关联。
SESSION_NUM	NUMBER	会话标识符（SID）。与 `V$SESSION.SID` 对应。
SQLADDR	RAW(8)	正在执行并使用了临时段的SQL语句的哈希值地址。用于与 `V$SQLAREA` 等视图关联。
SQLHASH	NUMBER	正在执行并使用了临时段的SQL语句的哈希值。用于唯一标识SQL语句。
SQL_ID	VARCHAR2(13)	正在执行并使用了临时段的SQL语句的SQL_ID。这是定位和优化SQL最关键的依据之一。
TABLESPACE	VARCHAR2(30)	临时段所在的临时表空间的名称。
CONTENTS	VARCHAR2(9)	段内容类型。对于临时表空间，此值始终为 `TEMPORARY`。
SEGTYPE	VARCHAR2(9)	临时段的类型。重要字段，指示了临时空间的用途： • `SORT`: SQL排序操作（如 `ORDER BY`, `GROUP BY`, `DISTINCT`, 创建索引等）。 • `HASH`: 哈希连接操作。 • `DATA`: 存储全局临时表（GLOBAL TEMPORARY TABLE）的数据。 • `INDEX`: 全局临时表上的索引。 • `LOB_DATA` 和 `LOB_INDEX`: 临时LOB对象。
SEGFILE#	NUMBER	临时段所在的临时文件的绝对文件号（Absolute File Number）。注意与 `V$TEMPFILE`.`FILE#` 可能不同，关联查询时需使用 `V$TEMPFILE` 的底层信息。
SEGBLK#	NUMBER	临时段在临时文件中的起始块号。
EXTENTS	NUMBER	该会话的临时段所使用的扩展区（Extent）数量。
BLOCKS	NUMBER	该会话的临时段所使用的Oracle块数量。
SEGRFNO#	NUMBER	临时段所在的临时文件的相对文件号（Relative File Number）。
CON_ID	NUMBER	容器ID。在多租户环境（CDB）中，标识该数据属于哪个容器（PDB）。对于非CDB环境，此值为0。

💡 核心解读要点：

SEGTYPE：这是最重要的字段之一，它直接告诉你临时空间被用于何种操作。例如，大量的 SORT 类型可能意味着需要优化SQL或调整PGA；而 DATA 类型则提示临时表的使用情况。
BLOCKS：通过此字段可以计算临时空间的实际使用量。公式为：BLOCKS * DB_BLOCK_SIZE。
SQL_ID：这是定位问题SQL的黄金钥匙，可以快速找到需要优化的SQL语句。

3. 使用场景

V$SORT_USAGE 在以下场景中非常重要：

临时表空间空间不足或使用率过高：当收到 “ORA-01652: unable to extend temp segment” 错误或监控发现临时表空间快满时，立即查询此视图，找出是哪个会话和SQL语句在消耗大量空间。
诊断性能问题：当数据库响应变慢，同时发现磁盘I/O很高（特别是临时表空间所在的磁盘）时，查询此视图检查是否有大规模磁盘排序或哈希连接操作。
监控异常SQL语句：定期查询此视图，找出持续使用大量临时空间的SQL，进行主动优化。
容量规划：分析临时空间的使用模式和高峰，为临时表空间的容量规划提供数据支持。
多租户环境下的资源管理：在CDB环境中，监控各个PDB对临时表空间的使用情况，避免一个PDB耗尽所有临时空间影响其他PDB。

4. 底层原理与相关知识点

4.1 临时段与临时表空间

临时表空间主要用于存储临时数据，与永久表空间不同：

临时表空间使用临时文件（Tempfile），其读写操作不生成重做日志（Redo），但会生成撤销日志（Undo）。
临时段在实例启动后，当有第一个排序操作需要磁盘空间时，在临时表空间中创建。
临时段由所有会话共享使用。一个临时表空间通常只有一个临时段（但可以有多个扩展区），不同会话的排序操作会使用该临时段中的不同扩展区。
当排序操作结束后，Oracle 并不会立即释放磁盘空间，而是将其标记为可复用。临时段会持续存在直至实例关闭或通过 ALTER TABLESPACE ... COALESCE 进行合并。

4.2 排序操作与临时空间

SQL语句中的以下操作可能需要使用临时表空间：

ORDER BY、GROUP BY、窗口函数（如 ROLLUP）、DISTINCT
连接操作，如 UNION、INTERSECT、MINUS
哈希连接（Hash Joins）
索引的创建和重建
全局临时表（Global Temporary Tables）的数据操作
一些LOB操作

Oracle会优先在PGA的排序区中完成这些操作。只有当工作区不足以容纳中间结果时，数据才会被写入磁盘（临时表空间）。

4.3 排序模式

Oracle的排序主要有三种模式：

最优排序（Optimal Sort）：整个排序操作完全在内存（PGA）中完成，无需磁盘I/O。性能最佳。
单次通过排序（One-Pass Sort）：排序数据量略大于PGA工作区，只需要一次磁盘I/O（将数据分片写入临时表空间后再合并一次）。性能较好。
多次通过排序（Multi-Pass Sort）：排序数据量远大于PGA工作区，需要多次磁盘I/O和合并操作。性能最差，应尽量避免。

V$SORT_USAGE 中显示的活动主要是单次通过和多次通过排序（或哈希等）操作。

5. 相关视图

视图名称	主要用途描述
V$TEMPSEG_USAGE	`V$SORT_USAGE` 的同义词，查询结果完全一致。
V$SORT_SEGMENT	显示临时表空间中排序段的整体使用情况（如总扩展区数、已用扩展区数、空闲扩展区数），从段的角度进行汇总统计。
V$TEMPFILE	显示实例中所有临时文件的信息。
V$TEMP_SPACE_HEADER	显示每个临时文件的空间头信息中记录的空间使用情况（总字节数、已用字节数、空闲字节数）。注意：此视图显示的是临时文件初始化过的空间，并非当前实时分配的空间，因此其值可能大于 `V$SORT_USAGE` 和 `V$SORT_SEGMENT` 的统计之和。
V$SQLAREA	提供SQL语句的详细信息。通过 `V$SORT_USAGE.SQL_ID` 关联，可获取正在消耗临时空间的SQL文本。
V$SESSION	显示会话信息。通过 `V$SORT_USAGE.SESSION_ADDR` 或 `SESSION_NUM`（SID）关联，可获取会话的详细属性（如用户名、客户端机器、程序等）。
V$SYSSTAT	包含系统统计信息，如 “sorts (memory)” 和 “sorts (disk)”，可以了解内存排序和磁盘排序的总体情况。

6. 基表信息

动态性能视图（V$视图）通常是基于更底层的 X $表 * * （虚拟内存表）构建的。 ‘ V$ SORT_USAGE/V $TEMPSEG_USAGE` 很可能基于名为 **X$ KTTSS 或 **X $K TSSO * * 的内部 X$ 表。

重要提醒：

X$表是Oracle数据库的内部结构，其命名、结构、字段含义没有公开的官方文档支持，不同版本之间可能会发生变化。
强烈不建议 直接查询 X$ 表。Oracle 不支持这样做，且存在风险。所有需要的信息都应通过公开的 V$ 视图获取。

7. 常用查询 SQL

7.1 当前临时空间使用情况概览

此查询可快速查看当前哪些会话和SQL正在使用临时空间，并按使用量降序排列。

SELECT 
    su.username,
    su.session_num AS sid,
    su.sql_id,
    su.tablespace,
    su.segtype,
    su.blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024 AS size_mb,
    su.extents,
    s.program,
    s.machine,
    sql.sql_text
FROM 
    v$sort_usage su
JOIN 
    v$session s ON su.session_num = s.sid
LEFT JOIN 
    v$sql sql ON su.sql_id = sql.sql_id
ORDER BY 
    su.blocks DESC;

7.2 按段类型统计临时空间使用

此查询用于分析临时空间的主要用途。

SELECT 
    segtype,
    COUNT(*) AS active_operations,
    SUM(blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024) AS total_size_mb,
    ROUND(SUM(blocks) * 100 / (SELECT SUM(blocks) FROM v$sort_usage), 2) AS percentage
FROM 
    v$sort_usage
GROUP BY 
    segtype
ORDER BY 
    total_size_mb DESC;

7.3 查找消耗临时空间最多的SQL

此查询有助于定位需要优化的高成本SQL语句。

SELECT 
    su.sql_id,
    sql.sql_text,
    SUM(su.blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024) AS total_temp_size_mb,
    COUNT(DISTINCT su.session_num) AS active_sessions
FROM 
    v$sort_usage su
JOIN 
    v$sql sql ON su.sql_id = sql.sql_id
GROUP BY 
    su.sql_id, sql.sql_text
HAVING 
    SUM(su.blocks) > 0
ORDER BY 
    total_temp_size_mb DESC;

7.4 临时空间使用与文件信息关联

此查询将使用情况与具体的临时文件关联起来。

SELECT 
    su.username,
    su.sql_id,
    su.tablespace,
    su.segfile#,
    tf.name AS temp_file_name,
    su.blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024 AS size_mb
FROM 
    v$sort_usage su
JOIN 
    v$tempfile tf ON su.segrfno# = tf.file#
ORDER BY 
    su.blocks DESC;

8. 最佳实践与注意事项

结合PGA调优：频繁出现大量磁盘排序（V$SORT_USAGE 中记录较多）通常意味着 PGA_AGGREGATE_TARGET 参数可能设置偏小。应优化PGA配置，力争使大多数排序在内存中完成（“sorts (memory)”）。
SQL优化是根本：解决临时空间过度使用问题的最有效方法是优化SQL语句：
- 避免不必要的 ORDER BY、DISTINCT。
- 为 GROUP BY、ORDER BY 子句中的列添加合适的索引。
- 重写复杂的SQL，减少中间结果集的大小。
- 考虑使用物化视图预计算大量聚合操作。
正确理解空间释放：临时段中的空间在操作完成后会被标记为空闲，可供新的操作复用，但磁盘空间并不会立即释放给操作系统。这是Oracle临时表空间管理的正常机制。如果确实需要收缩临时文件，通常需要先创建一个新的临时表空间并设为默认，然后重启实例或删除旧的临时表空间。
多租户环境下的监控：在CDB环境中，结合 CON_ID 字段监控每个PDB的临时空间使用情况，避免某个PDB的异常操作耗尽整个CDB的临时空间资源。
不要随意终止会话：虽然可以通过 ALTER SYSTEM KILL SESSION 'sid,serial#'; 终止占用大量临时空间的会话，但在生产环境中需极其谨慎，因为这可能会破坏事务一致性并影响用户体验。应优先尝试优化查询。