面试宝典:介绍下Oracle数据库动态性能视图 V$TEMPSTAT-优快云博客

在这里插入图片描述
好的，我们来全面、深入地解析 Oracle 19C 数据库中的 V$TEMPSTAT 动态性能视图。这个视图是评估临时表空间 I/O 性能和数据文件使用分布的关键工具。

1. 作用与使用场景

作用：
V$TEMPSTAT 动态性能视图用于统计和显示当前实例中所有临时文件（Tempfile）的读写操作信息。它提供了从实例启动以来，针对每个临时文件的物理读写次数和数据量的累计统计。其核心作用是监控和评估临时表空间的 I/O 性能和使用均衡性。

核心使用场景：

I/O 性能分析与瓶颈识别：当数据库性能低下，怀疑瓶颈在于临时表空间的磁盘 I/O 时（例如，大量磁盘排序、哈希连接或临时表操作），通过此视图可以确认临时文件的读写负载，找到热点文件。
负载均衡评估：在临时表空间由多个临时文件组成的情况下，检查这些文件之间的 I/O 操作是否分布均匀。不均匀的分布可能意味着文件大小不一致或存储配置有问题。
存储规划与优化：根据各临时文件的 I/O 压力，为后续的存储规划（如将高负载的临时文件迁移到更高性能的磁盘上）提供数据依据。
历史性能基线对比：通过定期采样此视图的数据，可以建立临时表空间 I/O 的性能基线，用于未来某个时间点性能问题的对比分析。

2. 字段含义详解

V$TEMPSTAT 的统计维度是 FILE#（文件号） 和 STATISTIC（统计类型）。下表列出了该视图中的所有字段及其详细含义。

字段名称	数据类型	含义与说明
`FILE#`	NUMBER	临时文件的文件编号。这是与 `V$TEMPFILE` 和 `DBA_TEMP_FILES` 视图中的 `FILE_ID` 关联的关键字段。
`STATISTIC`	VARCHAR2(21)	统计项目的名称。这是理解该行数据含义的核心。主要分为以下几类： • `physical reads`: 物理读取次数。从该临时文件读取数据块的次数。 • `physical writes`: 物理写入次数。向该临时文件写入数据块的次数。 • `physical blocks read`: 物理读取的块数。从该临时文件读取的数据块总数。 • `physical blocks written`: 物理写入的块数。向该临时文件写入的数据块总数。 • `bytes read`: 读取的字节数。从该临时文件读取的数据总字节数。 • `bytes written`: 写入的字节数。向该临时文件写入的数据总字节数。
`VALUE`	NUMBER	该统计项目对应的累计值。例如，如果 `STATISTIC` 是 `'physical reads'`，那么 `VALUE` 就是从实例启动以来，对该文件发生物理读操作的次数。
`CON_ID`	NUMBER	容器 ID。在多租户环境（CDB）中，标识该统计信息属于哪个可插拔数据库（PDB）。对于非 CDB 数据库，此值为 0。

重要说明：bytes read/written 的值通常可以由 physical blocks read/written * db_block_size 计算得出。直接提供该字段是为了方便使用。

3. 相关视图与基表

相关视图：
- V$TEMPFILE：最重要的关联视图。通过 FILE# 与 V$TEMPFILE.FILE# 关联，可以获取临时文件的详细信息，如 NAME（文件路径）、SIZE（当前大小）、STATUS 等。
- DBA_TEMP_FILES：数据字典视图，提供临时文件的持久化信息（如属于哪个表空间、是否自动扩展等）。
- V$SORT_SEGMENT：显示临时表空间中排序段的信息，提供了段的视角，而 V$TEMPSTAT 提供了文件的 I/O 视角。
- V$TEMPSEG_USAGE：显示当前正在使用临时段的会话和 SQL，与 V$TEMPSTAT 结合可以知道当前的 I/O 压力来自谁。
基表：
V$TEMPSTAT 是一个动态性能视图（GV$TEMPSTAT 用于 RAC 环境），其数据来源于实例的内存结构（SGA）。它通常基于一个名为 X$KCFIO（或类似名称，Oracle 未公开）的 X$ 表。这些 X$ 表是 Oracle 内核中的内部内存结构，用于跟踪文件 I/O 事件，绝对不建议用户直接查询。

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

1. 临时表空间的 I/O 特性：
与普通数据文件的 I/O 不同，临时文件的 I/O 有其独特之处：

直接路径 I/O（Direct I/O）：临时文件的读写通常 bypasses the buffer cache（绕过缓冲区缓存），采用直接路径 I/O。这意味着数据直接在 PGA 和磁盘之间传输，减少了 SGA 的争用，但也意味着这些操作完全是物理 I/O。V$TEMPSTAT 统计的正是这些物理 I/O。
异步 I/O：Oracle 会尽可能使用异步 I/O（如果平台和配置支持）来操作临时文件，以提升并发性能。
无重做日志：对临时文件的写入不产生重做日志（Redo Log），最多只产生少量撤销（Undo），这提升了写入速度。

2. 统计信息的收集与重置：

V$TEMPSTAT 中的数据是累计值，从实例启动开始统计，并一直累加。
这些统计信息存储在内存中，实例关闭后会被重置。
在 RAC 环境中，GV$TEMPSTAT 会显示所有实例的统计信息，每个实例的 INST_ID 不同。

3. 与排序/哈希操作的关系：
当 SQL 操作（如 ORDER BY, GROUP BY, 哈希连接等）需要的内存（PGA）不足时，Oracle 会将中间结果写入临时表空间，这个过程称为 “写入磁盘”（ writing to disk）。每次将一批数据（一个区或多个块）写入临时文件或从中读取，都会导致 V$TEMPSTAT 中相应统计项的数值增加。

4. 性能关联：
高频率的 physical reads 和 physical writes 通常意味着：

PGA 配置不足：PGA_AGGREGATE_TARGET 设置过小，无法容纳大量的排序或哈希操作，导致频繁的磁盘溢出。
存在大量消耗临时空间的 SQL：需要优化 SQL 或索引，减少排序和数据中间集的大小。
存储 I/O 性能瓶颈：即使操作需要临时空间，如果底层磁盘的 I/OPS 或吞吐量（Throughput）不足，也会成为性能瓶颈。此时需要关注存储性能。

5. 常用查询 SQL

1. 查看所有临时文件的 I/O 统计概况（最常用查询）
此查询将 V$TEMPSTAT 的行式数据转换为以文件为单位的列式数据，更易读。

SELECT 
    ts.FILE#,
    tf.NAME AS FILE_NAME,
    SUM(DECODE(ts.STATISTIC, 'physical reads', ts.VALUE, 0)) AS Physical_Reads,
    SUM(DECODE(ts.STATISTIC, 'physical writes', ts.VALUE, 0)) AS Physical_Writes,
    ROUND(SUM(DECODE(ts.STATISTIC, 'bytes read', ts.VALUE, 0)) / 1024 / 1024, 2) AS MB_Read,
    ROUND(SUM(DECODE(ts.STATISTIC, 'bytes written', ts.VALUE, 0)) / 1024 / 1024, 2) AS MB_Written,
    ROUND((SUM(DECODE(ts.STATISTIC, 'bytes read', ts.VALUE, 0)) + 
           SUM(DECODE(ts.STATISTIC, 'bytes written', ts.VALUE, 0))) / 1024 / 1024, 2) AS Total_MB
FROM V$TEMPSTAT ts
JOIN V$TEMPFILE tf ON ts.FILE# = tf.FILE#
GROUP BY ts.FILE#, tf.NAME
ORDER BY Total_MB DESC; -- 按总I/O量降序排列，快速找到最繁忙的文件

2. 检查临时文件间的 I/O 负载是否均衡

SELECT 
    FILE#,
    STATISTIC,
    VALUE,
    ROUND(RATIO_TO_REPORT(VALUE) OVER (PARTITION BY STATISTIC) * 100, 2) AS PERCENTAGE
FROM V$TEMPSTAT
WHERE STATISTIC IN ('physical reads', 'physical writes')
ORDER BY STATISTIC, FILE#;

这个查询使用 RATIO_TO_REPORT 分析函数来计算每个文件的读写操作占所有文件总操作的百分比。如果某个文件的百分比远高于其他文件，说明负载不均衡。

3. 计算临时文件的平均 I/O 大小

SELECT 
    FILE#,
    CASE WHEN SUM(DECODE(STATISTIC, 'physical reads', VALUE, 0)) > 0 
         THEN ROUND(SUM(DECODE(STATISTIC, 'bytes read', VALUE, 0)) / 
                    SUM(DECODE(STATISTIC, 'physical reads', VALUE, 0)) / 1024, 2)
         ELSE 0 
    END AS Avg_Read_Size_KB,
    CASE WHEN SUM(DECODE(STATISTIC, 'physical writes', VALUE, 0)) > 0 
         THEN ROUND(SUM(DECODE(STATISTIC, 'bytes written', VALUE, 0)) / 
                    SUM(DECODE(STATISTIC, 'physical writes', VALUE, 0)) / 1024, 2)
         ELSE 0 
    END AS Avg_Write_Size_KB
FROM V$TEMPSTAT
GROUP BY FILE#
ORDER BY FILE#;

平均 I/O 大小可以帮助你了解数据库是倾向于执行大量小 I/O 还是一次性大 I/O。

4. 在 RAC 环境中查看每个实例的临时文件 I/O（使用 G$V）

SELECT 
    INST_ID,
    FILE#,
    STATISTIC,
    VALUE
FROM GV$TEMPSTAT
WHERE STATISTIC = 'physical writes'
ORDER BY INST_ID, FILE#;

这可以帮你判断是否某个 RAC 实例承担了更多的临时 I/O 负载。

总结：
V$TEMPSTAT 视图是 DBA 洞察临时表空间 I/O 行为的性能仪表盘。它不关心空间被谁占用（那是 V$TEMPSEG_USAGE 的工作），而是专注于这些空间是如何被访问的。通过分析其数据，可以有效地诊断出与临时表空间相关的 I/O 瓶颈、评估存储配置的合理性，并为系统的整体性能调优提供关键指标。核心思路是：通过 FILE# 关联 V$TEMPFILE 找到具体文件，通过 STATISTIC 和 VALUE 分析 I/O 模式和负载。

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