Oracle Database Shared pool 内部原理、机制介绍和常用SQL

---

第一部分：Shared Pool 核心作用与官方定义

一、官方定义与核心作用 (Official Purpose)

Oracle官方文档将Shared Pool定义为系统全局区（SGA）中的一个关键内存区域，用于存储数据库实例运行的各类程序数据。其核心作用是实现代码和数据的共享，最大化内存利用效率，减少重复解析和加载的开销，从而极大提升数据库性能和可扩展性。

您可以将其想象成数据库的 “共享内存图书馆” 或 “代码与元数据的公共会议室”。所有会话（用户进程）都可以来这里查阅、执行相同的“书籍”（SQL代码）和“资料”（元数据），而无需每人自带一本，避免了巨大的浪费。

二、核心组件 (Key Components)

这个“图书馆”主要分为以下几个“阅览区”：

1. 库缓存 (Library Cache)

   • 官方：存储最近执行的SQL语句、PL/SQL程序单元（如存储过程、函数、包）、锁、管道等对象的解析形式（Parse Tree）和执行计划（Execution Plan）。
   • 通俗：SQL和程序的“执行方案仓库”。你告诉图书馆要做什么（SQL语句），图书管理员（Oracle）会为你制定一个最有效的执行方案（执行计划）并存起来。下次有人问同样的事，直接拿出方案就用，不用再重新策划。

2. 数据字典缓存 (Dictionary Cache / Row Cache)

   • 官方：缓存来自数据字典（Data Dictionary）的信息，如表、列的定义、权限、用户信息等。因为它缓存的是数据行（Row），而非数据块（Block），故也称Row Cache。
   • 通俗：数据库的“户口本信息中心”。存储了所有表结构、用户权限等元数据信息。比如你要查SELECT * FROM emp，首先要来这里确认emp表是否存在、你有无权限、它有哪些列。这些信息被频繁访问，放在这里极大提速。

3. 服务器结果缓存 (Server Result Cache) (11g+)

   • 官方：可选组件，用于缓存整个查询的结果集。并非所有Shared Pool都用于此。
   • 通俗：“标准答案集”。对于某些复杂且不常变的查询，直接把最终结果存起来，下次相同查询直接返回结果，连执行都省了。

4. 保留区 (Reserved Pool)

   • 官方：一块为分配大连续内存块（>4400B）预留的区域，防止大内存分配导致小内存碎片。
   • 通俗：“大型项目专用会议室”。防止一个需要大会议室（大内存）的请求，因为小会议室（小内存）碎片太多而找不到地方。

5. 空闲内存 (Free Memory)

   • 尚未被分配使用的内存块。

---

第二部分：详细管理机制 (Management Mechanism)

Shared Pool的管理是一门平衡艺术，核心是基于LRU（最近最少使用）算法的内存块（Chunk）管理。

一、内存分配：Chunk与堆 (Heap)

• 机制：Shared Pool不是一个整体，而是由无数个大小不一的内存块（Chunk）组成的堆（Heap）。当需要一个对象（如一个SQL的解析树）时，Oracle会从Free Memory中寻找一个足够大的空闲Chunk来存放它。用完后，该Chunk被标记为可重用（Reusable），但并非立即释放。

二、分配过程与LRU算法

1. 请求内存：一个服务器进程需要为某个对象（如新解析的SQL）分配内存。
2. 搜索空闲Chunk：Oracle在Free List（空闲链表）上寻找足够大的空闲Chunk。
3. LRU管理：如果找不到完全合适的，LRU算法就开始工作。它会扫描LRU列表，寻找那些** pinned count（钉住计数）为0**（即当前没有被任何会话使用）且可重用的Chunk，将其释放并加入Free List，以腾出空间。
4. 分配与钉住：找到空闲Chunk后，将其分配给请求者，并增加其pinned count（钉住计数）。一个被“钉住”的Chunk不会被LR算法移出。
5. 释放与解钉：当进程不再需要该对象（如SQL执行完毕），pinned count会减少。当它为0时，该Chunk就变为“可重用”，进入LRU列表等待被老化（Aging Out）。

三、硬解析与软解析：一次生动的访问过程

让我们通过一条SELECT * FROM employees WHERE employee_id = 100语句，串联起Shared Pool的工作原理：

1. 哈希与查找 (Hashing & Lookup)：

   • 服务器进程收到SQL文本，对其进行哈希（Hash）运算，得到一个哈希值。
   • 用这个哈希值作为“书名”，去Library Cache这个“仓库”里查找是否已有相同的SQL及其“执行方案”。

2. 场景一：库缓存命中（软解析 - Soft Parse）

   • 官方过程：在Library Cache中找到了完全匹配的SQL语句（包括空格、大小写等都完全一致）及其执行计划。服务器进程直接“钉住”这个Chunk，使用已有的执行计划执行语句，完成后“解钉”。
   • 通俗解释：“图书管理员找到了现成的方案”。直接按方案执行，省时省力。这是最理想的情况。

3. 场景二：库缓存未命中（硬解析 - Hard Parse）

   • 官方过程：如果在Library Cache中没找到，则必须进行昂贵的硬解析。这是一个极其消耗CPU和Shared Pool资源的操作：
     • 语法/语义检查：检查SQL语句语法是否正确。然后去Data Dictionary Cache查询employees表是否存在、employee_id列是否有效、当前用户是否有权限（如果Row Cache未命中，则需从磁盘读数据字典，更慢）。
     • 生成执行计划：优化器（Optimizer）基于统计信息，生成多个可能执行计划并计算成本，选择最优的一个。
     • 分配内存：将最终SQL文本、解析树、执行计划等存入Library Cache。这需要在Shared Pool中分配新的Chunk。
   • 通俗解释：“这是一个新任务，图书管理员需要从头研究方案”。他需要查资料（数据字典）、开会讨论（优化器计算）、写报告（生成执行计划），最后把报告存档（存入Library Cache）。整个过程非常耗时耗力。

---

第三部分：争用、等待事件与排查解决

当多个会话频繁请求Shared Pool内存分配，特别是大量硬解析时，对Library Cache和Shared Pool结构的访问会成为瓶颈，引发争用。

1. 库缓存锁闩争用 (Library Cache Latch / Mutex争用)

• 场景：高并发环境下，大量会话同时执行硬解析或频繁“钉住”同一个SQL对象（如低效的短事务频繁执行同一SQL）。
• 官方原理：Latch和Mutex是轻量级的锁，用于保护Library Cache和Shared Pool内存结构的并发访问。高并发解析导致进程需要排队获取Latch/Mutex来访问或修改Library Cache中的对象。
• 等待事件：latch: library cache 或 library cache: mutex X
• 通俗解释：“太多人同时去找图书管理员要新方案或借阅热门资料，管理员忙不过来，大家只能排队等待”。
• 排查SQL：

  -- 查找高版本SQL（可能因绑定变量窥视导致一个SQL有多个执行计划）
  SELECT SQL_ID, VERSION_COUNT, SQL_TEXT
  FROM V$SQLAREA
  WHERE VERSION_COUNT > 50
  ORDER BY VERSION_COUNT DESC;
  
  -- 查看Mutex等待情况
  SELECT * FROM V$MUTEX_SLEEP_HISTORY WHERE MUTEX_TYPE LIKE 'Library%';
  

• 解决方案：
  • 应用使用绑定变量（Bind Variables）：这是最根本的解决方案。将WHERE employee_id = 100改为WHERE employee_id = :id，避免因值不同而导致Oracle认为是新SQL（硬解析）。
  • 优化应用代码：避免循环中执行SQL，使用批量处理。
  • 调整共享池大小：适度增大SHARED_POOL_SIZE（但过大可能增加管理开销）。
  • 使用游标共享：谨慎设置CURSOR_SHARING = FORCE（有副作用，通常作为临时补救措施）。

2. 共享池内存不足争用 (Shared Pool Free Memory Waits)

• 场景：Shared Pool空间不足，无法满足新的内存分配请求（如一个大硬解析需要一大块连续内存）。
• 官方原理：由于碎片化或尺寸太小，找不到足够的连续空闲空间。服务器进程必须不断扫描LRU列表，释放可重用的Chunk，这个过程会持有相关Latch，导致其他进程等待。
• 等待事件：shared pool free memory
• 通俗解释：“图书馆的架子都堆满了，新书没地方放。管理员不得不先清理一些旧书腾地方，这个过程大家都得等着”。
• 排查SQL：

  -- 查看Shared Pool空闲内存
  SELECT * FROM V$SGASTAT WHERE POOL = 'shared pool' AND NAME = 'free memory';
  
  -- 查看内存分配碎片情况
  SELECT SIZE, COUNT(*) CHUNKS
  FROM V$SHARED_POOL_RESERVED
  GROUP BY SIZE
  ORDER BY SIZE;
  

• 解决方案：
  • 刷新共享池（谨慎！）：ALTER SYSTEM FLUSH SHARED_POOL; 仅在紧急情况下使用，它会清空所有缓存，导致短时间内硬解析暴涨。
  • 增大SHARED_POOL_SIZE。
  • 使用保留区：调整_SHARED_POOL_RESERVED_SIZE参数，为大分配预留空间。
  • 避免不必要的DDL：DDL操作会使依赖的SQL无效，重新执行时又需要解析。

3. 行缓存对象锁闩争用 (Row Cache Objects Latch)

• 场景：频繁访问数据字典信息，如大量登录认证、对象结构查询、权限检查等。
• 官方原理：保护Data Dictionary Cache（Row Cache）的Latch出现争用。
• 等待事件：latch: row cache objects
• 通俗解释：“太多人同时来‘户口本中心’查信息，工作人员忙不过来”。
• 排查：检查是否有频繁的CREATE/DROP语句或无效对象。
• 解决方案：
  • 避免频繁创建删除对象。
  • 使用本地认证，减少对数据字典中用户密码的验证。

---

第四部分：常用监控与诊断SQL

1. 查看解析效率（软/硬解析比率）：

   SELECT NAME, VALUE
   FROM V$SYSSTAT
   WHERE NAME IN ('parse count (total)', 'parse count (hard)');
   
   -- 计算硬解析比率
   SELECT ROUND((hard_parses / total_parses) * 100, 2) "Hard Parse Ratio %"
   FROM (
     SELECT VALUE AS total_parses
     FROM V$SYSSTAT WHERE NAME = 'parse count (total)'
   ), (
     SELECT VALUE AS hard_parses
     FROM V$SYSSTAT WHERE NAME = 'parse count (hard)'
   );
   

   • 硬解析比率应尽可能低（例如<5%）。

2. 查看Library Cache命中率：

   SELECT NAMESPACE, SUM(GETS), SUM(GETMISSES),
          ROUND((1 - (SUM(GETMISSES) / DECODE(SUM(GETS),0,1,SUM(GETS)))) * 100, 2) "Hit Ratio %"
   FROM V$LIBRARYCACHE
   GROUP BY NAMESPACE;
   

   • SQL AREA的命中率通常应高于95%。

3. 查看Shared Pool大小建议：

   SELECT SHARED_POOL_SIZE_FOR_ESTIMATE "SP Size (MB)",
          ESTD_LC_TIME_SAVED "Time Saved (s)"
   FROM V$SHARED_POOL_ADVICE;
   

   • 此视图建议了不同Shared Pool大小下可能节省的解析时间。

总结

Oracle Shared Pool是数据库性能和扩展性的基石。它的管理核心在于最大化共享，最小化解析。

• 官方总结：通过Library Cache共享SQL和PL/SQL代码，通过Data Dictionary Cache共享元数据，基于LRU算法管理内存Chunk的分配与回收。主要性能瓶颈在于硬解析引发的Latch/Mutex争用和内存不足。
• 通俗总结：把它管好，就是要让数据库“好记性、勤分享、少折腾”。让大多数SQL都能直接找到现成的执行方案（好记性），让大家都能复用这些方案（勤分享），并通过绑定变量等手段，避免数据库反复做制定方案的重复劳动（少折腾）。理解并优化Shared Pool，是Oracle DBA走向高性能调优的必经之路。

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