Shared Pool 深度解析

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Shared Pool 是 SGA 中缓存 SQL、PL/SQL、数据字典等共享对象的内存区[1]。

核心子组件：

子组件	作用
Library Cache	SQL/PL/SQL 解析结果、执行计划
Data Dictionary Cache	数据字典缓存（rowcache）
Result Cache	查询结果缓存
Reserved Pool	大块内存预留

2. Library Cache

2.1 作用

缓存 SQL/PL/SQL 的解析树、执行计划，避免重复解析[2]。

2.2 缓存对象

对象类型	说明
SQL 游标	SQL 语句的解析结果
PL/SQL 对象	存储过程、函数、包、触发器
Java 类	Java 存储过程
Object Definition	表/索引/视图定义

2.3 Library Cache 命中率

SELECT 
  SUM(gets) AS total_gets,
  SUM(gethits) AS hits,
  ROUND(SUM(gethits)/NULLIF(SUM(gets),0)*100, 2) AS hit_pct
FROM v$librarycache;
-- 健康标准：> 95%

2.4 SQL 共享条件

SQL 语句要共享执行计划，必须满足[3]：

文本完全一致（包括空格、大小写）
引用对象相同（所有者、对象名）
绑定变量类型/长度一致
会话环境一致（NLS、优化器模式等）

-- 这两条 SQL 不共享：
SELECT * FROM emp WHERE id=1;
SELECT * FROM emp WHERE id=2;

-- 这两条 SQL 共享：
SELECT * FROM emp WHERE id=:1;

2.5 查看库缓存中的 SQL

-- 查找 SQL 文本和执行次数
SELECT 
  sql_id,
  parsing_schema_name,
  executions,
  parse_calls,
  buffer_gets,
  sql_text
FROM v$sql
WHERE parsing_schema_name='SCOTT'
ORDER BY executions DESC;

-- 查找未共享的相似 SQL
SELECT sql_text, COUNT(*) 
FROM v$sql 
GROUP BY sql_text 
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC;

3. Data Dictionary Cache（Row Cache）

3.1 作用

缓存数据字典信息（如表定义、用户权限等）。

3.2 查看

SELECT 
  parameter,
  gets,
  getmisses,
  ROUND(getmisses/NULLIF(gets,0)*100, 2) AS miss_pct
FROM v$rowcache
WHERE gets > 0;
-- 健康标准：miss_pct < 15%

4. Result Cache

4.1 作用

缓存查询结果，重复查询直接返回缓存。

4.2 配置

SHOW PARAMETER result_cache_mode;
-- MANUAL: 需显式 Hint
-- FORCE: 自动缓存所有查询

-- 启用 Result Cache
ALTER SYSTEM SET result_cache_mode=MANUAL SCOPE=BOTH;
ALTER SYSTEM SET result_cache_max_size=256M SCOPE=BOTH;

4.3 使用

-- 显式 Hint
SELECT /*+ RESULT_CACHE */ * FROM large_table WHERE id=1;

-- 函数结果缓存
CREATE OR REPLACE FUNCTION get_salary(p_id NUMBER) 
  RETURN NUMBER 
  RESULT_CACHE
IS
  v_sal NUMBER;
BEGIN
  SELECT salary INTO v_sal FROM employees WHERE id=p_id;
  RETURN v_sal;
END;

4.4 查看

SELECT 
  type, 
  status, 
  count(*) AS cnt
FROM v$result_cache_objects
GROUP BY type, status;

5. Reserved Pool

5.1 作用

为大块内存分配（> 4400 字节）预留空间，避免 Shared Pool 碎片化。

5.2 配置

SHOW PARAMETER shared_pool_reserved_size;
-- 默认 Shared Pool 的 5%

ALTER SYSTEM SET shared_pool_reserved_size=100M SCOPE=BOTH;

6. Shared Pool 管理

6.1 大小配置

SHOW PARAMETER shared_pool_size;

-- 估算需求
SELECT 
  SUM(bytes)/1024/1024 AS shared_pool_mb
FROM v$sgastat
WHERE pool='shared pool';

6.2 查看空间使用

-- 子组件占用
SELECT 
  name, 
  bytes/1024/1024 AS mb,
  ROUND(bytes/SUM(bytes) OVER()*100, 2) AS pct
FROM v$sgastat 
WHERE pool='shared pool'
ORDER BY bytes DESC;

6.3 刷新 Shared Pool

-- 谨慎使用：会导致所有 SQL 重新解析
ALTER SYSTEM FLUSH SHARED_POOL;

7. 常见视图

-- Library Cache 详细信息
SELECT 
  namespace,
  gets,
  gethits,
  pins,
  pinhits,
  reloads,
  invalidations
FROM v$librarycache;

-- 共享游标
SELECT sql_id, child_number, sql_text, executions
FROM v$sql
WHERE sql_text LIKE '%employees%';

-- Library Cache 锁
SELECT sid, type, id1, id2, lmode, request 
FROM v$lock 
WHERE type IN ('LA','LP','LC','LR','LS','LT','LU','LV','LW','LX');

-- 等待事件
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event LIKE 'library cache%';

8. 常见坑与排错

8.1 ORA-04031: Shared Pool 空间不足

现象：

ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool",...)

排查：

-- 查看 Shared Pool 详情
SELECT name, bytes/1024/1024 AS mb 
FROM v$sgastat 
WHERE pool='shared pool' 
ORDER BY bytes DESC;

-- 查看 Library Cache 锁
SELECT sid, event, sql_id 
FROM v$session 
WHERE event LIKE 'library cache%';

修复：

-- 1. 增大 Shared Pool
ALTER SYSTEM SET shared_pool_size=2G SCOPE=BOTH;

-- 2. 临时刷新
ALTER SYSTEM FLUSH SHARED_POOL;

-- 3. 根本解决：使用绑定变量

8.2 硬解析过多

现象：Library Cache 命中率低，parse time elapsed 高。

SELECT name, value 
FROM v$sysstat 
WHERE name IN ('parse count (total)', 'parse count (hard)', 'parse time elapsed');
-- parse count (hard) 应 < parse count (total) 的 5%

修复：

使用绑定变量
设置 CURSOR_SHARING=FORCE（临时方案）
优化应用代码

8.3 Library Cache Lock / Pin

现象：DDL 操作卡住，无法重编译对象。

-- 查找阻塞者
SELECT 
  s.sid, s.serial#, s.username, s.program,
  l.type, l.lmode, l.request
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
WHERE l.type IN ('LA','LP','LC','LT','LU','LV','LW','LX');

-- 找到后终止
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

8.4 ORA-04045: errors during recompilation/revalidation

现象：重编译对象报错。

原因：对象上有 Library Cache Lock。

修复：找到并终止持锁会话。

9. 最佳实践

使用绑定变量：减少硬解析
Shared Pool ≥ 1GB：保证缓存充足
避免 SHARED_POOL 刷新：除非紧急
定期监控命中率：Library Cache > 95%
使用 CURSOR_SHARING=EXACT（默认）：避免执行计划偏差
PL/SQL 包封装：减少对象依赖
避免 DDL 高峰期：减少 Library Cache 失效

10. 参考资料

[1] Oracle Database Concepts 19c, “Shared Pool” https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/memory-architecture.html

[2] Oracle Database Administrator’s Guide 19c, “Managing the Shared Pool” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-memory.html

[3] AskTOM, “Library Cache and SQL Sharing” https://asktom.oracle.com/pls/apex/f?p=100:1:0