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

好的，我们来详细解析一下 Oracle 19C 数据库中的 V$TEMPORARY_LOBS 动态性能视图。这个视图是管理和诊断与临时 LOB (大型对象) 相关性能问题的关键工具。

1. 作用与使用场景

作用：
V$TEMPORARY_LOBS 动态性能视图用于显示当前实例中所有已创建的临时 LOB 数据的信息。它提供了会话级别对临时 LOB 的使用和占用空间的实时监控。

核心使用场景：

1. 性能诊断与排查：当发现临时表空间（TEMP）异常增长或磁盘空间不足时，使用此视图可以快速定位是否是某个会话或 SQL 语句创建了大量的临时 LOB 未能及时释放。
2. 监控与优化：监控哪些应用程序模块或用户会话正在频繁使用临时 LOB，评估其对临时表空间的压力，从而进行代码优化（如改进 LOB 处理逻辑、及时释放 LOB）。
3. 资源管理：识别并管理长时间持有大量临时 LOB 空间的会话，必要时可以终止这些会话以释放资源。
4. 理解行为：帮助开发者和 DBA 理解应用程序中临时 LOB 的生命周期和占用模式。

2. 字段含义详解

下表列出了 V$TEMPORARY_LOBS 视图中各字段的详细含义。请注意，该视图的内容是实例级别的，且数据在会话结束后消失。

字段名称	数据类型	含义与说明
SID	NUMBER	会话标识符 (Session Identifier)。持有该临时 LOB 的会话的 SID。这是与 V$SESSION 视图关联的关键字段。
SERIAL#	NUMBER	会话序列号 (Session Serial Number)。用于唯一标识一个会话。如果会话断开并重新连接，SID 可能相同，但 SERIAL# 会递增。与 SID 结合使用，可以精确地定位一个会话，防止误杀重复使用的 SID。
CACHE_LOBS	NUMBER	当前在该会话中被缓存的临时 LOB 的数量。这些 LOB 被存储在缓冲区缓存中，旨在用于重复读取，性能更好，但会消耗更多内存。
NOCACHE_LOBS	NUMBER	当前在该会话中未被缓存的临时 LOB 的数量。这些 LOB 不存储在缓冲区缓存中，直接读写磁盘，适合一次性使用的大对象。
ABSTRACT_LOBS	NUMBER	当前在该会话中的抽象 LOB 的数量（与某些特定高级功能相关，通常数量较少）。
TOTAL_LOBS	NUMBER	当前会话中所有类型的临时 LOB 的总数。通常是 CACHE_LOBS + NOCACHE_LOBS + ABSTRACT_LOBS。
TEMP_BLOCKS	NUMBER	该会话中所有临时 LOB 当前占用的临时表空间中的磁盘块数。这是评估空间占用的最关键指标。
TEMP_BYTES	NUMBER	该会话中所有临时 LOB 当前占用的临时表空间的大小（以字节为单位）。由 TEMP_BLOCKS * 块大小 计算得出。
CON_ID	NUMBER	容器 ID。在多租户环境（CDB）中，标识该临时 LOB 属于哪个可插拔数据库（PDB）。对于非 CDB 数据库，此值为 0。

3. 相关视图与基表

• 相关视图：

  • V$SESSION：这是最重要的关联视图。通过 SID 和 SERIAL# 与 V$TEMPORARY_LOBS 关联，可以获取会话的详细信息，如 USERNAME, OSUSER, MACHINE, PROGRAM, SQL_ID 等，从而准确定位问题来源。
  • V$SORT_USAGE：显示临时段的使用情况，包括排序和临时 LOB 等操作。有时临时 LOB 的占用也会体现在这里，但 V$TEMPORARY_LOBS 提供了更细粒度的 LOB 专属信息。
  • V$TEMPFILE / DBA_TEMP_FILES：查看临时表空间的文件信息。
  • V$TEMP_SPACE_HEADER：查看临时文件的空间使用摘要。

• 基表：
  V$TEMPORARY_LOBS 是一个动态性能视图，其数据来源于实例的内存结构（SGA），特别是关于会话和临时 LOB 分配的内部状态。它通常基于一个名为 X$KTCLO（或类似名称，Oracle 未公开）的 X$ 表。X$ 表是 Oracle 内核中的内部内存结构，绝对不建议用户直接查询。

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

1. 临时 LOB 是什么？
临时 LOB 是存储在数据库临时表空间中的 LOB 定位器（Locator）和其对应的数据。它们由应用程序或 SQL 操作在会话期间动态创建（例如，使用 DBMS_LOB.CREATETEMPORARY），用于临时处理大型数据。当会话结束（正常断开或异常终止）或显式调用 DBMS_LOB.FREETEMPORARY 后，临时 LOB 占用的空间会被 Oracle 自动回收。

2. 存储机制：
临时 LOB 的数据并不存储在普通的表段中，而是存储在临时表空间的排序段（Sort Segments） 中。这与大规模磁盘排序操作使用的空间相同。

3. CACHE vs NOCACHE：

• CACHE：将临时 LOB 的数据页缓存在缓冲区缓存（Buffer Cache）中。这适合于需要对 LOB 进行多次读取操作的场景，可以减少磁盘 I/O，但会消耗 SGA 内存。
• NOCACHE：不缓存数据页，每次读写都直接与磁盘上的临时文件交互。这适合于一次性写入和读取的大对象，节省内存，但会增加磁盘 I/O。

4. 空间管理：
临时 LOB 的空间分配是按需的。创建临时 LOB 时，并不会立即分配 TEMP_BYTES 所显示的全部空间。临时文件是稀疏文件（Sparse File），物理磁盘空间是随着数据实际写入而逐步分配的。V$TEMPORARY_LOBS 中的 TEMP_BYTES 表示的是当前已实际分配的空间。

5. 释放机制：
这是一个关键知识点：临时 LOB 的释放是 Oracle 的职责，但前提是会话必须结束。在一个长会话中，即使调用了 DBMS_LOB.FREETEMPORARY 或 LOB 定位器超出作用域，Oracle 也可能不会立即将物理空间返还给临时表空间以供重用。它更可能将这些空间标记为“可重用”，并在该会话后续的临时 LOB 操作中优先使用。这意味着一个设计不良的、持续运行且不断创建临时 LOB 的会话（例如，在循环中），可能会导致临时表空间使用量不断增长，直到会话结束才彻底释放。

5. 常用查询 SQL

1. 查看当前所有使用临时 LOB 的会话及其空间占用（最关键查询）

SELECT 
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.OSUSER,
    s.MACHINE,
    s.PROGRAM,
    s.SQL_ID,
    t.CACHE_LOBS,
    t.NOCACHE_LOBS,
    t.TOTAL_LOBS,
    ROUND(t.TEMP_BYTES / 1024 / 1024, 2) AS TEMP_MB,
    t.TEMP_BLOCKS
FROM V$TEMPORARY_LOBS t
JOIN V$SESSION s ON t.SID = s.SID AND t.SERIAL# = s.SERIAL#
ORDER BY t.TEMP_BYTES DESC;

2. 查找占用临时表空间最多的前10个会话（包含详细信息）

SELECT 
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.MODULE, -- 应用程序模块
    s.ACTION,
    s.CLIENT_IDENTIFIER,
    ROUND(t.TEMP_BYTES / 1024 / 1024, 2) AS TEMP_USED_MB,
    q.SQL_TEXT
FROM V$TEMPORARY_LOBS t
JOIN V$SESSION s ON t.SID = s.SID AND t.SERIAL# = s.SERIAL#
LEFT JOIN V$SQL q ON s.SQL_ID = q.SQL_ID
WHERE ROWNUM <= 10
ORDER BY t.TEMP_BYTES DESC;

3. 监控特定用户或程序的临时 LOB 使用情况

SELECT 
    s.USERNAME,
    s.PROGRAM,
    SUM(t.CACHE_LOBS) AS Total_Cache_LOBs,
    SUM(t.NOCACHE_LOBS) AS Total_NoCache_LOBs,
    ROUND(SUM(t.TEMP_BYTES) / 1024 / 1024, 2) AS Total_Temp_MB
FROM V$TEMPORARY_LOBS t
JOIN V$SESSION s ON t.SID = s.SID AND t.SERIAL# = s.SERIAL#
WHERE s.USERNAME = 'YOUR_APP_USER' -- 替换为具体的用户名
GROUP BY s.USERNAME, s.PROGRAM;

4. 结合 SQL 查询，找到产生大量临时 LOB 的 SQL 语句

SELECT 
    s.SQL_ID,
    q.SQL_FULLTEXT, -- 或者 SQL_TEXT，注意SQL_FULLTEXT是CLOB
    SUM(t.TEMP_BYTES) TOTAL_BYTES,
    COUNT(*) NUM_SESSIONS
FROM V$TEMPORARY_LOBS t
JOIN V$SESSION s ON t.SID = s.SID AND t.SERIAL# = s.SERIAL#
JOIN V$SQL q ON s.SQL_ID = q.SQL_ID
GROUP BY s.SQL_ID, q.SQL_FULLTEXT
HAVING SUM(t.TEMP_BYTES) > 100 * 1024 * 1024 -- 查找使用了超过100MB临时LOB空间的SQL
ORDER BY TOTAL_BYTES DESC;

总结：
V$TEMPORARY_LOBS 是管理临时 LOB 不可或缺的视图，它直接将临时表空间的空间消耗与具体的用户会话和 SQL 语句关联起来。通过熟练使用它，可以有效地诊断和解决因临时 LOB 使用不当导致的临时表空间膨胀和性能问题。核心思路是：通过 SID 和 SERIAL# 关联 V$SESSION，找到是谁（会话）、在做什么（SQL）、占用了多少资源（TEMP_BYTES）。

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