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

好的，我们来深入解析 Oracle 19C 数据库中的 V$TEMPSEG_USAGE 动态性能视图。这个视图是诊断临时表空间（TEMP）使用情况的核心和关键，它揭示了是谁、在做什么操作、消耗了多少临时空间。

1. 作用与使用场景

作用：
V$TEMPSEG_USAGE 动态性能视图用于实时显示当前实例中所有正在消耗临时表空间的操作和会话。它跟踪了哪些 SQL 操作（如排序、哈希连接、临时表、索引创建等）正在使用临时段（Temporary Segments），并详细说明了其空间消耗情况。

核心使用场景：

1. 故障排查与性能诊断：当临时表空间空间不足（ORA-01652、ORA-1652），或发现临时表空间文件异常增长时，首先查询此视图，快速定位罪魁祸首（哪个会话、哪个SQL）。
2. 监控与优化：监控系统中哪些 SQL 语句是“临时空间消耗大户”，识别出需要优化的 SQL（如是否缺少索引导致大量排序，是否可调整 PGA_AGGREGATE_TARGET 参数）。
3. 资源管控：识别并管理长时间占用大量临时空间的会话或操作，必要时可以终止会话以释放资源，保证系统稳定。
4. 容量规划：了解临时空间的使用模式和峰值，为临时表空间的容量规划提供数据支持。

2. 字段含义详解

下表列出了 V$TEMPSEG_USAGE 视图中各字段的详细含义。请注意，该视图只显示当前正在进行的、正在消耗临时空间的操作。一旦操作完成，对应的记录就会消失。

字段名称	数据类型	含义与说明
SESSION_ADDR	RAW(8)	会话对象的地址（内部标识符）。通常与 V$SESSION 的 SADDR 字段关联。
SESSION_NUM	NUMBER	会话标识符 (Session Identifier)，即 SID。这是与 V$SESSION 视图关联最常用的字段。
SERIAL_NUM	NUMBER	会话序列号 (Session Serial Number)，即 SERIAL#。与 SID 结合，唯一地标识一个会话，防止误操作因 SID 重用而指向错误的会话。
SQL_ID	VARCHAR2(13)	正在执行并消耗临时空间的 SQL 语句的 ID。这是与 V$SQL 或 DBA_HIST_SQLTEXT 关联以获取 SQL 文本的关键字段。
SQL_EXEC_START	DATE	该 SQL 语句开始执行的时间。
SQL_PLAN_HASH_VALUE	NUMBER	SQL 执行计划的哈希值。与 SQL_ID 结合，可以唯一确定一个执行计划。用于判断是否因执行计划改变导致临时空间使用激增。
SQL_CHILD_ADDRESS	RAW(8)	子游标的地址（内部使用）。
SEGFILE#	NUMBER	临时段所在的临时文件的文件号。与 V$TEMPFILE 或 DBA_TEMP_FILES 中的 FILE_ID 关联。
SEGBLK#	NUMBER	临时段在临时文件中的起始块号。
BLOCKS	NUMBER	该操作当前已使用的临时段中的磁盘块数。这是评估空间占用的核心指标之一。
EXTENTS	NUMBER	该操作当前已分配的区（Extent）的数量。
SEGTYPE	VARCHAR2(20)	临时段的类型。极其重要的字段，直接说明了操作的类型。常见值： • SORT：排序操作 • HASH：哈希连接 • DATA：全局临时表数据 • INDEX：全局临时表索引 • LOB_DATA：临时 LOB 数据 • LOB_INDEX：临时 LOB 索引 • PQ SORT / PQ HASH：并行查询的排序/哈希
SEGSIZE	NUMBER	以字节为单位的临时段大小。由 BLOCKS * 块大小 计算得出。
TABLESPACE	VARCHAR2(31)	临时段所在的表空间名称（通常是 TEMP）。
CONTENTS	VARCHAR2(9)	段的内容类型。对于临时段，此值始终为 TEMPORARY。
CON_ID	NUMBER	容器 ID。在多租户环境（CDB）中，标识该操作属于哪个可插拔数据库（PDB）。对于非 CDB 数据库，此值为 0。
PROGRAM	VARCHAR2(48)	会话对应的客户端程序名称（如 sqlplus.exe）。此信息从关联的 V$SESSION 中获取。
MODULE	VARCHAR2(64)	应用程序设置的模块名称（如通过 DBMS_APPLICATION_INFO.SET_MODULE）。
ACTION	VARCHAR2(64)	应用程序设置的动作名称。
CLIENT_INFO	VARCHAR2(64)	客户端信息。
TABLESPACE_NAME	VARCHAR2(31)	同 TABLESPACE。
SIZE_BYTES	NUMBER	同 SEGSIZE。

3. 相关视图与基表

• 相关视图：

  • V$SESSION：最重要的关联视图。通过 SESSION_NUM (SID) 和 SERIAL_NUM (SERIAL#) 关联，可以获取会话的详细信息，如 USERNAME, OSUSER, MACHINE, PROGRAM, STATUS 等。
  • V$SQL / DBA_HIST_SQLTEXT：通过 SQL_ID 关联，获取消耗大量临时空间的 SQL 语句的完整文本，用于分析优化。
  • V$TEMPFILE / DBA_TEMP_FILES：通过 SEGFILE# (FILE_ID) 关联，查看临时文件的具体信息。
  • V$SORT_SEGMENT：显示临时表空间中所有排序段的状态信息（更底层的段视角）。
  • V$TEMPORARY_LOBS：当 SEGTYPE 为 LOB_DATA 或 LOB_INDEX 时，可以关联此视图获取更详细的 LOB 信息。

• 基表：
  V$TEMPSEG_USAGE 是一个动态性能视图（GV$ 表示全局，用于 RAC），其数据来源于实例的内存结构。它通常基于一个名为 X$KTSSO（或类似名称，Oracle 未公开）的 X$ 表。这些 X$ 表是 Oracle 内核中的内部内存结构，绝对不建议用户直接查询。

4. 底层原理与知识点介绍

1. 为什么需要临时空间？
当 SQL 操作（如 ORDER BY, GROUP BY, DISTINCT, UNION, 哈希连接、创建索引等）需要处理的数据量太大，无法在内存（PGA）中完成时，Oracle 会将中间结果写入磁盘——即临时表空间。

2. 临时段（Temporary Segment）的管理：

• 临时表空间是本地管理表空间（LMT），使用临时文件（Tempfile）。
• 临时段在临时表空间中动态创建和销毁。第一个需要磁盘空间的操作会创建排序段，该段可以被同一实例上的多个排序操作共享使用。
• 临时段由区（Extent） 组成，区是按需分配的。

3. 工作流程（以排序为例）：

1. SQL 语句执行，需要进行排序。
2. 数据库尝试在 PGA 的排序区（Sort Area）中进行排序。
3. 如果数据量超过 PGA_AGGREGATE_TARGET 或 SORT_AREA_SIZE 的限制，数据库将分配一个临时段，并将部分排序数据写入临时表空间（磁盘排序）。
4. 此时，V$TEMPSEG_USAGE 中会出现一条记录，SEGTYPE 为 SORT，BLOCKS 会不断增加。
5. 当排序完成，最终结果返回给用户后，临时段占用的空间不会被立即释放给操作系统，而是被标记为“空闲”，可供同一实例中的其他操作重用。
6. 对应的记录从 V$TEMPSEG_USAGE 中消失。
7. 只有当实例关闭时，临时表空间的空间才会被彻底释放（临时文件本身不会缩小，除非手动操作）。

4. 关键知识点：

• 动态性：该视图只显示当前正在发生的磁盘操作。历史消耗无法通过此视图查看。
• 空间重用：临时表空间的空间是重用的，而不是释放。这是临时表空间文件只增不减的原因。
• PGA 的重要性：合理设置 PGA_AGGREGATE_TARGET 参数至关重要。足够大的 PGA 可以减少磁盘排序，极大提升性能并减轻临时表空间压力。
• 并行查询：并行执行（PQ）操作会分配多个从属进程（Slave Processes）共同完成一个任务，每个进程都可能消耗临时空间，SEGTYPE 会显示为 PQ SORT 等。

5. 常用查询 SQL

1. 查看当前所有消耗临时空间的操作（最常用、最关键查询）

SELECT 
    u.SESSION_NUM as SID,
    u.SERIAL_NUM as SERIAL#,
    s.USERNAME,
    s.OSUSER,
    s.MACHINE,
    s.PROGRAM,
    u.SQL_ID,
    u.SEGTYPE,
    ROUND(u.BLOCKS * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024, 2) AS SIZE_MB,
    u.EXTENTS,
    u.SQL_EXEC_START,
    u.TABLESPACE
FROM V$TEMPSEG_USAGE u
JOIN V$SESSION s ON u.SESSION_NUM = s.SID AND u.SERIAL_NUM = s.SERIAL#
ORDER BY SIZE_MB DESC;

2. 查找正在运行且消耗临时空间最多的 SQL 语句

SELECT 
    u.SQL_ID,
    sq.SQL_TEXT,
    sq.SQL_FULLTEXT,
    ROUND(SUM(u.BLOCKS * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024, 2)) AS TOTAL_TEMP_MB,
    COUNT(*) AS NUM_OPERATIONS
FROM V$TEMPSEG_USAGE u
JOIN V$SQL sq ON u.SQL_ID = sq.SQL_ID
GROUP BY u.SQL_ID, sq.SQL_TEXT, sq.SQL_FULLTEXT
HAVING SUM(u.BLOCKS) > 0
ORDER BY TOTAL_TEMP_MB DESC;

3. 按段类型分组，查看空间使用分布

SELECT 
    SEGTYPE,
    COUNT(*) AS OPERATIONS,
    SUM(BLOCKS) AS TOTAL_BLOCKS,
    ROUND(SUM(BLOCKS * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024, 2) AS TOTAL_MB
FROM V$TEMPSEG_USAGE
GROUP BY SEGTYPE
ORDER BY TOTAL_MB DESC;

4. 监控特定用户的临时空间使用（例如 ‘APP_USER’）

SELECT 
    s.USERNAME,
    u.SQL_ID,
    u.SEGTYPE,
    ROUND(u.BLOCKS * 8 / 1024, 2) AS SIZE_MB -- 假设块大小为8K
FROM V$TEMPSEG_USAGE u
JOIN V$SESSION s ON u.SESSION_NUM = s.SID AND u.SERIAL_NUM = s.SERIAL#
WHERE s.USERNAME = 'APP_USER'
ORDER BY SIZE_MB DESC;

总结：
V$TEMPSEG_USAGE 是管理和优化 Oracle 临时表空间的雷达。它直接将临时空间的高消耗与具体的 SQL 语句和用户会话关联起来。掌握这个视图的使用，是每一位 DBA 进行性能调优和故障排查的必备技能。核心思路永远是：通过 SESSION_NUM/SERIAL_NUM 关联 V$SESSION 找到源头，通过 SQL_ID 关联 V$SQL 找到根本原因，关注 SEGTYPE 和 BLOCKS 评估影响。

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