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

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

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

22 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述

好的，我们来对 Oracle 19c 数据库中一个专门用于监控实时内存调整操作的视图 V$SGA_CURRENT_RESIZE_OPS 进行最全面、最深入的解析。

V$SGA_CURRENT_RESIZE_OPS 视图提供了当前正在进行的 SGA 组件大小调整操作的实时信息。当 Oracle 的自动共享内存管理 (ASMM) 或自动内存管理 (AMM) 功能正在增加或缩小某个内存组件（如缓冲区缓存、共享池）时，此视图可以捕捉到这一动态过程。它是诊断数据库自动内存调整行为、监控调整进度和确认调整是否挂起的关键实时工具。

一、字段含义详解

V$SGA_CURRENT_RESIZE_OPS 的字段详细描述了当前每个调整操作的各个方面。

字段名	数据类型	描述	重要说明与解读
COMPONENT	VARCHAR2(64)	正在调整的 SGA 组件的名称。	例如：`'DEFAULT buffer cache'`, `'shared pool'`, `'large pool'`, `'java pool'`。
OPER_TYPE	VARCHAR2(13)	操作的类型。	`GROW`: 组件正在扩大。 `SHRINK`: 组件正在缩小。
OPER_MODE	VARCHAR2(10)	操作的模式。	`IMMEDIATE`: 立即调整。 `DEFERRED`: 延迟调整（在下一个间隔进行）。 `MANUAL`: 由 DBA 手动发起的调整（如 `ALTER SYSTEM SET sga_target=`）。 `AUTO`: 由 ASMM/AMM 自动发起的调整。
PARAMETER	VARCHAR2(64)	与此组件对应的初始化参数名。	例如：对于 `'DEFAULT buffer cache'`，参数是 `'db_cache_size'`；对于 `'shared pool'`，参数是 `'shared_pool_size'`。
INITIAL_SIZE	NUMBER	调整操作开始时的组件大小（字节）。
TARGET_SIZE	NUMBER	调整操作的目标大小（字节）。	操作旨在达到的这个值。
FINAL_SIZE	NUMBER	调整操作最终完成时的大小（字节）。	在操作进行中，此值可能与 `TARGET_SIZE` 相同，也可能因系统约束而不同。
STATUS	VARCHAR2(16)	当前操作的状态。	`IN PROGRESS`: 调整正在进行中。 `PENDING`: 调整操作已计划，但尚未开始。 `COMPLETED`: 操作已成功完成。（注意：已完成的操作通常会很快从该视图中移除，转到 `V$SGA_RESIZE_OPS`）。 `CANCELLED`: 操作已被取消。
START_TIME	DATE	调整操作开始的时间。
END_TIME	DATE	调整操作结束的时间。	对于 `IN PROGRESS` 状态的操作，此值为 `NULL`。
CON_ID	NUMBER	容器ID。	在多租户环境中标识所属容器。对于非CDB，此值为0。

二、核心原理与底层机制

1. 数据来源与底层基表

V$SGA_CURRENT_RESIZE_OPS 是一个动态性能视图，其数据来源于 SGA 中用于跟踪内存管理操作的内存结构。当 MMAN (Memory Manager) 或其它相关后台进程执行调整操作时，会在此结构中注册操作详情。

其底层源是 X$ 表，通常是 X$KSMGRSO (Kernel Service Memory GRanule ReSize Operations) 或类似的底层结构。这些 X$ 表直接映射了内存中记录调整操作的队列或列表。

工作原理：
1. 触发决策：MMAN (Memory Manager) 后台进程根据工作负载的变化（如监测到新的 SQL 需要大量库缓存，或物理读增高），或响应 DBA 的命令，决定调整某个 SGA 组件的大小。
2. 创建操作记录：MMAN 在 SGA 的特定内存区域中创建一条调整操作记录，填充其 OPER_TYPE, TARGET_SIZE, OPER_MODE 等信息，并将 STATUS 设置为 'PENDING' 或 'IN PROGRESS'。
3. 执行调整：MMAN 或其他进程以颗粒 (Granule) 为单位，从 Free SGA Memory 中分配内存给正在增长的组件，或从正在缩小的组件回收内存到空闲池。此过程是渐进的。
4. 实时更新：操作的状态、当前大小等信息会在内存记录中被实时更新。
5. 查询：查询 V$SGA_CURRENT_RESIZE_OPS 即是读取这些内存中的操作记录。
6. 操作完成：当操作达到目标或终止时，记录会被更新为 'COMPLETED' 或 'CANCELLED'，并很快从当前视图移至历史视图 V$SGA_RESIZE_OPS。

2. 自动内存管理 (ASMM/AMM) 的运作

此视图是观察 ASMM (Automatic Shared Memory Management) 和 AMM (Automatic Memory Management) 工作的核心窗口。

OPER_MODE = 'AUTO'：表示此次调整是由 Oracle 内存管理器自动发起的，目的是根据当前负载优化性能。
调整粒度：调整不是一蹴而就的，而是以 V$SGAINFO 中显示的 Granule Size 为单位逐步进行，以避免对系统性能造成剧烈冲击。
目标：ASMM/AMM 的目标是在不同组件之间平衡内存分配，确保高需求的部分（如繁忙时的 Buffer Cache）能获得更多资源，同时将闲置组件（如无人使用的 Java Pool）的内存回收。

3. 与相关视图的关系

V$SGA_RESIZE_OPS：这是 V$SGA_CURRENT_RESIZE_OPS 的历史版本。它记录了近期所有已完成或已取消的 SGA 调整操作。当前视图只显示正在发生或pending的操作，操作完成后会从这里“毕业”到历史视图。
V$SGA_DYNAMIC_COMPONENTS：显示所有可动态调整组件的当前状态（当前大小、最小大小等），但不显示操作过程。它是调整的结果，而 V$SGA_CURRENT_RESIZE_OPS 是调整的过程。
V$SGAINFO：显示 SGA 的静态配置信息和组件大小，包括 Free SGA Memory Available，这是调整操作所依赖的“资源池”。

三、常用查询 SQL 示例

查看当前所有正在进行的 SGA 调整操作（最基本查询）

SELECT component, oper_type, oper_mode, parameter,
       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, start_time
FROM v$sga_current_resize_ops
ORDER BY start_time;

监控由 ASMM 自动发起的调整操作

SELECT component, oper_type, oper_mode,
       ROUND(initial_size/1024/1024,2) AS initial_mb,
       ROUND(target_size/1024/1024,2) AS target_mb,
       status, start_time
FROM v$sga_current_resize_ops
WHERE oper_mode = 'AUTO' -- 只关注自动调整
  AND status = 'IN PROGRESS';

检查调整操作的进度（计算已完成百分比）

SELECT component, oper_type,
       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 current_mb,
       status,
       CASE 
         WHEN oper_type = 'GROW' AND target_size > initial_size 
           THEN ROUND( (final_size - initial_size) / (target_size - initial_size) * 100, 2)
         WHEN oper_type = 'SHRINK' AND initial_size > target_size 
           THEN ROUND( (initial_size - final_size) / (initial_size - target_size) * 100, 2)
         ELSE 100 
       END AS percent_complete
FROM v$sga_current_resize_ops
WHERE status = 'IN PROGRESS';

注意：此计算为估算，因为调整是以颗粒为单位跳跃进行的。

结合 V$SGA_DYNAMIC_COMPONENTS 查看调整的上下文

SELECT comp.component, 
       ROUND(comp.current_size/1024/1024,2) AS current_size_mb,
       ROUND(comp.min_size/1024/1024,2) AS min_size_mb,
       res.oper_type, res.oper_mode, 
       ROUND(res.target_size/1024/1024,2) AS target_size_mb,
       res.status
FROM v$sga_dynamic_components comp
LEFT JOIN v$sga_current_resize_ops res 
  ON comp.component = res.component
WHERE comp.current_size > 0
ORDER BY comp.component;

四、主要应用场景

监控和验证 ASMM/AMM 活动：
这是最核心的用途。DBA 可以通过此视图确认自动内存管理功能是否在正常工作。如果你看到频繁的 OPER_MODE = 'AUTO' 的调整操作，这证明 ASMM 正在积极地根据工作负载变化优化内存分配。
诊断性能波动：
如果数据库性能出现间歇性的短暂下降，查询此视图可能发现原因：一个大型的 SGA 组件调整（如 Buffer Cache 的收缩）可能正在发生，这会消耗 CPU 资源并可能引起短暂的争用。这可以帮助区分是“业务负载导致性能差”还是“系统调整导致性能差”。
确认手动调整的执行：
当 DBA 执行 ALTER SYSTEM SET sga_target=... 或 ALTER SYSTEM SET db_cache_size=... SCOPE=MEMORY 后，可以立即查询此视图来确认调整操作是否已开始、它的目标大小是多少以及当前的状态。
排查调整挂起问题：
如果一个调整操作的 STATUS 长时间停留在 'IN PROGRESS' 或 'PENDING'，这可能表明系统存在资源约束（如没有足够的空闲 SGA 内存用于增长，或有连接正在阻止某个组件的收缩），需要进一步调查。
容量规划和学习：
通过观察历史时期（需要结合 V$SGA_RESIZE_OPS）的调整模式，可以了解不同业务场景下数据库对内存的需求，为未来的容量规划提供数据支持。

五、相关视图

V$SGA_RESIZE_OPS：最重要的相关视图。记录所有已完成的历史调整操作。用于分析过去的调整模式和行为。
V$SGA_DYNAMIC_COMPONENTS：显示 SGA 各组件的当前大小、最小/最大限制等信息。提供了调整操作的起点和终点快照。
V$SGAINFO：提供 SGA 的总体信息，特别是 Free SGA Memory Available，这是调整操作的“资源池”。
V$SGASTAT：提供 SGA 内存使用的详细分类，可以查看调整前后组件内部的使用情况变化。
V$MEMORY_DYNAMIC_COMPONENTS：如果使用 AMM，此视图显示 SGA 和 PGA 组件的动态调整信息。

总结：V$SGA_CURRENT_RESIZE_OPS 是 Oracle DBA 深入理解和管理数据库自动内存功能的“仪表盘”。它提供了前所未有的可见性，让我们能够实时观察 Oracle 内部如何动态地重新分配宝贵的内存资源以优化性能。在由 ASMM/AMM 主导的现代 Oracle 内存管理体系中，熟练掌握这个视图对于诊断性能问题、验证配置更改和确保数据库平稳运行至关重要。

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