Diagnose Cursor Proliferation and Stabilize High-Throughput OLTP SQL

Learning outcomes

ServiceHub's busiest API SQL shows VERSION_COUNT=18. Latency has become erratic, so an incident runbook says “flush shared pool until version count resets.” Instead, this lesson creates one controlled child-cursor mismatch, proves the reason through V$SQL_SHARED_CURSOR, normalizes the session environment, and defines a repeatable high-throughput stabilization workflow. The goal is not zero children; it is justified, bounded, reusable children with low parse pressure.

1. Version count is a symptom, not a root cause

V$SQLAREA.VERSION_COUNT reports the number of child cursors associated with a parent. A high number can be justified—for example by Adaptive Cursor Sharing—or problematic due to session environment drift, bind metadata inconsistencies, authorization differences, repeated invalidations, or other sharing mismatches.

SELECT  sql_id,  version_count,  executions,  parse_calls,  loads,  invalidations,  sharable_mem,  substr(sql_text,1,120) AS sql_textFROM v$sqlareaWHERE executions > 0ORDER BY version_count DESC, parse_calls DESCFETCH FIRST 20 ROWS ONLY;

Do not set a universal “version count > N is bad.” Evaluate memory, parse rate, latency, child reasons, and whether executions concentrate on reusable children.

2. V$SQL_SHARED_CURSOR explains why a child could not be shared

V$SQL_SHARED_CURSOR contains many Y/N mismatch columns. Examples include OPTIMIZER_MISMATCH, OPTIMIZER_MODE_MISMATCH, BIND_MISMATCH, STATS_ROW_MISMATCH, AUTH_CHECK_MISMATCH, and numerous feature-specific reasons. Query only the fields relevant to the incident first, then expand when needed.

SELECT  sql_id,  child_number,  optimizer_mismatch,  optimizer_mode_mismatch,  bind_mismatch,  stats_row_mismatch,  auth_check_mismatch,  literal_mismatch,  roll_invalid_mismatchFROM v$sql_shared_cursorWHERE sql_id=:sql_idORDER BY child_number;

A Y indicates that mismatch contributed to the sharing decision for that child relationship. Interpret it with the matching V$SQL child and session/application change history.

3. Controlled lab: create an optimizer-environment mismatch

Use two independent SQLcl/SQL*Plus sessions connected to the same disposable PDB/schema. Both execute the exact same tagged SQL and same bind type, but with different OPTIMIZER_MODE. This is intentionally a lab-only way to create child incompatibility.

DROP TABLE servicehub_child_case IF EXISTS PURGE;CREATE TABLE servicehub_child_case (  case_id      NUMBER PRIMARY KEY,  status_code  VARCHAR2(12) NOT NULL,  amount       NUMBER(10,2) NOT NULL);INSERT INTO servicehub_child_caseSELECT  LEVEL,  CASE MOD(LEVEL,4)    WHEN 0 THEN 'OPEN'    WHEN 1 THEN 'CLOSED'    WHEN 2 THEN 'HOLD'    ELSE 'ESCALATED'  END,  MOD(LEVEL*41,10000)/10FROM dualCONNECT BY LEVEL <= 10000;CREATE INDEX sh10_l5_status_ixON servicehub_child_case(status_code);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(    USER,'SERVICEHUB_CHILD_CASE',cascade=>TRUE  );END;/

ALTER SESSION SET optimizer_mode=ALL_ROWS;VARIABLE b_status VARCHAR2(12)EXEC :b_status := 'OPEN';SELECT /* sh10_l5_child */ COUNT(*)FROM servicehub_child_caseWHERE status_code=:b_status;

ALTER SESSION SET optimizer_mode=FIRST_ROWS_10;VARIABLE b_status VARCHAR2(12)EXEC :b_status := 'OPEN';SELECT /* sh10_l5_child */ COUNT(*)FROM servicehub_child_caseWHERE status_code=:b_status;

4. Observe the children and mismatch reason

SELECT  sql_id,  child_number,  optimizer_mode,  optimizer_env_hash_value,  plan_hash_value,  executions,  parse_calls,  is_shareableFROM v$sqlWHERE sql_text LIKE 'SELECT /* sh10_l5_child */ COUNT%'ORDER BY child_number;

SELECT  sql_id,  child_number,  optimizer_mismatch,  optimizer_mode_mismatch,  bind_mismatch,  stats_row_mismatchFROM v$sql_shared_cursorWHERE sql_id = :sql_idORDER BY child_number;

You should expect evidence consistent with an optimizer-mode/environment mismatch, although exact child numbering and additional flags can vary. This is preferable to guessing from VERSION_COUNT alone.

5. Stabilize the environment and prove reuse

Set both sessions back to the application's documented optimizer mode—normally do not alter it per request at all—and execute the identical SQL repeatedly with consistent bind metadata.

ALTER SESSION SET optimizer_mode=ALL_ROWS;VARIABLE b_status VARCHAR2(12)EXEC :b_status := 'OPEN';BEGIN  FOR i IN 1..20 LOOP    NULL;  END LOOP;END;/SELECT /* sh10_l5_child */ COUNT(*)FROM servicehub_child_caseWHERE status_code=:b_status;

The old child can remain in the shared pool until it ages out; successful stabilization does not require version count to instantly fall. Instead, verify that new executions accumulate on compatible children and that hard-parse/latency rates improve under the normalized workload.

6. Bind metadata mismatches are another common application source

Connection-pool code can bind the same placeholder as different data types, lengths, or character semantics across calls. That can require separate children or cause implicit-conversion problems. Stabilize the application's bind contract: one semantic parameter should have one intended Oracle type/size/character behavior across code paths.

7. Parse-pressure evidence before and after

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

SELECT  sql_id,  version_count,  executions,  parse_calls,  loads,  invalidationsFROM v$sqlareaWHERE sql_text LIKE 'SELECT /* sh10_l5_child */ COUNT%';SELECT  sql_id,  child_number,  executions,  parse_calls,  plan_hash_value,  optimizer_mode,  is_bind_sensitive,  is_bind_awareFROM v$sqlWHERE sql_text LIKE 'SELECT /* sh10_l5_child */ COUNT%'ORDER BY child_number;

Capture these metrics before and after a representative request window. A lower hard-parse rate plus stable latency/throughput is stronger evidence than a one-time cache snapshot.

8. Deliberately wrong: flush shared pool and declare victory

A flush temporarily removes many shared SQL objects, including good reusable cursors, and triggers repopulation/hard parsing. It may hide version proliferation until the same mismatched workload recreates it. The correct repair is to make SQL text, bind metadata, session optimizer settings, schema/object state, and application statement-cache behavior stable—or document the legitimate reason multiple children must exist.

9. High-throughput OLTP cursor-stability checklist

• SQL text: same semantic statement emitted consistently; intentional tags/comments controlled.
• Binds: data values bound, with stable Oracle types/length semantics across code paths.
• Session environment: optimizer/NLS settings standardized by pool initialization, not varied per request without reason.
• Statement lifecycle: prepared statements reused and closed correctly; no cursor leaks.
• Session cache: SESSION_CACHED_CURSORS evaluated from cache-count/hit/parse evidence.
• Open limit: OPEN_CURSORS sized for legitimate simultaneous handles after leak analysis.
• Children: version counts explained with V$SQL_SHARED_CURSOR; ACS children distinguished from mismatch noise.
• Evidence: executions, total/hard parses, buffer/latency/CPU, request throughput, and pool metrics compared before/after.
• Rollback: any parameter/pool change has a recorded prior value and reversal path.

10. Cleanup and bridge to Chapter 11

ALTER SESSION SET optimizer_mode=ALL_ROWS;DROP TABLE servicehub_child_case PURGE;

This closes the SQL execution-lifecycle chapter. You now have a concrete model from application SQL text to parent/child cursors, binds, parse pressure, ACS, session/open-cursor limits, and supported sharing diagnostics. Chapter 11 moves into PL/SQL blocks, variables, procedures/functions, packages, bulk processing, and exception design—where cursor lifetime and bind/SQL execution concepts continue to matter.

Authoritative references

• V$SQL_SHARED_CURSOR — supported child-cursor nonsharing reasons
• V$SQL — child execution, plan, optimizer and bind-sensitive metadata
• V$SQLAREA — VERSION_COUNT and parent-level parse/execution metrics
• Tuning the Shared Pool and the Large Pool — cursor reuse and nonsharing diagnostics
• Statistics Descriptions — parse/session-cursor statistics