SESSION_CACHED_CURSORS - Reduce Parse Overhead with Cursor Caching

SESSION_CACHED_CURSORS

Overview

SESSION_CACHED_CURSORS controls the number of closed cursors Oracle retains in each session’s cursor cache. When a cursor is closed, instead of discarding its parse information, Oracle keeps it in a per-session cache. The next time the same SQL is executed, Oracle performs a soft-soft parse — validating the cursor is still usable from the cache — rather than a full soft parse against the library cache. For applications that repeatedly execute the same SQL statements (particularly OLTP workloads), this can meaningfully reduce parse-related CPU overhead and library cache latch contention.

Parameter Type: Dynamic (ALTER SESSION and ALTER SYSTEM) Default Value: 50 Valid Range: 0 to operating system limit (effectively unlimited, but memory-constrained) Available Since: Oracle 8i Modifiable: Yes — ALTER SYSTEM (affects new sessions) and ALTER SESSION (affects current session immediately) PDB Modifiable: Yes

Configuration

Viewing Current Value

-- Current running value
SELECT name, value, description
FROM   v$parameter
WHERE  name = 'session_cached_cursors';

-- SPFILE value (what will be used after next restart)
SELECT name, value
FROM   v$spparameter
WHERE  name = 'session_cached_cursors';

-- Check both together to spot drift
SELECT p.name,
       p.value        AS memory_value,
       sp.value       AS spfile_value
FROM   v$parameter   p
JOIN   v$spparameter sp ON sp.name = p.name
WHERE  p.name = 'session_cached_cursors';

Setting the Parameter

-- Set for the current session only (tuning/testing)
ALTER SESSION SET session_cached_cursors = 100;

-- Set system-wide in both memory and SPFILE (production change)
ALTER SYSTEM SET session_cached_cursors = 100 SCOPE = BOTH;

-- Reset to default
ALTER SYSTEM SET session_cached_cursors = 50 SCOPE = BOTH;

-- Verify
SELECT name, value FROM v$parameter WHERE name = 'session_cached_cursors';

Tuning Guidance

Recommended Values

Environment	Typical Value	Rationale
Simple scripts / ad-hoc queries	50 (default)	No benefit if SQL rarely repeats
Moderate OLTP	50–100	Small improvement on frequently repeated statements
High-throughput OLTP	100–300	Applications executing the same DML thousands of times per second benefit significantly
Connection pool environments	100–200	Pooled connections reuse the same SQL patterns repeatedly
PL/SQL-heavy applications	200–500	PL/SQL cursors benefit greatly from session-level caching
Batch processing	50–100	Batch SQL is often unique per row, reducing cache effectiveness

Values above 500 are rarely beneficial and increase per-session PGA consumption. If the hit ratio is already above 95% at a lower value, raising the parameter will not provide additional benefit.

How to Size

The primary diagnostic for SESSION_CACHED_CURSORS is the session cursor cache hit ratio from V$SYSSTAT.

-- Session cursor cache statistics (instance-wide, since startup)
SELECT name, value
FROM   v$sysstat
WHERE  name IN (
    'session cursor cache hits',
    'session cursor cache count',
    'parse count (total)',
    'parse count (hard)',
    'parse count (describe)',
    'parse count (failures)'
)
ORDER  BY name;

-- Calculate session cursor cache hit ratio
-- Target: > 90% for OLTP workloads
SELECT ROUND(
           (SELECT value FROM v$sysstat WHERE name = 'session cursor cache hits') /
           NULLIF((SELECT value FROM v$sysstat WHERE name = 'parse count (total)'), 0)
           * 100, 2
       ) AS session_cursor_cache_hit_pct;

-- Full parse efficiency summary
SELECT a.value                                    AS total_parses,
       b.value                                    AS hard_parses,
       c.value                                    AS cache_hits,
       ROUND(c.value / NULLIF(a.value, 0) * 100, 2) AS cache_hit_pct,
       ROUND(b.value / NULLIF(a.value, 0) * 100, 2) AS hard_parse_pct
FROM   (SELECT value FROM v$sysstat WHERE name = 'parse count (total)')    a,
       (SELECT value FROM v$sysstat WHERE name = 'parse count (hard)')     b,
       (SELECT value FROM v$sysstat WHERE name = 'session cursor cache hits') c;

If the cache hit ratio is below 90% and the workload is known to be repetitive, increase SESSION_CACHED_CURSORS. If the ratio is already at or above 95%, further increases will have diminishing returns.

-- Per-session cache utilisation — how many cached cursors each session is using
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       st.value AS session_cached_cursor_count
FROM   v$session  s
JOIN   v$sesstat  st ON st.sid = s.sid
JOIN   v$statname sn ON sn.statistic# = st.statistic#
WHERE  sn.name = 'session cursor cache count'
  AND  s.username IS NOT NULL
ORDER  BY st.value DESC
FETCH FIRST 20 ROWS ONLY;

-- Compare cached cursor count to the parameter limit
-- Sessions at or near the limit indicate the parameter may be too low
SELECT s.sid,
       s.username,
       s.program,
       st.value                                      AS cached_cursors_used,
       (SELECT TO_NUMBER(value)
        FROM   v$parameter
        WHERE  name = 'session_cached_cursors')       AS limit,
       ROUND(st.value /
             (SELECT TO_NUMBER(value)
              FROM   v$parameter
              WHERE  name = 'session_cached_cursors') * 100, 1) AS pct_of_limit
FROM   v$session  s
JOIN   v$sesstat  st ON st.sid = s.sid
JOIN   v$statname sn ON sn.statistic# = st.statistic#
WHERE  sn.name  = 'session cursor cache count'
  AND  s.username IS NOT NULL
  AND  st.value > 0
ORDER  BY pct_of_limit DESC;

If many sessions are consistently at 100% of the SESSION_CACHED_CURSORS limit, the parameter is undersized for the workload. Increase it and re-evaluate the cache hit ratio.

Monitoring

-- Library cache activity — soft parses avoided by cursor sharing mechanisms
SELECT namespace,
       gets,
       gethits,
       ROUND(gethitratio * 100, 2) AS get_hit_pct,
       pins,
       pinhits,
       ROUND(pinhitratio * 100, 2) AS pin_hit_pct,
       reloads,
       invalidations
FROM   v$librarycache
WHERE  namespace = 'SQL AREA'
ORDER  BY namespace;

-- Parse-to-execute ratio (should ideally be low for OLTP)
-- A ratio near 1.0 means every execution requires a parse — bad
-- A ratio near 0 means cursors are being reused well — good
SELECT ROUND(
           (SELECT value FROM v$sysstat WHERE name = 'parse count (total)') /
           NULLIF((SELECT value FROM v$sysstat WHERE name = 'execute count'), 0),
           4
       ) AS parse_to_execute_ratio;

-- Confirm the parameter is effective: compare parse rates before and after change
-- Snapshot approach: capture stats, wait, capture again
SELECT name, value, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS snap_time
FROM   v$sysstat
WHERE  name IN ('parse count (total)', 'parse count (hard)', 'session cursor cache hits');

Common Issues

Issue 1: High Parse Rates Despite Adequate SESSION_CACHED_CURSORS

Symptom: The parse-to-execute ratio remains high even after increasing SESSION_CACHED_CURSORS.

Cause: Cursor caching only helps when the same SQL statement is re-executed within the same session. If the application constructs slightly different SQL text for each execution (e.g., embedding literal values in the statement rather than using bind variables), each statement is treated as unique and cannot be cached or shared.

Resolution:

-- Find top SQL by parse-to-execute ratio (candidates for bind variable refactoring)
SELECT sql_id,
       parse_calls,
       executions,
       ROUND(parse_calls / NULLIF(executions, 0), 2) AS parse_ratio,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM   v$sql
WHERE  executions > 100
  AND  parse_calls / NULLIF(executions, 0) > 0.5
ORDER  BY parse_calls DESC
FETCH FIRST 20 ROWS ONLY;

Statements with a parse ratio near 1.0 and many executions are the primary refactoring candidates. Consider using bind variables or evaluating CURSOR_SHARING.

Issue 2: Increased PGA Memory Usage After Raising the Parameter

Symptom: PGA memory consumption increases noticeably after setting SESSION_CACHED_CURSORS to a high value.

Cause: Each cached cursor retains its parsed representation and context in the session’s PGA. With many sessions each holding hundreds of cached cursors, the aggregate memory cost can be substantial.

Resolution: Monitor PGA usage before and after changes:

-- PGA usage by session
SELECT s.sid,
       s.username,
       s.program,
       p.pga_used_mem   / 1024 / 1024 AS pga_used_mb,
       p.pga_alloc_mem  / 1024 / 1024 AS pga_alloc_mb,
       p.pga_max_mem    / 1024 / 1024 AS pga_max_mb
FROM   v$session s
JOIN   v$process p ON p.addr = s.paddr
WHERE  s.username IS NOT NULL
ORDER  BY p.pga_alloc_mem DESC
FETCH FIRST 20 ROWS ONLY;

If PGA pressure is seen, reduce SESSION_CACHED_CURSORS to the minimum value that still achieves an acceptable cache hit ratio (above 90%).

Issue 3: Parameter Change Has No Immediate Effect

Symptom: After executing ALTER SYSTEM SET session_cached_cursors = 200 SCOPE = BOTH, the cache hit ratio and parse statistics do not improve for existing sessions.

Cause: When set at the system level, SESSION_CACHED_CURSORS takes effect for new sessions only. Existing sessions continue using the value that was in effect when they connected.

Resolution: Either disconnect and reconnect active sessions (feasible with a connection pool by bouncing the pool), or use ALTER SESSION targeted at sessions that can be temporarily bounced. For a full rollout, plan a maintenance window or allow the connection pool to recycle connections naturally over time.

Parameter	Relationship
`OPEN_CURSORS`	Sets the hard limit on how many cursors a session can have open simultaneously. `SESSION_CACHED_CURSORS` controls how many closed cursors are retained for reuse. Both parameters work together to manage cursor memory and performance.
`CURSOR_SHARING`	Controls whether Oracle rewrites SQL to use bind variables. When `CURSOR_SHARING = FORCE`, more statements can share cursors in the library cache, complementing session-level cursor caching.
`SHARED_POOL_SIZE`	The library cache within the shared pool holds parsed cursor definitions. `SESSION_CACHED_CURSORS` is a per-session complement to shared pool cursor sharing — together they minimise parse overhead.

Error	Description
ORA-01000	Maximum Open Cursors Exceeded. High parse rates without caching can cause applications to open many cursors unnecessarily; proper cursor caching helps manage this.
ORA-04031	Unable to Allocate Shared Memory. Excessive hard parsing due to poor cursor reuse exhausts the shared pool.

Version Notes

Oracle Version	Notes
Oracle 8i	Parameter introduced. Default was 0 (no session-level cursor caching).
Oracle 10g	Default raised to 20, then subsequently to 50 in later patchsets.
Oracle 11g	Default is 50. ALTER SESSION support confirmed.
Oracle 12c	PDB-level modification supported. Each PDB can set its own value.
Oracle 19c	No change. Recommended production baseline remains 50–200 depending on workload.
Oracle 21c / 23ai	No change to default or behaviour. Parameter remains one of the most impactful low-risk tuning levers for parse-heavy OLTP workloads.