Oracle DBA

In high-throughput Oracle environments, cursor sharing is a pivotal lever for controlling hard parse overhead, shared pool pressure, and overall scalability. The central control is the initialization parameter CURSOR_SHARING:

• EXACT (best when your code already uses bind variables)
• FORCE (normalize literals to binds internally to reduce cursor explosion)
• SIMILAR (deprecated; avoid)

This article illustrates not just how cursor sharing behaves, but how to monitor it rigorously using Oracle’s dynamic performance views.

1) Environment and Safety First

Run the following in a non-production environment. Where system-level changes are shown, you may substitute session-level changes.

-- Check current cursor sharing:
SHOW PARAMETER cursor_sharing;

-- Optionally constrain changes to your session:
ALTER SESSION SET cursor_sharing = EXACT;
-- or
ALTER SESSION SET cursor_sharing = FORCE;

2) The Core Idea: Literal SQL vs. Bind SQL

Two textually different statements with different literals:

SELECT * FROM employees WHERE employee_id = 101;
SELECT * FROM employees WHERE employee_id = 102;

• With EXACT, Oracle creates separate cursors.
• With FORCE, Oracle normalizes literals to binds internally (e.g., :"SYS_B_0") and reuses one parent cursor (with possible multiple child cursors via Adaptive Cursor Sharing).

3) A Practical Lab: Generate Load, Then Observe

3.1 Generate Literal-Heavy Workload

-- Run these from a test session under CURSOR_SHARING=EXACT first
-- and then repeat under CURSOR_SHARING=FORCE.

DECLARE
  v_emp_id NUMBER;
  v_dummy  employees.last_name%TYPE;
BEGIN
  FOR i IN 1..500 LOOP
    v_emp_id := 100 + MOD(i, 50); -- cycle through 50 different literals
    EXECUTE IMMEDIATE
      'SELECT last_name FROM employees WHERE employee_id = ' || v_emp_id
      INTO v_dummy;
  END LOOP;
END;
/

Repeat the PL/SQL block after switching to FORCE to compare library cache behavior.

4) Monitoring with V$SQL: What Is in the Shared Pool?

4.1 Show All Variants of a Given Text Pattern

SELECT
  sql_id,
  child_number,
  executions,
  parse_calls,
  plan_hash_value,
  substr(sql_text, 1, 80) AS sql_text
FROM v$sql
WHERE sql_text LIKE 'SELECT % FROM employees WHERE employee_id%'
ORDER BY sql_id, child_number;

Interpretation:

• Under EXACT, expect many parent SQLs (different sql_ids).
• Under FORCE, expect a single parent with one or more child cursors (same sql_id, different child_numbers).

4.2 Identify Parent Cursor and Child Cursor Multiplicity

-- Number of child cursors per parent
SELECT
  sql_id,
  COUNT(*) AS child_count,
  SUM(executions) AS total_execs
FROM v$sql
WHERE sql_text LIKE 'SELECT % FROM employees WHERE employee_id%'
GROUP BY sql_id
ORDER BY total_execs DESC;

Interpretation:
A high child_count under FORCE can indicate Adaptive Cursor Sharing (ACS) reacting to bind value skew or environmental differences.

4.3 Why Did Oracle Create Additional Child Cursors?

SELECT
  sql_id,
  child_number,
  reason
FROM v$sql_shared_cursor
WHERE sql_id IN (
  SELECT sql_id
  FROM v$sql
  WHERE sql_text LIKE 'SELECT % FROM employees WHERE employee_id%'
)
ORDER BY sql_id, child_number;

Interpretation:
V$SQL_SHARED_CURSOR.REASON reveals causes such as differing optimizer environment, bind peeking, etc.

5) Bind Intelligence: V$SQL_BIND_CAPTURE

5.1 See Captured Bind Metadata and Values

Bind capture is periodic; you may need multiple executions or specific settings to see values.

-- Find the SQL_ID for the normalized statement first (from V$SQL), then:
SELECT
  sql_id,
  child_number,
  name,
  position,
  datatype_string,
  value_string,
  last_captured
FROM v$sql_bind_capture
WHERE sql_id = :your_sql_id
ORDER BY child_number, position;

Interpretation:
Under FORCE, you should see Oracle’s generated system bind placeholders (e.g., :SYS_B_0) along with the values captured. This helps confirm that literals were normalized.

6) Similarity at Scale: Force-Matching Signatures

When applications flood the shared pool with near-identical SQLs differing only by literals, force-matching signatures are crucial for analysis.

6.1 Group Similar SQLs by FORCE_MATCHING_SIGNATURE

SELECT
  force_matching_signature,
  COUNT(*) AS sql_variants,
  SUM(executions) AS total_execs
FROM v$sql
WHERE force_matching_signature <> 0
GROUP BY force_matching_signature
HAVING COUNT(*) > 1
ORDER BY sql_variants DESC, total_execs DESC;

Interpretation:
Signatures with a high count of variants are prime candidates for bind usage or CURSOR_SHARING=FORCE when code changes are not feasible.

6.2 List the Top Literal-Variant Families

SELECT
  force_matching_signature,
  sql_id,
  child_number,
  executions,
  parse_calls,
  substr(sql_text, 1, 100) AS sample_text
FROM v$sql
WHERE force_matching_signature IN (
  SELECT force_matching_signature
  FROM v$sql
  GROUP BY force_matching_signature
  HAVING COUNT(*) > 10         -- adjust threshold to suit
)
ORDER BY force_matching_signature, executions DESC;

Interpretation:
This surfaces families of SQLs differing only in literals—useful for targeting refactoring or parameter changes.

7) Library Cache Health: V$LIBRARYCACHE

7.1 Assess Misses, Reloads, and Invalidations

SELECT
  namespace,
  pins,
  pinhits,
  reloads,
  invalidations,
  (CASE WHEN pins > 0 THEN ROUND(100 * pinhits / pins, 2) END) AS pinhit_pct
FROM v$librarycache
ORDER BY namespace;

Interpretation:

• Rising reloads or falling pinhit_pct can suggest pressure or churn in shared objects, often exacerbated by literal-heavy SQL under EXACT.

8) Parse Pressure and Ratios: V$SYSSTAT / V$SESSTAT

8.1 System-Level Parse Indicators

SELECT
  name,
  value
FROM v$sysstat
WHERE name IN ('parse count (total)', 'parse count (hard)', 'parse time cpu', 'execute count')
ORDER BY name;

Compute rough ratios:

WITH s AS (
  SELECT
    SUM(CASE WHEN name = 'parse count (hard)'  THEN value END) AS hard_parses,
    SUM(CASE WHEN name = 'parse count (total)' THEN value END) AS total_parses,
    SUM(CASE WHEN name = 'execute count'       THEN value END) AS execs
  FROM v$sysstat
  WHERE name IN ('parse count (total)','parse count (hard)','execute count')
)
SELECT
  hard_parses,
  total_parses,
  execs,
  ROUND(100 * hard_parses / NULLIF(total_parses,0), 2) AS hard_parse_pct,
  ROUND(1.0 * execs / NULLIF(total_parses,0), 2)       AS execs_per_parse
FROM s;

Interpretation:
Lower hard_parse_pct and higher execs_per_parse are generally healthier. Excessive hard parsing often correlates with literal SQL under EXACT.

8.2 Session-Scoped View (Your Test Session Only)

SELECT
  st.name,
  ss.value
FROM v$sesstat ss
JOIN v$statname st ON st.statistic# = ss.statistic#
WHERE ss.sid = SYS_CONTEXT('USERENV','SID')
  AND st.name IN ('parse count (total)', 'parse count (hard)', 'execute count', 'parse time cpu')
ORDER BY st.name;

Interpretation:
Use this around your lab steps to measure your test session’s direct effect.

9) Execution Plan Stability vs. Cursor Sharing

Excessive normalization (FORCE) can cause single-plan reuse for skewed predicates, which may be suboptimal. Oracle mitigates this with Adaptive Cursor Sharing (ACS), producing multiple child cursors when bind values lead to different plan selectivities.

9.1 Detect Multiple Plans for the Same Parent

SELECT
  sql_id,
  COUNT(DISTINCT plan_hash_value) AS distinct_plans,
  COUNT(*) AS child_cursors
FROM v$sql
WHERE sql_id = :your_sql_id
GROUP BY sql_id;

9.2 Inspect Each Child

SELECT
  sql_id,
  child_number,
  plan_hash_value,
  executions,
  buffer_gets,
  disk_reads,
  rows_processed,
  is_bind_sensitive,
  is_bind_aware,
  is_shareable
FROM v$sql
WHERE sql_id = :your_sql_id
ORDER BY child_number;

Interpretation:

• IS_BIND_SENSITIVE/IS_BIND_AWARE='Y' indicates ACS.
• Multiple children with distinct PLAN_HASH_VALUEs reflect plan diversification to handle skew or changing bind selectivity.

10) Holistic “Hotspot” Reports for Blog Readers

10.1 Top Statements by Hard Parses

SELECT
  sql_id,
  child_number,
  parse_calls,
  executions,
  ROUND(parse_calls / NULLIF(executions, 0), 2) AS parses_per_exec,
  substr(sql_text, 1, 100) AS sample_sql
FROM v$sql
WHERE executions >= 10
ORDER BY parse_calls DESC
FETCH FIRST 50 ROWS ONLY;

10.2 Statements with Many Similar Variants (by Force-Matching Signature)

SELECT
  force_matching_signature,
  COUNT(*) AS variants,
  SUM(executions) AS total_execs
FROM v$sql
WHERE force_matching_signature <> 0
GROUP BY force_matching_signature
HAVING COUNT(*) > 20
ORDER BY variants DESC
FETCH FIRST 20 ROWS ONLY;

10.3 Literal-Heavy Families with Sample Texts

SELECT
  force_matching_signature,
  COUNT(*) AS variants,
  MAX(substr(sql_text, 1, 120)) KEEP (DENSE_RANK LAST ORDER BY executions) AS sample_sql
FROM v$sql
WHERE force_matching_signature <> 0
GROUP BY force_matching_signature
HAVING COUNT(*) > 20
ORDER BY variants DESC;

11) Recommended Operating Model

1. Prefer bind variables in application code, keep CURSOR_SHARING=EXACT.
2. If you cannot change the app and see cursor explosion, consider CURSOR_SHARING=FORCE while monitoring:
   • Library cache pinhit_pct, reloads
   • System/session parse metrics
   • Child cursor proliferation and reasons (V$SQL_SHARED_CURSOR)
   • Plan diversity (plan_hash_value) and ACS flags
3. Validate with controlled load tests and AWR/ASH where available.

12) Quick Reference: View-to-Question Map

• How many variants of the same SQL exist?
  → V$SQL with FORCE_MATCHING_SIGNATURE, grouping and counts.
• Are binds being captured and normalized?
  → V$SQL_BIND_CAPTURE (and normalized text in V$SQL).
• Why do I have many child cursors?
  → V$SQL_SHARED_CURSOR.REASON.
• Is my library cache under stress?
  → V$LIBRARYCACHE pins, pinhits, reloads, invalidations.
• Are we over-parsing?
  → V$SYSSTAT / V$SESSTAT for parses vs. executions.
• Is ACS in play, and do I have plan diversity?
  → V$SQL child cursors, IS_BIND_SENSITIVE, IS_BIND_AWARE, PLAN_HASH_VALUE.

---

Cursor sharing is a force multiplier for scalability when used judiciously. Bind-aware coding plus vigilant instrumentation—V$SQL, V$SQL_BIND_CAPTURE, V$SQL_SHARED_CURSOR, V$LIBRARYCACHE, and parse statistics—enables you to strike the right balance between reduced hard parses and stable, selective execution plans.

Appendix: Session-Only Switch for Safer Testing

-- Safer than system-wide changes during demos:
ALTER SESSION SET cursor_sharing = EXACT;  -- or FORCE