Session Cursor Cache, Open Cursors, Parse Storms, and Shared Pool Contention

Learning outcomes

ServiceHub raises ORA-01000 in one connection pool, while another pool shows high parse CPU even though the shared SQL exists. An operator proposes increasing the shared pool, OPEN_CURSORS, and SESSION_CACHED_CURSORS together. Those settings govern different layers. This lesson separates open handles, cached closed session cursors, driver caches, and instance shared-pool code so each symptom gets the right repair.

1. OPEN_CURSORS limits open private/session cursor handles

OPEN_CURSORS sets the maximum number of open cursor handles a session can have simultaneously. Current 26ai documents a default of 50, but production deployments often change it. A high setting does not preallocate the maximum for every session; the correct value depends on the application's legitimate concurrent open statements.

SELECT name, value, isdefault, issys_modifiable, ispdb_modifiableFROM v$parameterWHERE name='open_cursors';

ORA-01000 means a session attempted to exceed this limit. It can be a real sizing issue, but Oracle's current error guidance explicitly identifies cursor leaks as a primary root cause. Raising the limit can postpone a leak without fixing it.

2. SESSION_CACHED_CURSORS caches closed session cursors for reuse

SESSION_CACHED_CURSORS controls how many closed session cursors Oracle can keep cached for that session. Current 26ai documents a default of 50 and allows ALTER SESSION or deferred system changes. The cache is independent of OPEN_CURSORS because cached session cursors are not held open.

Oracle's performance guide notes that after repeated parse requests for a statement, a closed cursor can become eligible for the session cursor cache. Reopening it can avoid some reparsing work. A cache hit is still reported as a parse call, but it is a much softer reuse path than rebuilding executable code.

SELECT n.name, m.valueFROM v$mystat mJOIN v$statname n ON n.statistic#=m.statistic#WHERE n.name IN (  'session cursor cache count',  'session cursor cache hits',  'parse count (total)',  'parse count (hard)',  'opened cursors current',  'opened cursors cumulative')ORDER BY n.name;SELECT name, valueFROM v$parameterWHERE name IN ('open_cursors','session_cached_cursors')ORDER BY name;

3. Driver statement caches are another layer

JDBC, OCI, python-oracledb, ODP.NET, and other drivers/pools can cache prepared statements or cursor handles according to driver-specific rules. A driver statement cache can avoid repeated client/database prepare/open work; Oracle's session cursor cache can reuse closed session cursors; the library cache shares executable SQL across sessions. These are complementary layers, not duplicate names for one cache.

Always record the actual driver/version/cache configuration before changing database parameters.

4. Diagnose ORA-01000 from a separate observer connection

SELECT    s.username,    s.sid,    s.serial#,    ss.value AS opened_cursors_current,    p.value  AS open_cursors_limitFROM v$sesstat ssJOIN v$statname sn  ON sn.statistic#=ss.statistic#JOIN v$session s  ON s.sid=ss.sidCROSS JOIN (    SELECT value FROM v$parameter WHERE name='open_cursors') pWHERE sn.name='opened cursors current'  AND s.username IS NOT NULLORDER BY ss.value DESC;

SELECT    sid,    user_name,    sql_id,    cursor_type,    COUNT(*) AS open_cursor_entries,    substr(sql_text,1,120) AS sql_textFROM v$open_cursorWHERE sid=:suspect_sidGROUP BY sid,user_name,sql_id,cursor_type,substr(sql_text,1,120)ORDER BY open_cursor_entries DESC, sql_id;

V$OPEN_CURSOR is diagnostic evidence, not a direct leak detector by itself. Frameworks can legitimately keep prepared statements open. Correlate the count over time with request/connection lifecycle and application telemetry.

5. Deliberately wrong: solve ORA-01000 by multiplying the limit immediately

If a pool leaks ten cursors per request, increasing OPEN_CURSORS from 300 to 3000 only lets the leak run longer and consume more memory. The safe repair is to find the owning code path and close/reuse statements correctly. Increase the limit only if before/after observation proves the application legitimately needs more simultaneous opens.

6. Parse storms are about rate and concurrency, not just hard-parse percentage

A parse storm is sustained excessive parse activity relative to useful executions, often from literal SQL, prepare/close loops, session-setting mismatches, or invalidations. Hard parse is especially expensive, but extremely high soft-parse rates can also burn CPU and contend on library-cache/mutex structures.

SELECT name, valueFROM v$sysstatWHERE name IN (  'parse count (total)',  'parse count (hard)',  'parse time cpu',  'parse time elapsed',  'execute count')ORDER BY name;

A cumulative counter without a time interval is not a rate. Capture two timestamps around a representative window and compute deltas. Pair the database evidence with request throughput and application statement-cache metrics.

7. Do not flush the shared pool as a tuning technique

ALTER SYSTEM FLUSH SHARED_POOL can invalidate the very evidence you need and forces unrelated SQL to reload/hard parse. It can create an artificial CPU spike and make a transient plan/cursor problem look “fixed” until the workload repopulates the cache. Use it only for narrowly justified administrative/test purposes, not as incident treatment.

8. Hands-on lab: session cursor cache behavior without changing system settings

Run this in a disposable SQLcl/SQL*Plus session. Record the original session setting first and restore it afterward.

SELECT value AS original_session_cached_cursorsFROM v$parameterWHERE name='session_cached_cursors';ALTER SESSION SET session_cached_cursors=20;

DECLARE  n NUMBER;BEGIN  FOR pass IN 1..8 LOOP    FOR i IN 1..10 LOOP      EXECUTE IMMEDIATE        'SELECT /* sh10_l4_cache */ COUNT(*) FROM dual WHERE :x=:x'        INTO n        USING i, i;    END LOOP;  END LOOP;END;/

SELECT n.name, m.valueFROM v$mystat mJOIN v$statname n ON n.statistic#=m.statistic#WHERE n.name IN (  'session cursor cache count',  'session cursor cache hits',  'parse count (total)',  'parse count (hard)',  'opened cursors current')ORDER BY n.name;

Restore the original value from your recorded evidence. Do not assume 20 or 50 is appropriate for production; sizing depends on the repeated working set and parse/cache-hit evidence.

9. Production judgment

When ORA-01000 appears, determine whether the application legitimately holds many concurrent statements or leaks them. When parse CPU is high, determine whether SQL text changes, close/reparse behavior, invalidation, optimizer-environment drift, or child proliferation is responsible. Tune driver statement caches and session cursor caching with measured hit/reuse behavior; size OPEN_CURSORS for legitimate concurrency after code is correct.

No restart, management pack, or COMPATIBLE change is needed for the mandatory lab. Lesson 5 combines the chapter into an incident workflow: deliberately create a child-cursor mismatch, explain it with V$SQL_SHARED_CURSOR, normalize session settings, and prove stabilization with parse/child evidence.

Authoritative references

• OPEN_CURSORS — per-session open cursor limit
• SESSION_CACHED_CURSORS — session cursor cache parameter and default
• Tuning the Shared Pool and the Large Pool — session cursor cache sizing and shared-pool guidance
• ORA-01000 — current 26ai causes and diagnostic queries
• V$OPEN_CURSOR — open/parsed cursor evidence