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

好的，我们来深入解析 Oracle 19C 数据库中一个更为底层和核心的动态性能视图：V$SQL_SHARED_MEMORY。这个视图揭示了 SQL 游标在库缓存（Library Cache）中内存分配的详细信息，是诊断共享池（Shared Pool）内存问题、理解游标内存结构的终极工具。

我将为您提供一份极其详细和准确的指南。

---

1. 视图概述与核心作用

V$SQL_SHARED_MEMORY 视图用于显示共享游标（Shared Cursors）在库缓存中如何分配和管理其内存片段（Heaps）。

每个子游标（Child Cursor）在库缓存中并非一个单一的内存块，而是由多个不同的堆（Heap）组成，每个堆存储特定类型的信息（如执行计划、绑定变量元数据、SQL文本等）。该视图的核心作用在于：

• 内存结构洞察：展示每个子游标的内存组成，详细到每个堆（Heap）的大小、状态和作用。
• 共享池问题诊断：当发生ORA-04031: unable to allocate shared memory错误时，此视图可以帮助定位是哪些SQL游标的哪些内存堆无法获得连续的内存空间，从而判断是内存碎片化还是总体不足。
• 性能优化：理解SQL游标的内存占用模式，评估是否因复杂的SQL或大量的执行计划导致共享池压力过大。

2. 使用场景

• 深度诊断ORA-04031错误：超越V$SQL_SHARED_CURSOR，从内存分配的角度理解为什么游标无法被加载到共享池。
• 分析游标内存占用：调查特定SQL语句或所有SQL在共享池中消耗的内存详情，找出“内存大户”。
• 评估共享池碎片：通过查看内存堆的分配和释放状态，辅助评估共享池的健康状况。
• 高级性能调优：在与Oracle Support合作处理复杂的库缓存争用或内存管理问题时，提供关键数据。

---

3. 字段含义详解

V$SQL_SHARED_MEMORY 的字段描述了游标内存堆的元数据。其结构如下表所示：

字段名称	数据类型	含义详解
SQL_ID	VARCHAR2(13)	父游标的 SQL 标识符。
CHILD_NUMBER	NUMBER	该子游标的编号。与 V$SQL 中的 CHILD_NUMBER 对应。
SHARED_MEMORY_ADDR	RAW(4 | 8)	库缓存对象句柄（Library Cache Handle）的内存地址。这是游标在库缓存中的唯一标识，比 ADDRESS 更底层。
HEAP_DESC	RAW(4 | 8)	特定内存堆描述符的地址。
HEAP_NO	NUMBER	内存堆的编号。这是最关键字段之一，每个编号代表一个特定用途的内存区： • 0: 永久性数据（Permanent Data），如SQL文本、上下文。 • 1: 运行时数据（Runtime Data），如绑定变量信息。 • **2 - 5: 依赖项（Dependencies）、授权信息（Authorizations）等。 • 6: 执行计划（Execution Plan） 通常存储于此。 • 大于 6: 其他特定用途的堆。
SIZE_BYTES	NUMBER	该内存堆当前的大小（单位：字节）。
ALLOCATED_BYTES	NUMBER	在该内存堆中已分配的总字节数。
FREEABLE_BYTES	NUMBER	该内存堆中可被释放的字节数（如果游标被age out）。
LOCKS	NUMBER	在此内存堆上持有的锁的数量。
PINS	NUMBER	在此内存堆上持有的Pin的数量。
REFERENCES	NUMBER	对此内存堆的引用次数。
STATUS	VARCHAR2(12)	内存堆的状态： • PERM: 永久（Permanent）。游标已被固定或长期持有。 • FREE: 空闲（Free）。可被重用或释放。 • RECREATEABLE: 可重建（Recreateable）。如果内存被释放，其内容可以根据需要重新创建。 • FLUSHED: 已刷出（Flushed）。内容已被刷出到磁盘（如因为ASH或AWR）。
HEAP_TYPE	VARCHAR2(10)	堆的类型（例如，CURSOR）。
HEAP_NAME	VARCHAR2(32)	堆的名称（例如，kglhd）。

---

4. 相关视图与基表

• 相关视图：
  • V$SQL / V$SQLAREA: 核心SQL信息视图。通过 (SQL_ID, CHILD_NUMBER) 关联，以获取SQL文本、执行统计等信息。
  • V$SQL_SHARED_CURSOR: 解释为何不能共享。可与本视图结合，全面分析游标（为何不能共享 + 内存如何分配）。
  • V$DB_OBJECT_CACHE: 显示所有缓存在库缓存中的对象（表、视图、游标等）。
  • V$LIBRARYCACHE: 提供库缓存整体性能的聚合信息（获取、失效率等）。
• 基表：
  V$SQL_SHARED_MEMORY 是基于内存中极其底层的 X$KSMHS 表（或类似变体）构建的。X$KSM 表通常与内核内存（Kernel Shared Memory）管理相关。强烈不建议用户直接查询X$表。

---

5. 底层原理与工作机制

1. 库缓存与游标：
   库缓存是共享池（SGA的一部分）中的一个关键组件，用于存储已解析的SQL语句、PL/SQL代码、执行计划等共享结构，以避免重复解析（硬解析）。

2. 游标的内存结构：
   一个子游标在内存中不是一个单体，而是一个由多个堆（Heap）组成的对象。每个堆是一个逻辑上的内存段，用于存储特定类型的数据：

   • Heap 0: 存储最基础、永久的信息，如SQL文本的指针、上下文区域。此堆通常较小但至关重要。
   • Heap 6: 存储执行计划。这通常是最大的一个堆，尤其是对于复杂的查询。执行计划的复杂度直接决定了Heap 6的大小。
   • 其他堆（1,2,3,4,5等）：存储依赖关系（如表、视图）、权限检查信息、绑定变量元数据、优化器环境等。

3. V$SQL_SHARED_MEMORY 的工作机制：
   Oracle 内部的内存管理器（Kernel Memory Manager）负责为这些堆分配和回收内存。此视图通过直接查询内部的内存管理结构，将每个游标的每个堆的分配情况暴露给DBA。

   • 当一条SQL被硬解析时，Oracle会为它分配所有必要的堆。
   • 当游标被age out（老化移出）时，这些堆的内存会被标记为可重用（FREE）或真正释放。
   • 如果共享池中没有足够的连续空间来分配某个堆（尤其是大的Heap 6），就会引发ORA-04031错误，即使总空闲空间可能还很多（这就是碎片化问题）。

---

6. 常用查询 SQL

查询 1：查看特定SQL语句的内存分配详情

这是最常用的查询，用于分析一个游标的内存分布。

SELECT sql_id,
       child_number,
       heap_no,
       size_bytes,
       allocated_bytes,
       freeable_bytes,
       status
FROM v$sql_shared_memory
WHERE sql_id = '&sql_id' -- 替换为你的SQL_ID，例如 'g8uxf6w4vqyup'
ORDER BY heap_no;

查询 2：查找共享池中占用内存最大的SQL游标

用于快速定位“内存大户”，判断其对共享池的压力。

SELECT sql_id,
       child_number,
       SUM(size_bytes) / 1024 total_mem_kb,
       COUNT(*) heap_count
FROM v$sql_shared_memory
GROUP BY sql_id, child_number
HAVING SUM(size_bytes) / 1024 > 100 -- 查找占用超过100KB的游标
ORDER BY total_mem_kb DESC;

查询 3：深入分析某个大游标，查看是哪个堆占用最多

承接查询2，对找出的“大户”进行深入分析。

SELECT sql_id,
       child_number,
       heap_no,
       size_bytes / 1024 size_kb,
       status,
       heap_type
FROM v$sql_shared_memory
WHERE sql_id = '&sql_id' 
AND child_number = &child_number -- 替换为具体的CHILD_NUMBER
ORDER BY size_bytes DESC;

查询 4：检查共享池中可释放的内存

评估如果刷新共享池，大致能释放多少空间。

SELECT SUM(freeable_bytes) / (1024 * 1024) total_freeable_mb
FROM v$sql_shared_memory;

查询 5：结合V$SQL获取完整信息（SQL文本、执行次数等）

提供最全面的诊断视图。

SELECT m.sql_id,
       m.child_number,
       s.sql_text,
       s.executions,
       m.heap_no,
       m.size_bytes,
       m.status
FROM v$sql_shared_memory m
JOIN v$sql s ON (m.sql_id = s.sql_id AND m.child_number = s.child_number)
WHERE m.sql_id = '&sql_id'
ORDER BY m.heap_no;

7. 关键知识点总结

• 内存堆（Heap）： 游标在内存中被分解为多个逻辑堆，每个堆有特定用途。Heap 6（执行计划）通常是最大的。
• ORA-04031错误的根源： 此错误通常不是因为共享池总体空间不足，而是因为碎片化导致无法找到足够的连续空间来分配一个大堆（特别是大的Heap 6）。
• 游标与内存： 复杂的SQL（涉及多表关联、子查询）会产生更大的执行计划（Heap 6），消耗更多共享池内存。不使用绑定变量会导致大量相似的父/子游标，加剧内存消耗和碎片化。
• 状态（STATUS）的含义：
  • PERM： 需要特别注意的对象，它不会被正常的老化机制移出共享池，可能导致长期的内存占用。
  • RECREATEABLE： 即使被移出，下次执行时也可以重新创建，对性能影响相对较小。
• 诊断路径： 遇到共享池问题，可按此路径排查：
  1. V$SQLAREA -> 找到高内存占用的SQL (SQL_ID)。
  2. V$SQL_SHARED_CURSOR -> 检查该SQL是否有大量子游标及原因。
  3. V$SQL_SHARED_MEMORY -> 深入分析该SQL游标的内存分配细节，确认是哪个堆占用了大量内存或导致分配失败。

通过掌握 V$SQL_SHARED_MEMORY，您可以从内存分配的微观层面洞察Oracle SQL执行的底层机制，从而具备解决最棘手的共享池性能问题的能力。

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