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

最新推荐文章于 2025-11-24 15:52:13 发布

原创最新推荐文章于 2025-11-24 15:52:13 发布 · 636 阅读

20 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述
好的，我们来对 Oracle 19c 数据库中用于追溯历史内存调整操作的 V$SGA_RESIZE_OPS 动态性能视图进行最全面、最深入的解析。

V$SGA_RESIZE_OPS 视图提供了实例启动以来，所有已完成或已取消的 SGA 组件大小调整操作的历史记录。它是 V$SGA_CURRENT_RESIZE_OPS 的“历史档案馆”，让你能够分析过去的自动内存管理 (ASMM/AMM) 行为、识别调整趋势，并为未来的容量规划提供数据支持。

一、字段含义详解

V$SGA_RESIZE_OPS 的字段详细记录了每一次调整操作的完整元数据。

字段名	数据类型	描述	重要说明与解读
COMPONENT	VARCHAR2(64)	被调整的 SGA 组件的名称。	例如：`'shared pool'`, `'DEFAULT buffer cache'`, `'large pool'`。
OPER_TYPE	VARCHAR2(13)	操作的类型。	`GROW`: 组件被扩大。 `SHRINK`: 组件被缩小。
OPER_MODE	VARCHAR2(10)	操作的模式。	`AUTO`: 由 ASMM/AMM 自动发起。 `MANUAL`: 由 DBA 手动发起（如 `ALTER SYSTEM`）。 `DEFERRED`/`IMMEDIATE`: 调整的调度方式。
PARAMETER	VARCHAR2(64)	与此组件对应的初始化参数名。	例如：`'db_cache_size'`, `'shared_pool_size'`。
INITIAL_SIZE	NUMBER	调整操作开始时的组件大小（字节）。
TARGET_SIZE	NUMBER	调整操作最初设定的目标大小（字节）。
FINAL_SIZE	NUMBER	调整操作最终完成时的实际大小（字节）。	由于系统约束，此值可能与 `TARGET_SIZE` 不同。
STATUS	VARCHAR2(16)	操作的最终状态。	`COMPLETED`: 操作已成功完成。 `CANCELLED`: 操作已被取消（例如，由于无法获得所需内存）。
START_TIME	DATE	调整操作开始的时间。
END_TIME	DATE	调整操作结束的时间。
ELAPSED_TIME	NUMBER	操作从开始到结束所花费的时间（秒）。	长时间的操作可能对系统性能有轻微影响。
CON_ID	NUMBER	容器ID。	在多租户环境中标识所属容器。对于非CDB，此值为0。

二、核心原理与底层机制

1. 数据来源与底层基表

V$SGA_RESIZE_OPS 是一个动态性能视图，其数据来源于 SGA 中的一个循环缓冲区，该缓冲区专门用于存储已完成的调整操作历史。

其底层源是 X$ 表，通常是 X$KSMGRS (Kernel Service Memory GRanule ReSize history) 或类似的底层结构。这个 X$ 表映射了 SGA 中用作调整历史日志的内存区域。

工作原理：
1. 操作执行：MMAN (Memory Manager) 进程发起并执行一个 SGA 组件的调整操作（增长或收缩）。
2. 记录创建：当操作开始、正在进行时，其记录存在于 V$SGA_CURRENT_RESIZE_OPS 视图中。
3. 操作完成与归档：当操作完成（或失败取消）后，MMAN 会将该操作的最终状态（STATUS）、FINAL_SIZE、END_TIME 等信息写入历史记录缓冲区，并从当前视图移至本历史视图。
4. 循环覆盖：历史缓冲区的大小是固定的。当新的历史记录产生而缓冲区已满时，最旧的记录会被覆盖。因此，此视图只保留最近一段时间的操作历史，而非实例启动以来的全部记录。
5. 查询：查询此视图即是读取这个历史缓冲区。

2. 自动内存管理 (ASMM/AMM) 的“黑匣子”

此视图是分析 ASMM (Automatic Shared Memory Management) 决策和行为的历史记录仪。

OPER_MODE = 'AUTO'：这些记录揭示了 ASMM 在过去是如何响应工作负载变化的。例如，可以看到它在业务高峰前是否提前扩大了 Buffer Cache。
调整策略分析：通过分析历史，可以判断 ASMM 的调整是否有效和合理。例如，如果看到 Shared Pool 和 Buffer Cache 在频繁地互相“抢夺”内存（一个增长另一个就缩小），可能意味着 SGA_TARGET 总体设置不足，无法同时满足两者需求。
性能问题溯源：如果某个时间点发生了性能抖动，可以查询此历史视图，看是否恰好有一个大型的、耗时的内存调整操作（ELAPSED_TIME 很长）同时发生。

3. 与相关视图的关系

V$SGA_CURRENT_RESIZE_OPS：这是它的“现在进行时”版本。当前视图显示正在发生的操作，历史视图显示已经完成的操作。一条操作记录会从“当前”移动至“历史”。
V$SGA_DYNAMIC_COMPONENTS：显示组件的当前状态（是历史调整的结果）。LAST_OPER_* 字段（如 LAST_OPER_TIME）可以与历史视图中的记录关联起来。
V$SGA_DYNAMIC_FREE_MEMORY：历史调整操作会影响该视图中的 CURRENT_SIZE。例如，一个 SHRINK 操作会向空闲池加入内存，一个 GROW 操作会从空闲池消耗内存。

三、常用查询 SQL 示例

查看最近发生的 SGA 调整操作历史（按时间倒序）

SELECT component, oper_type, oper_mode,
       ROUND(initial_size/1024/1024, 2) AS initial_mb,
       ROUND(target_size/1024/1024, 2) AS target_mb,
       ROUND(final_size/1024/1024, 2) AS final_mb,
       status,
       TO_CHAR(start_time, 'MM-DD HH24:MI:SS') AS start_t,
       TO_CHAR(end_time, 'HH24:MI:SS') AS end_t,
       elapsed_time
FROM v$sga_resize_ops
ORDER BY start_time DESC;

分析由 ASMM 自动发起的所有调整操作

SELECT component, oper_type, COUNT(*) AS operation_count,
       ROUND(AVG(elapsed_time), 2) AS avg_elapsed_sec
FROM v$sga_resize_ops
WHERE oper_mode = 'AUTO'
GROUP BY component, oper_type
ORDER BY operation_count DESC;

找出耗时最长的调整操作（可能对性能影响最大）

SELECT component, oper_type, oper_mode,
       ROUND(final_size/1024/1024, 2) AS final_mb,
       elapsed_time,
       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time
FROM v$sga_resize_ops
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

查询特定组件（如共享池）的调整历史

SELECT oper_type, oper_mode, status,
       ROUND(initial_size/1024/1024, 2) AS initial_mb,
       ROUND(final_size/1024/1024, 2) AS final_mb,
       TO_CHAR(start_time, 'MM-DD HH24:MI') AS start_time,
       elapsed_time
FROM v$sga_resize_ops
WHERE component = 'shared pool'
ORDER BY start_time DESC;

检查是否有被取消 (CANCELLED) 的操作（指示内存请求失败）

SELECT component, oper_type, oper_mode,
       ROUND(target_size/1024/1024, 2) AS target_mb,
       start_time
FROM v$sga_resize_ops
WHERE status = 'CANCELLED'
ORDER BY start_time DESC;
-- 被取消的操作通常意味着系统无法满足调整请求（如空闲内存不足），是SGA压力大的信号。

四、主要应用场景

ASMM/AMM 行为分析与审计：
这是最核心的用途。DBA 可以通过分析历史记录来回答：“过去一段时间，ASMM 都在忙什么？” 这有助于理解数据库的负载模式和学习ASMM的调整策略。
性能问题根本原因分析：
当报告“数据库在某个特定时间点变慢”时，可以查询该时间点附近的调整历史。一个耗时较长（ELAPSED_TIME 大）的 SHRINK 操作（特别是收缩 Buffer Cache）可能会引起短暂的但可感知的性能下降，因为可能需要将脏块急速写入磁盘。
容量规划与趋势预测：
通过分析历史记录，可以看到不同组件的大小范围。例如，如果 Buffer Cache 在每天下午都会自动增长到 5GB，那么就应该考虑将 db_cache_size 的最小值设置为接近这个值，以减少运行时调整的开销和不确定性。
验证手动操作结果：
当 DBA 执行了手动内存调整命令（如 ALTER SYSTEM SET shared_pool_size=...）后，可以通过查询此视图来确认操作是否成功完成 (STATUS='COMPLETED')，以及最终的实际大小 (FINAL_SIZE) 是多少。
诊断配置问题：
如果发现大量操作状态为 CANCELLED，这可能表明 SGA_TARGET 设置过小，无法满足各组件的动态需求，或者某个组件的最小值 (*_size) 设置得过高，限制了ASMM的灵活性。

五、相关视图

V$SGA_CURRENT_RESIZE_OPS：最重要的相关视图。查看当前正在进行的调整操作。
V$SGA_DYNAMIC_COMPONENTS：查看所有可调整组件的当前状态和上一次操作信息。LAST_OPER_TIME 可与此视图的 START_TIME 关联。
V$SGA_DYNAMIC_FREE_MEMORY：调整操作的来源或目的地。GROW 操作消耗其内存，SHRINK 操作向其添加内存。
V$SGAINFO：提供 SGA 的总体信息。
DBA_HIST_SGA_RESIZE_OPS：AWR 快照中捕获的 V$SGA_RESIZE_OPS 历史数据。用于回溯更久远时间的调整历史，突破了内存中循环缓冲区的限制。

总结：V$SGA_RESIZE_OPS 是 Oracle DBA 进行历史性能分析和管理自动内存环境的“飞行数据记录器”。它将ASMM/AMM的动态行为转化为可查询、可分析的结构化数据，使得DBA能够从被动响应变为主动优化。通过分析这些历史调整记录，DBA可以更深入地理解数据库的工作负载特性，验证自动管理的有效性，并做出更明智的容量规划决策，最终确保数据库系统持续稳定高效地运行。

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