CURSOR_SHARING - Reduce Hard Parsing by Sharing SQL Cursors

CURSOR_SHARING

Overview

CURSOR_SHARING controls whether Oracle rewrites SQL statements that differ only in literal values to use system-generated bind variables, enabling those statements to share a single cursor in the shared pool. When set to EXACT (the default), Oracle only shares cursors for statements with identical SQL text. When set to FORCE, Oracle replaces literals (numbers, strings, dates) with bind variables before parsing, allowing many similar statements to share one cursor. This can dramatically reduce hard parsing, shared pool fragmentation, and latch contention — but introduces trade-offs around bind variable peeking and data skew.

Parameter Type: Dynamic (ALTER SESSION and ALTER SYSTEM) Default Value: EXACT Valid Values: EXACT, FORCE Deprecated Values: SIMILAR (removed in Oracle 12c) Available Since: Oracle 8.1.6 Modifiable: Yes — ALTER SESSION and ALTER SYSTEM PDB Modifiable: Yes

Configuration

Viewing Current Value

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

-- SPFILE value
SELECT name, value
FROM   v$spparameter
WHERE  name = 'cursor_sharing';

-- Check related parameters together
SELECT name, value
FROM   v$parameter
WHERE  name IN ('cursor_sharing', 'open_cursors', 'session_cached_cursors', 'shared_pool_size')
ORDER  BY name;

Setting the Parameter

-- Enable cursor sharing (system-wide)
ALTER SYSTEM SET cursor_sharing = FORCE SCOPE = BOTH;

-- Revert to default (exact match only)
ALTER SYSTEM SET cursor_sharing = EXACT SCOPE = BOTH;

-- Set for current session only (testing/tuning)
ALTER SESSION SET cursor_sharing = FORCE;
ALTER SESSION SET cursor_sharing = EXACT;

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

Tuning Guidance

When to Use FORCE

CURSOR_SHARING = FORCE is most beneficial when:

The application cannot be modified — legacy or third-party applications that embed literal values in SQL cannot be refactored to use bind variables. FORCE provides the benefit of bind variable sharing without code changes.
High-volume OLTP with uniform data distribution — applications that execute thousands of similar SELECT ... WHERE id = 12345 statements per second create thousands of hard-parse events and consume shared pool memory. When the data distribution is uniform (similar row counts for each ID value), the plan generated for any given bind variable value is appropriate for all others, so plan quality does not suffer.
Shared pool fragmentation is a documented problem — if the shared pool is filling with thousands of single-use SQL statements (identifiable via V$SQL), FORCE can reclaim that memory.

When to Avoid FORCE

CURSOR_SHARING = FORCE is dangerous when:

Data is skewed — if a status column has 99% of rows with value 'CLOSED' and 1% with 'OPEN', the optimal plan for status = 'OPEN' (index scan) differs from the optimal plan for status = 'CLOSED' (full table scan). With FORCE, both statements share one cursor with one plan — determined by which value was used when the cursor was first parsed (bind variable peeking). The wrong plan will be used for one of the values.
The application already uses bind variables — FORCE adds overhead for SQL rewriting with no benefit.
Complex queries with multiple joins — cursor sharing can produce less optimal plans for complex analytical queries where literal values carry significant cardinality information.
Reporting or data warehouse workloads — these workloads typically issue unique queries anyway; FORCE adds overhead without benefit.

Recommended Values

Environment	Recommended Setting	Rationale
Well-written OLTP (uses bind variables)	`EXACT` (default)	No benefit from FORCE; avoid the overhead
Legacy OLTP with literal-heavy SQL	`FORCE`	Significant parse reduction if data is relatively uniform
Data warehouse / reporting	`EXACT`	Ad-hoc queries are unique; FORCE adds no benefit
Mixed (OLTP + reporting)	`EXACT` with session-level FORCE for OLTP sessions	Surgical application of cursor sharing
Applications with skewed data and literals	`EXACT` + bind variable refactoring	Fix the root cause; FORCE will cause plan quality issues

Diagnosing Whether FORCE Would Help

-- Find SQL statements with high version counts (many similar statements in the shared pool)
-- High version counts with differing literals are the primary FORCE candidate
SELECT sql_id,
       version_count,
       executions,
       parse_calls,
       ROUND(elapsed_time / 1e6, 2) AS elapsed_sec,
       SUBSTR(sql_text, 1, 100)     AS sql_text
FROM   v$sqlarea
WHERE  version_count > 5
ORDER  BY version_count DESC
FETCH FIRST 20 ROWS ONLY;

-- Find single-execution SQL statements (strong signal of literal-heavy applications)
-- These are hard-parse candidates that FORCE would eliminate
SELECT COUNT(*)                   AS single_exec_cursors,
       SUM(sharable_mem) / 1024   AS total_kb_wasted
FROM   v$sql
WHERE  executions = 1
  AND  parse_calls = 1;

-- Quantify the shared pool impact of literal SQL
SELECT ROUND(SUM(sharable_mem) / 1024 / 1024, 2) AS single_use_sql_mb
FROM   v$sql
WHERE  executions = 1;

-- Hard parse rate (instance-wide since startup)
SELECT name, value
FROM   v$sysstat
WHERE  name IN (
    'parse count (total)',
    'parse count (hard)',
    'parse count (failures)'
)
ORDER  BY name;

-- Hard parse as a percentage of total parses
SELECT ROUND(
           (SELECT value FROM v$sysstat WHERE name = 'parse count (hard)') /
           NULLIF((SELECT value FROM v$sysstat WHERE name = 'parse count (total)'), 0)
           * 100, 2
       ) AS hard_parse_pct;

If hard_parse_pct exceeds 5–10% and single-execution SQL accounts for significant shared pool memory, CURSOR_SHARING = FORCE is worth evaluating.

Monitoring CURSOR_SHARING Effectiveness

-- After enabling FORCE: check V$SQL_SHARED_CURSOR for reasons cursors cannot be shared
-- This view explains why two similar statements ended up with separate cursors
SELECT sql_id,
       reason
FROM   v$sql_shared_cursor
WHERE  ROWNUM <= 50;

-- Detailed shared cursor analysis — decode the bitmap column
SELECT c.sql_id,
       s.sql_text,
       c.unbound_cursor,
       c.optimizer_mismatch,
       c.optimizer_mode_mismatch,
       c.row_level_sec_mismatch,
       c.bind_mismatch,
       c.describe_mismatch
FROM   v$sql_shared_cursor c
JOIN   v$sql s ON s.sql_id = c.sql_id
WHERE  ROWNUM <= 20
ORDER  BY c.sql_id;

-- Compare hard parse rates before and after enabling FORCE
-- Snapshot parse counts at two points in time to measure the rate
SELECT name,
       value,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS snap_time
FROM   v$sysstat
WHERE  name IN ('parse count (total)', 'parse count (hard)');

-- Monitor shared pool free memory to confirm reduction in fragmentation
SELECT name,
       ROUND(bytes / 1024 / 1024, 2) AS mb
FROM   v$sgastat
WHERE  pool = 'shared pool'
  AND  name IN ('free memory', 'library cache', 'sql area')
ORDER  BY name;

-- After enabling FORCE: verify version counts dropped for previously problematic SQL
SELECT sql_id,
       version_count,
       executions,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM   v$sqlarea
WHERE  sql_id IN ('your_sql_id_1', 'your_sql_id_2')  -- from pre-FORCE audit
ORDER  BY version_count DESC;

Common Issues

Issue 1: Suboptimal Plans After Enabling CURSOR_SHARING = FORCE

Symptom: Certain queries perform worse after enabling FORCE. A query that previously ran with an index range scan is now doing a full table scan, or vice versa.

Cause: Bind variable peeking. When Oracle first parses a statement under FORCE, it peeks at the current bind variable value and generates a plan optimised for that value. Subsequent executions with different values (particularly if data is skewed) reuse the same plan even if it is suboptimal for the new value.

Diagnosis and Resolution:

-- Check how many child cursors exist for the SQL (high count = plan instability)
SELECT sql_id,
       child_number,
       plan_hash_value,
       executions,
       rows_processed,
       optimizer_mode
FROM   v$sql
WHERE  sql_id = ':your_problem_sql_id'
ORDER  BY child_number;

-- View the actual execution plan for each child cursor
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id        => ':your_problem_sql_id',
    child_number  => 0,
    format        => 'ALL'
));

-- Option 1: Revert to EXACT for this session type
ALTER SESSION SET cursor_sharing = EXACT;

-- Option 2: Refactor the application to use genuine bind variables
-- and remove FORCE from the system configuration

-- Option 3: Use adaptive cursor sharing (ACS) — Oracle 11g+
-- ACS automatically creates multiple child cursors for different bind value ranges
-- Enabled by default when cursor_sharing = FORCE and optimizer_adaptive_cursor_sharing is TRUE
SELECT name, value
FROM   v$parameter
WHERE  name IN ('_optimizer_adaptive_cursor_sharing', 'optimizer_adaptive_plans');

Issue 2: SIMILAR Mode Found in Legacy SPFILE

Symptom: After upgrading to 12c or later, the SPFILE contains cursor_sharing = SIMILAR, which is no longer a valid value.

Cause: SIMILAR was deprecated in Oracle 11g and removed in 12c. It was intended as a middle ground between EXACT and FORCE, but its behaviour was complex and it was rarely beneficial.

Resolution:

-- Check if SIMILAR is set
SELECT name, value FROM v$parameter WHERE name = 'cursor_sharing';
SELECT name, value FROM v$spparameter WHERE name = 'cursor_sharing';

-- Remove or replace the value
-- Oracle 12c+ will treat SIMILAR as FORCE at startup (with a warning in the alert log)
-- Explicitly set to EXACT or FORCE to remove ambiguity
ALTER SYSTEM SET cursor_sharing = EXACT SCOPE = BOTH;
-- or
ALTER SYSTEM SET cursor_sharing = FORCE SCOPE = BOTH;

Issue 3: Application Errors After Enabling FORCE

Symptom: After setting cursor_sharing = FORCE, some application queries return ORA-00932: inconsistent datatypes or unexpected results.

Cause: Oracle’s literal-to-bind-variable substitution under FORCE occasionally misidentifies the datatype of a literal, particularly with date literals, NVARCHAR comparisons, or strings that resemble numbers. The generated bind variable type does not match the column type.