🎉 2.5 Oracle数据库共享池状态检查

在 Oracle 11g 中，共享池（Shared Pool） 是 SGA 的重要组成部分，主要缓存 SQL 执行计划、PL/SQL 代码、数据字典信息等。共享池状态直接影响到 SQL 的解析效率、系统并发能力以及内存使用。下面是一套完整的共享池状态检查方法。

1. 查看共享池当前大小

-- 查看 SGA 各组件大小
SHOW SGA

-- 或查询 v$sgainfo（更详细）
SELECT * FROM v$sgainfo WHERE name LIKE '%Shared%';

-- 查看参数设置（当前生效值）
SELECT name, value, isdefault 
FROM v$parameter 
WHERE name = 'shared_pool_size';

在 11g 中，若启用了 ASMM（自动共享内存管理），shared_pool_size​ 会被视为最小值，实际大小会动态调整。可以通过 v$shared_pool_advice 了解在不同大小下的性能预测。

2. 共享池内存使用分布

查看共享池中各个组件占用的内存（按大小排序）：

SELECT * FROM (
  SELECT name, ROUND(bytes/1024/1024, 2) AS mb
  FROM v$sgastat
  WHERE pool = 'shared pool'
  ORDER BY bytes DESC
) WHERE ROWNUM <= 20;

重点关注：

• ​free memory​：空闲内存，如果持续很小说明共享池紧张。
• ​sql area​：SQL 游标占用，过大可能表示 SQL 未重用或存在大量子游标。
• ​library cache​：包含 SQL 和 PL/SQL 对象。
• ​row cache​：数据字典缓存。

3. 库缓存命中率

库缓存命中率反映 SQL 和 PL/SQL 执行计划的重用程度，命中率低说明硬解析多。

SELECT
  ROUND(SUM(pins - reloads) / SUM(pins) * 100, 2) AS "Library Cache Hit Ratio"
FROM v$librarycache;

• > 95% ：正常
• < 90% ：存在大量硬解析，需检查绑定变量使用情况

4. 字典缓存命中率

SELECT
  ROUND(SUM(gets - getmisses) / SUM(gets) * 100, 2) AS "Dictionary Cache Hit Ratio"
FROM v$rowcache;

• > 95% ：正常
• < 85% ：可能共享池不足或动态 SQL 过多

5. 硬解析比例

SELECT
  ROUND(100 * (hard_parses.value / total_parses.value), 2) AS "Hard Parse Ratio"
FROM
  (SELECT VALUE FROM v$sysstat WHERE name = 'parse count (hard)') hard_parses,
  (SELECT VALUE FROM v$sysstat WHERE name = 'parse count (total)') total_parses;

• < 5% ：良好
• > 10% ：应检查应用是否使用绑定变量

6. 共享池碎片情况

6.1 查看空闲内存块大小分布

SELECT * FROM (
  SELECT
    chunk_size,
    count(*) chunks,
    sum(chunk_size)/1024/1024 AS total_mb
  FROM (
    SELECT bytes AS chunk_size
    FROM v$sgastat
    WHERE name = 'free memory' AND pool = 'shared pool'
  )
  GROUP BY chunk_size
  ORDER BY total_mb DESC
) WHERE ROWNUM <= 10;

6.2 查看保留池使用情况

共享池中有一块保留区域（reserved pool）用于存放大对象（如大 PL/SQL 包）。查看其使用情况：

SELECT
  request_misses,
  request_failures,
  free_space,
  free_unpinned_space
FROM v$shared_pool_reserved;

• 如果 request_misses 持续增长，说明保留池可能太小。
• request_failures > 0​ 表示大对象无法分配内存，需增大 shared_pool_reserved_size。

7. SQL 游标重用情况

7.1 查看 SQL 版本数过多的游标

SELECT sql_id, COUNT(*) AS versions
FROM v$sql
GROUP BY sql_id
HAVING COUNT(*) > 10
ORDER BY versions DESC;

如果大量 SQL_ID 有多个子游标，可能是由于环境变量不一致、统计信息不同步或未使用绑定变量导致。

7.2 查看占用共享池最多的 SQL

SELECT * FROM (
  SELECT
    sql_id,
    SUBSTR(sql_text,1,50) sql_text,
    sharable_mem/1024/1024 AS sharable_mb,
    executions,
    loads
  FROM v$sqlarea
  WHERE sharable_mem > 1024*1024  -- 大于1MB
  ORDER BY sharable_mem DESC
) WHERE ROWNUM <= 10;

8. 共享池自动调优建议（11g ASMM）

如果启用了自动共享内存管理（SGA_TARGET > 0），Oracle 会自动调整共享池大小。可以通过以下视图查看建议：

sql

SELECT
  shared_pool_size_for_estimate AS size_mb,
  shared_pool_size_factor,
  estd_lc_time_saved_factor AS estd_lc_saved_factor
FROM v$shared_pool_advice;

• shared_pool_size_factor=1 表示当前大小。
• 若 estd_lc_time_saved_factor > 1，说明增加共享池可能减少解析时间。

9. 综合检查脚本

sql

SET LINES 200 PAGES 100
COL metric FORMAT A40
COL value FORMAT A20

PROMPT === Shared Pool Overall Status ===
SELECT 'Shared Pool Size (MB)' AS metric, ROUND(bytes/1024/1024, 0) AS value
FROM v$sgainfo WHERE name = 'Shared Pool Size'
UNION ALL
SELECT 'Free Memory (MB)' AS metric, ROUND(SUM(bytes)/1024/1024, 2) AS value
FROM v$sgastat WHERE pool='shared pool' AND name='free memory'
UNION ALL
SELECT 'Library Cache Hit Ratio (%)' AS metric, 
       ROUND(SUM(pins - reloads)/SUM(pins)*100, 2) AS value
FROM v$librarycache
UNION ALL
SELECT 'Dictionary Cache Hit Ratio (%)' AS metric,
       ROUND(SUM(gets - getmisses)/SUM(gets)*100, 2) AS value
FROM v$rowcache
UNION ALL
SELECT 'Hard Parse Ratio (%)' AS metric,
       ROUND(100 * (SELECT VALUE FROM v$sysstat WHERE name='parse count (hard)') /
                    (SELECT VALUE FROM v$sysstat WHERE name='parse count (total)'), 2) AS value
FROM dual;

PROMPT === Top 10 Memory Consumers in Shared Pool ===
SELECT * FROM (
  SELECT name, ROUND(bytes/1024/1024, 2) AS mb
  FROM v$sgastat WHERE pool = 'shared pool'
  ORDER BY bytes DESC
) WHERE ROWNUM <= 10;

PROMPT === SQL with High Versions (suboptimal reuse) ===
SELECT * FROM (
  SELECT sql_id, COUNT(*) versions
  FROM v$sql
  GROUP BY sql_id
  ORDER BY versions DESC
) WHERE ROWNUM <= 5;

10. 常见问题与调优建议

11. 在 11g 中的特殊注意事项

• ASMM​：如果设置了 SGA_TARGET​，共享池大小由 Oracle 自动管理，手动调整 shared_pool_size 会变成最小值。
• Shared Pool Subpools​：11g 中共享池分为多个子池，通过 _kghdsidx_count 等隐含参数控制，一般无需干预。
• 内存分配失败​：如果出现 ORA-04031 错误，说明共享池内存不足或碎片严重，需及时检查并调整。

通过上述检查，你可以全面掌握 11g 共享池的运行状态。如果发现具体问题（如 ORA-04031 或命中率异常），可以进一步分析对应的等待事件或 SQL 详情。