session_cached_cursors参数的作用

Inkcup

session_cached_cursors 控制会话级游标缓存的大小。它决定每个数据库会话可以在其 PGA 中缓存的已解析 SQL 语句（游标）的数量。

工作原理--

游标解析的三个阶段

    1.硬解析（Hard Parse）

        检查语法

        语义检查

        生成执行计划

        最消耗资源

    2.软解析（Soft Parse）

        在共享池中找到已解析的游标

        较少资源消耗

    3.软软解析（Soft Soft Parse）

        在会话游标缓存中找到游标

        几乎不消耗资源

缓存机制--
text-

应用请求 SQL
    ↓
检查会话游标缓存    ←─ session_cached_cursors 控制
    ↓ 命中 → 直接执行（软软解析）
    ↓ 未命中
检查共享池
    ↓ 命中 → 放入会话缓存 → 执行（软解析）
    ↓ 未命中
硬解析 → 放入共享池 → 放入会话缓存 → 执行

参数配置--

查看当前设置
sql-

-- 查看参数值
SELECT name, value, description
FROM v$parameter
WHERE name = 'session_cached_cursors';

-- 或使用 show 命令
SHOW PARAMETER session_cached_cursors;

-- 查看所有会话的游标缓存使用情况
SELECT sid, value
FROM v$sesstat s, v$statname n
WHERE n.statistic# = s.statistic#
AND n.name = 'session cursor cache count'
ORDER BY value DESC;

推荐设置
sql-

-- 通常建议的设置
ALTER SYSTEM SET session_cached_cursors = 200 SCOPE=BOTH;

-- 对于OLTP高并发系统
ALTER SYSTEM SET session_cached_cursors = 300 SCOPE=BOTH;

-- 对于数据仓库系统（通常较低）
ALTER SYSTEM SET session_cached_cursors = 50 SCOPE=BOTH;

监控和诊断
1. 检查游标缓存命中率
sql

-- 系统级游标缓存命中率
SELECT 'session cursor cache hits' metric,
       ROUND(a.value/b.value*100, 2) value
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'session cursor cache hits'
AND b.name = 'parse count (total)';

-- 会话级游标缓存命中率
SELECT s.sid, s.username,
       a.value "Cache Hits",
       b.value "Total Parses",
       ROUND(a.value/b.value*100, 2) "Cache Hit Ratio"
FROM v$sesstat a, v$sesstat b, v$statname c, v$statname d, v$session s
WHERE a.statistic# = c.statistic#
AND b.statistic# = d.statistic#
AND s.sid = a.sid
AND s.sid = b.sid
AND c.name = 'session cursor cache hits'
AND d.name = 'parse count (total)'
AND b.value > 0
ORDER BY 5 DESC;

2. 检查是否需要调整
sql

-- 检查游标缓存溢出
SELECT 'session cursor cache count' metric,
       SUM(value) value
FROM v$sesstat s, v$statname n
WHERE n.statistic# = s.statistic#
AND n.name = 'session cursor cache count';

-- 检查游标缓存使用情况
SELECT sid,
       s.value "Cached Cursors",
       (SELECT value FROM v$parameter WHERE name = 'session_cached_cursors') "Max Cache"
FROM v$sesstat s, v$statname n
WHERE n.statistic# = s.statistic#
AND n.name = 'session cursor cache count'
AND s.value > 0
ORDER BY s.value DESC;

3. 查找高解析的SQL
sql

-- 查找硬解析最多的SQL
SELECT sql_text, parse_calls, executions,
       parse_calls/executions parse_ratio,
       loads, invalidations
FROM v$sql
WHERE parse_calls > 1000
AND executions > 0
ORDER BY parse_calls DESC;

-- 查找可能受益于游标缓存的SQL
SELECT sql_id, sql_text,
       parse_calls, executions,
       loads, invalidations
FROM v$sql
WHERE parse_calls/executions > 0.1  -- 每次执行都解析
AND executions > 100
ORDER BY parse_calls DESC;

优化建议--

何时需要增加 session_cached_cursors？
sql

-- 检查指标，如果以下条件成立，可能需要增加
SELECT
  (SELECT value FROM v$sysstat WHERE name = 'parse count (hard)') hard_parses,
  (SELECT value FROM v$sysstat WHERE name = 'parse count (total)') total_parses,
  (SELECT value FROM v$sysstat WHERE name = 'session cursor cache hits') cache_hits,
  ROUND(
    (SELECT value FROM v$sysstat WHERE name = 'session cursor cache hits') /
    (SELECT value FROM v$sysstat WHERE name = 'parse count (total)') * 100, 2
  ) cache_hit_ratio
FROM dual;

-- 如果以下情况出现，考虑增加：
-- 1. cache_hit_ratio < 90%
-- 2. 硬解析率 > 10%
-- 3. 共享池存在大量游标老化


设置调整步骤
sql-

-- 1. 先监控当前值
SELECT sid, username,
       ss.value "Cached Cursors",
       sp.value "Max Cache"
FROM v$sesstat ss, v$statname sn, v$session s, v$parameter sp
WHERE ss.statistic# = sn.statistic#
AND ss.sid = s.sid
AND sp.name = 'session_cached_cursors'
AND sn.name = 'session cursor cache count'
AND ss.value > 0
ORDER BY ss.value DESC;

-- 2. 逐步增加（每次增加20-50）
ALTER SYSTEM SET session_cached_cursors = 150 SCOPE=BOTH;

-- 3. 监控效果
-- 等待一段时间后再次检查命中率

与其他参数的关系
open_cursors vs session_cached_cursors
sql-

-- open_cursors: 最大同时打开的游标数（防止泄漏）
-- session_cached_cursors: 缓存已关闭但可能重用的游标数

SHOW PARAMETER open_cursors;
SHOW PARAMETER session_cached_cursors;

-- 建议关系：open_cursors 应该是 session_cached_cursors 的 3-5 倍

shared_pool_size 影响
sql-

-- 共享池大小会影响硬解析频率
SHOW PARAMETER shared_pool_size;

-- 如果共享池太小，即使有游标缓存也无效
-- 因为父游标可能已被挤出共享池

最佳实践--

1. 应用层优化
sql

-- 使用绑定变量（最重要！）
-- 坏的写法
SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = 101;

-- 好的写法（使用绑定变量）
SELECT * FROM employees WHERE employee_id = :emp_id;

2. 中间件配置
sql

-- 对于连接池（如WebLogic, Tomcat等）
-- 确保连接池配置了语句缓存
-- WebLogic: Statement Cache Size
-- Tomcat: preparedStatementCacheSize

3. 监控脚本
sql

-- 创建定期监控脚本
CREATE OR REPLACE PROCEDURE monitor_cursor_cache IS
  v_cache_hits NUMBER;
  v_total_parses NUMBER;
  v_hit_ratio NUMBER;
BEGIN
  SELECT SUM(DECODE(name, 'session cursor cache hits', value, 0)),
         SUM(DECODE(name, 'parse count (total)', value, 0))
  INTO v_cache_hits, v_total_parses
  FROM v$sysstat
  WHERE name IN ('session cursor cache hits', 'parse count (total)');
  
  IF v_total_parses > 0 THEN
    v_hit_ratio := ROUND(v_cache_hits / v_total_parses * 100, 2);
   
    IF v_hit_ratio < 90 THEN
      DBMS_OUTPUT.PUT_LINE('警告：游标缓存命中率偏低：' || v_hit_ratio || '%');
      -- 可以发送警报邮件
    END IF;
   
    INSERT INTO cursor_cache_stats
    VALUES (SYSDATE, v_hit_ratio, v_cache_hits, v_total_parses);
  END IF;
END;
/

-- 定期执行
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_CURSOR_CACHE',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN monitor_cursor_cache; END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY',
    enabled => TRUE
  );
END;
/

常见问题解决--
问题1：ORA-01000: maximum open cursors exceeded
sql

-- 增加 open_cursors
ALTER SYSTEM SET open_cursors = 500 SCOPE=BOTH;

-- 检查应用是否有游标泄漏
SELECT s.sid, s.username, s.program,
       s.value "Open Cursors",
       (SELECT value FROM v$parameter WHERE name = 'open_cursors') "Max Cursors"
FROM v$sesstat s, v$statname n
WHERE n.statistic# = s.statistic#
AND n.name = 'opened cursors current'
AND s.value > 100
ORDER BY s.value DESC;

问题2：高硬解析率
sql

-- 增加 session_cached_cursors
ALTER SYSTEM SET session_cached_cursors = 300 SCOPE=BOTH;

-- 增加 shared_pool_size
ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

-- 使用绑定变量

问题3：PGA内存压力
sql

-- 如果PGA内存紧张，可能需要降低 session_cached_cursors
-- 检查PGA使用
SELECT * FROM v$pgastat;

-- 检查每个会话的PGA使用
SELECT sid, username,
       pga_used_mem/1024/1024 pga_used_mb,
       pga_alloc_mem/1024/1024 pga_alloc_mb,
       pga_max_mem/1024/1024 pga_max_mb
FROM v$process p, v$session s
WHERE p.addr = s.paddr
ORDER BY pga_used_mem DESC;

---------------------总结---------------------

session_cached_cursors 的关键点：

    作用：减少软解析，实现"软软解析"

    默认值：通常为50，但经常需要调整

    监控：关注命中率，目标 > 90%

    优化：配合绑定变量使用效果最佳

    平衡：与 open_cursors 和 shared_pool_size 协同调整

推荐设置范围：

    OLTP系统：200-500

    数据仓库：50-100

    混合型：100-200