Oracle 库缓存（Library Cache）中的执行计划管理与子游标激增是什么？

在这里插入图片描述
库缓存管理是Oracle共享池（Shared Pool）中最复杂精妙的部分之一，子游标激增是其中最常见的性能杀手。我们将深入探讨其内部机制。

第一部分：官方严谨的详细阐述

一、核心概念：为什么需要库缓存（Library Cache）？

Oracle采用共享服务器架构，核心目标是资源共享，避免重复劳动。当成千上万的用户执行相同的SQL语句时（例如 SELECT * FROM users WHERE user_id = :id），理想情况下，数据库应该只对其进行一次解析（硬解析），生成一个执行计划，然后让所有会话共享这个计划。库缓存就是在SGA的共享池中专门用于存储这些可执行对象的内存区域，包括：

SQL和PL/SQL的文本
解析树（Parse Tree）
执行计划（Execution Plan）
存储过程的源代码和执行代码

二、游标（Cursor）结构：父游标与子游标

为了高效管理，Oracle将SQL游标分为两级结构：

父游标（Parent Cursor）
- 标识：由SQL语句的文本经过哈希运算后得到的值唯一标识。你可以理解为SQL的“书名”。
- 存储位置：库缓存中的一个句柄（Handle），主要存储在 V$SQLAREA 视图中。
- 作用：主要用于快速查找。当一条新SQL传入时，Oracle会计算其哈希值，然后在库缓存中查找是否存在同名的“书”（父游标）。
子游标（Child Cursor）
- 标识：父游标下的一个具体实例。你可以理解为书的特定版本。同一本书可能有精装版、平装版、翻译版等。
- 存储内容：这才是真正宝贵的内容，包括：
  - 执行计划（Execution Plan）
  - 执行环境（Execution Environment）：优化器模式、NLS设置（如字符集、排序规则）、绑定变量类型和长度等。
  - 统计信息（Statistics）：编译该执行计划时所依据的对象的统计信息。
- 作用：存储具体的、可在特定环境下直接执行的方案。主要存储在 V$SQL 或 V$SQLSTATS 视图中。

一个父游标（SQL文本）可以对应多个子游标（不同的执行计划/环境）。Oracle的目标是让一个父游标尽量只对应一个最优的子游标，以实现最大化共享。

三、硬解析、软解析与软软解析

硬解析（Hard Parse）： SQL语句第一次执行，或其在库缓存中的游标已被老化出去（aged out）。这是一个极其昂贵的过程，需要：
1. 语法、语义检查
2. 权限检查
3. 优化器生成多个执行计划并选择成本最低的一个
4. 生成执行计划（子游标）
  最终，将父游标和子游标都存入库缓存。
软解析（Soft Parse）： SQL语句已在库缓存中找到了父游标。接下来需要在该父游标下，寻找一个与之完全匹配的子游标。匹配的条件极其严苛，要求执行环境完全一致。
软软解析（Soft Soft Parse） / 会话游标缓存：发生在会话层面。如果同一个会话重复执行相同的SQL，其私有PGA中的会话游标缓存（Session Cursor Cache）可能会直接命中，从而避免再到共享的库缓存中去竞争闩锁（Latch），性能最高。

四、子游标激增（Child Cursor Flooding）的成因

当Oracle无法为一条SQL找到匹配的子游标时，即使其父游标已存在，它也会被迫进行一次硬解析，创建一个新的子游标。导致这种情况发生的原因，即执行环境不匹配，主要包括：

绑定变量长度不匹配（最常见）：
- 原理：如果应用使用绑定变量，但两次调用传入的变量长度差异巨大（例如，第一次传入 :name='ABC'（3字节），第二次传入 :name='ABCDEFGHIJKLMNOP'（17字节）），Oracle可能会认为需要一个新的执行计划，从而生成一个新的子游标。
- 内部机制：优化器在选择执行计划时，VARCHAR2 绑定变量的长度可能会影响索引选择（例如，对长值可能觉得全表扫描更划算）。为了安全起见，Oracle会选择创建新的子游标。
NLS（国家语言支持）设置差异：
- 原理：会话级别的NLS设置，如 NLS_SORT（排序规则）、NLS_COMP（比较规则）、NLS_DATE_FORMAT 等，会直接影响SQL的语义和执行结果。WHERE name COLLATE BINARY_CI = ... 和 WHERE name = ... 被认为是不同的环境。
- 场景：一个应用连接池中的不同会话可能拥有不同的NLS设置。
优化器模式不同：
- 原理：会话设置了 OPTIMIZER_MODE=FIRST_ROWS，而另一个会话使用 OPTIMIZER_MODE=ALL_ROWS，会导致为同一SQL生成不同的执行计划。
对象统计信息变更：
- 原理：如果在一个子游标被编译后，底层表的统计信息被更新，Oracle的游标失效（Cursor Invalidation） 机制会标记该子游标为失效。下次执行时，即使环境相同，也需要进行硬解析来生成基于新统计信息的子游标。
BUG：某些Oracle版本的BUG也可能导致非必要的子游标创建。

后果：一条简单的SQL可能拥有成百上千个子游标。每个子游标都会消耗共享池的内存，加剧库缓存闩锁（Library Cache Latch）的争用，导致硬解析率飙升，CPU使用率暴涨，最终可能耗尽共享池，引发 ORA-04031: unable to allocate shared memory 错误。

五、排查、诊断与解决方案

排查与诊断

发现激增：查询 V$SQLAREA 和 V$SQL，找到子游标数量异常多的SQL。

-- 查找子游标数量最多的SQL
SELECT sql_id, sql_text, version_count 
FROM v$sqlarea 
WHERE version_count > 100 -- 阀值根据情况调整
ORDER BY version_count DESC;

-- 查看某个特定SQL的所有子游标概要信息
SELECT sql_id, child_number, plan_hash_value, executions, loads, parse_calls, address, hash_value
FROM v$sql 
WHERE sql_id = '&sql_id'
ORDER BY child_number;

根因分析：使用神奇的 V$SQL_SHARED_CURSOR 视图。这个视图为每个子游标都有一行，并通过一系列 'Y'/'N' 的列来解释为什么它不能与另一个子游标共享。

-- 诊断为什么子游标不能共享
SELECT child_number, 
       TRANSLATION_MISMATCH, 
       OPTIMIZER_MODE_MISMATCH,
       ROW_LEVEL_SECURITY_MISMATCH,
       BIND_MISMATCH, -- 绑定变量不匹配
       USE_FEEDBACK_STATS, -- 是否因统计信息反馈导致
       ... -- 查看所有列，寻找值为'Y'的列
FROM v$sql_shared_cursor 
WHERE sql_id = '&sql_id';

BIND_MISMATCH=Y：极有可能是绑定变量长度或类型问题。
OPTIMIZER_MODE_MISMATCH=Y：优化器模式不同。
TRANSLATION_MISMATCH=Y：可能涉及视图或同义词的解析不同。

解决方案

应用层修复（根本解决）：
- 标准化绑定变量：在应用层，确保传递相同类型的绑定变量，并对于字符串类型，尽量使用相同的长度。例如，在Java中使用 PreparedStatement.setString() 时，可以统一将变量用空格填充到固定长度。
- 标准化NLS设置：确保所有应用会话使用相同的NLS环境变量。可以在数据库级设置 (ALTER SYSTEM SET NLS_XXX=...) 或会话级连接字符串中强制指定。
数据库层调优（缓解方案）：
- 调整 CURSOR_SHARING 参数：
  - EXACT （默认）：精确匹配，要求苛刻。
  - FORCE ： 强制共享。Oracle会用系统生成的绑定变量（如 :SYS_B_0）替换所有字面量，但这是一个钝器，可能导致次优计划被共享，需谨慎测试。
  - SIMILAR （已过时）：已不推荐使用。
- 使用 DBMS_SHARED_POOL.KEEP：对于重要的SQL，将其游标“钉”在共享池中，防止其被老化出去，从而避免后续的硬解析。
- 增加共享池大小：如果确实需要大量不同的子游标（如业务确实需要多套NLS环境），这是一个治标不治本的方法，但可以防止 ORA-04031 错误。
SQL管理：
- 使用SQL执行计划管理（SPM）：通过SQL基线（Baseline）来固定和认可一个最优的执行计划，避免因统计信息变化等原因导致计划突变。

第二部分：通俗易懂的解释

让我们用一个比喻来理解这个复杂的过程。

把Oracle的库缓存想象成一个巨大的公司图书馆。

SQL文本：一本书的书名。
父游标：图书馆目录系统里的书名卡片。你通过书名来查找书。
执行计划：书的具体内容。
子游标：书的特定版本和译本。例如，《哈利波特》这本书，有英文原版、中文简体版、精装版、平装版。它们都是同一本书（同一个书名），但内容/形式有区别。

借书流程（解析过程）：

你（会话）来到图书馆，说要借《哈利波特》（SQL文本）。
图书管理员（Oracle）先去查目录卡片（父游标）。如果连卡片都没有，说明这是本新书，需要硬解析：采购（语法检查）、翻译和排版（优化生成执行计划）、上架（存入库缓存）。
如果目录卡片存在，管理员会问：“你要哪个版本？”（寻找匹配的子游标）。
如果你要的是中文简体平装版，并且书就在书架上（子游标存在且匹配），管理员直接拿给你。这就是软解析，很快。
如果你要的是德文插图版，而这个版本图书馆从来没有过（没有匹配的子游标），管理员就必须再进行一次硬解析：联系出版社翻译、印刷、上架（创建一个新的子游标）。

子游标激增（图书馆的灾难）：
现在，想象一下，每个来借《哈利波特》的人，都要求一个略有不同的版本：