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

在这里插入图片描述
好的，我们来对 Oracle 19C 数据库中的 V$OBJECT_DML_FREQUENCIES 动态性能视图进行一次全面而深入的解析。这个视图对于数据库性能优化、容量规划和管理至关重要。

1. 作用与概述

V$OBJECT_DML_FREQUENCIES 视图用于展示自实例启动以来，数据库中各个对象（如表、分区）上发生的数据操作语言（DML）操作的频率统计信息。

核心目的：识别“热点”对象，即那些被频繁插入（INSERT）、更新（UPDATE）、删除（DELETE）的对象。这对于性能调优、存储设计和数据库管理具有极高的价值。
数据来源：该视图中的数据来源于数据库在运行时对 SGA 中 DML 操作的采样和统计。它反映了实例级别的活动，实例重启后数据会被重置。
统计粒度：统计可以精确到表级或分区级。如果一个表被分区，你可以看到每个分区的 DML 频率，这对于分区维护策略尤其有用。

2. 使用场景

此视图在以下场景中极为有用：

性能调优与瓶颈识别：
- 识别热点表：快速找出系统中DML操作最频繁的表，这些表通常是锁竞争、日志写入（redo）和缓冲区缓存（buffer cache）活动的焦点，是性能调优的首要目标。
- 评估索引策略：频繁更新的表上的大量索引会带来严重的维护开销。此视图可以帮助评估这些开销是否合理。
分区策略设计与评估：
- 识别热点分区：对于分区表，可以精确找到哪些分区是DML活动的热点。这有助于评估当前的分区键（如时间字段）是否有效，是否需要进行分区维护（如迁移热分区到高性能存储）。
- 支持间隔分区（Interval Partitioning）：优化器可能会使用这些统计信息来指导间隔分区的创建决策。
容量与存储规划：
- 频繁插入的表可能增长更快，需要更多的存储空间和更频繁的统计信息收集。
- 频繁更新的表可能产生更多的undo数据和redo日志。
数据库管理：
- 决定哪些表需要设置更高的FREQUENCY（自动统计信息收集策略）或使用实时统计（Real-Time Statistics）。
- 验证应用程序的行为是否符合预期（例如，某个后台作业是否真的在大量更新它声称要更新的表）。

3. 字段含义详解

字段名称	数据类型	含义说明
OBJECT_ID	NUMBER	对象的唯一标识符（Data Object ID）。可与 `DBA_OBJECTS` 关联以获取对象名。注意：对于分区，这是分区的对象ID，而不是表的ID。
DATAOBJ_ID	NUMBER	数据对象的标识符。通常与 `OBJECT_ID` 相同，但对于某些对象类型（如IOT）可能不同。
TABLESPACE_ID	NUMBER	对象所在表空间的ID。可与 `DBA_TABLESPACES` 关联。
TABLE_NAME	VARCHAR2(128)	对象的名称。对于分区，此字段为NULL。
PARTITION_NAME	VARCHAR2(128)	分区的名称。如果对象不是分区或统计在表级，则为NULL。
INSERT_FREQUENCY	NUMBER	该对象上发生INSERT操作的近似次数。
UPDATE_FREQUENCY	NUMBER	该对象上发生UPDATE操作的近似次数。
DELETE_FREQUENCY	NUMBER	该对象上发生DELETE操作的近似次数。
DML_FREQUENCY	NUMBER	该对象上发生的所有DML操作的总近似次数（`INSERT_FREQUENCY + UPDATE_FREQUENCY + DELETE_FREQUENCY`）。
AVG_ROWCNT_PER_DML	NUMBER	每次DML操作平均影响的行数。这是一个平均值，实际操作可能影响多行（如批量操作）。
SAMPLE_PERCENT	NUMBER	用于计算这些统计信息的采样百分比。Oracle使用采样而非全量记录以保证性能开销极小。
LAST_DML_TIME	TIMESTAMP(3)	该对象上最后一次发生DML操作的时间。

4. 相关视图与基表

相关数据字典视图：
- DBA_OBJECTS / DBA_TABLES / DBA_TAB_PARTITIONS：用于通过 OBJECT_ID 或 DATAOBJ_ID 关联，获取对象的详细名称、所有者等信息。
- DBA_TABLESPACES：通过 TABLESPACE_ID 关联获取表空间名称。
- DBA_HIST_SEG_STAT： AWR历史快照中的段级统计信息，提供了类似但更持久的历史数据，可用于跨时间范围的分析。
- WRH$_SEG_STAT： AWR快照数据的基表，存储了详细的段级统计信息。
基表（Underlying Base Table）：
- X $K E W RTB A L * * ：这是 ‘ V$ OBJECT_DML_FREQUENCIES` 所依赖的主要**底层内存结构（基表）。它存储了实例运行时收集的DML频率统计信息。
- **X $K E W RTP A RT * * ：另一个相关的 X$ 表，可能存储分区级别的统计信息。
- 重要提示：与所有X $表一样，这些是 O r a c l e 内部的、未公开的、基于内存的结构。它们的结构可能随版本变化， * * 严禁在生产环境中直接查询 * * 。其存在是为了支持 V$ 视图的功能。
- 视图定义查询：
```
SELECT view_definition FROM v$fixed_view_definition WHERE view_name = 'GV$OBJECT_DML_FREQUENCIES';
```

5. 底层详细原理

数据收集机制（采样，非全量）：
- Oracle 数据库内核在执行 DML 语句（INSERT, UPDATE, DELETE）时，并不会为每一行操作都更新一个计数器，那样开销极大。
- 取而代之的是一种轻量级的采样机制。系统会以很低的频率（由内部算法控制）对正在执行的 DML 操作进行采样，并记录到 SGA 的一个专用内存区域（即 X$KEWRTBAL 和 X$KEWRTPART）。
- SAMPLE_PERCENT 字段表明这是一个统计估算值，而非精确值。但对于识别热点对象的目的来说，其精度已经足够。
统计信息生命周期：
- 这些统计信息存储在内存中，其生命周期与实例的生命周期相同。
- 实例重启后，所有累积值都会被重置为0。这是因为这些统计信息旨在反映当前实例运行期间的访问模式。
与优化器的关系：
- 这些频率信息被提供给 Oracle 优化器，用于辅助某些决策。
- 自动统计信息收集：对于 DML 频率很高的对象，优化器可能会更频繁地触发统计信息收集，以确保执行计划的准确性。
- 实时统计：在 SQL 语句执行期间，如果优化器发现表的统计信息已过时，它可以使用从这些动态性能视图中获取的实时 DML 信息来辅助成本计算。
- 间隔分区：当创建新的间隔分区时，优化器可能会参考这些信息。

6. 相关知识点介绍

DML 与 Redo/Undo：频繁的 DML 操作会直接产生大量的 redo 日志和 undo 数据。识别出热点对象后，DBA 应同时关注这些方面的性能（如 redo log 文件的 IOPs、undo 表空间的大小和 retention）。
高水位线（HWM）：频繁的 INSERT 操作会导致表的高水位线不断推进，即使 DELETE 操作删除了大量数据，高水位线也不会下降，可能影响全表扫描的性能。这对于识别需要执行 SHRINK SPACE 或 MOVE 操作的表很有用。
锁竞争：热点更新/删除表是发生行级锁竞争和队列（enqueue）等待的潜在区域。监控这些表的相关等待事件（如 enq: TX - row lock contention）非常重要。
AWR 与持久化： V$OBJECT_DML_FREQUENCIES 提供的是实时内存数据。而 AWR 快照会定期将 WRH$_SEG_STAT 中的段级统计信息（包括逻辑读、物理读、DML 变化等）持久化到磁盘，可用于历史趋势分析。

7. 常用查询 SQL

1. 查询实例中DML最频繁的热点对象（TOP-N查询）

SELECT 
    o.owner,
    o.object_name,
    o.subobject_name AS partition_name,
    o.object_type,
    f.dml_frequency,
    f.insert_frequency,
    f.update_frequency,
    f.delete_frequency,
    f.avg_rowcnt_per_dml,
    f.last_dml_time
FROM v$object_dml_frequencies f
JOIN dba_objects o ON f.dataobj_id = o.data_object_id
WHERE f.dml_frequency > 0
ORDER BY f.dml_frequency DESC
FETCH FIRST 20 ROWS ONLY;

2. 专注于查找特定表空间中的热点对象

SELECT 
    t.name AS tablespace_name,
    o.owner,
    o.object_name,
    f.dml_frequency
FROM v$object_dml_frequencies f
JOIN dba_objects o ON f.dataobj_id = o.data_object_id
JOIN v$tablespace t ON f.tablespace_id = t.ts#
WHERE f.dml_frequency > 0
ORDER BY f.dml_frequency DESC;

3. 分析分区表的DML分布，找出热点分区

SELECT 
    t.table_owner,
    t.table_name,
    f.partition_name,
    f.dml_frequency,
    f.insert_frequency,
    f.update_frequency,
    f.delete_frequency,
    f.last_dml_time
FROM v$object_dml_frequencies f
JOIN dba_tab_partitions t ON f.partition_name = t.partition_name 
                         AND f.dataobj_id = t.data_object_id
WHERE f.dml_frequency > 0
ORDER BY t.table_name, f.dml_frequency DESC;

4. 检查哪些对象有大量的DELETE操作（可能需要进行碎片整理）

SELECT 
    o.owner,
    o.object_name,
    f.delete_frequency,
    f.dml_frequency,
    ROUND((f.delete_frequency / NULLIF(f.dml_frequency, 0)) * 100, 2) AS delete_pct
FROM v$object_dml_frequencies f
JOIN dba_objects o ON f.dataobj_id = o.data_object_id
WHERE f.delete_frequency > 1000
ORDER BY f.delete_frequency DESC;

5. 关联AWR历史数据（需要Diagnostic Pack许可）进行跨时间分析

-- 此查询将当前内存中的热点与AWR历史中的段统计关联，提供更全面的视图
SELECT 
    o.owner,
    o.object_name,
    f.dml_frequency AS "Current_Session_DML",
    (SELECT SUM(s.value) 
     FROM dba_hist_seg_stat s
     JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id AND s.dbid = sn.dbid AND s.instance_number = sn.instance_number
     WHERE s.obj# = o.object_id
     AND s.statistic_name = 'logical reads'
     AND sn.begin_interval_time > SYSDATE - 1) AS "Last_24H_Logical_Reads"
FROM v$object_dml_frequencies f
JOIN dba_objects o ON f.dataobj_id = o.data_object_id
WHERE f.dml_frequency > 10000
ORDER BY f.dml_frequency DESC;