CURSOR_SHARING = EXACT vs FORCE vs SIMILAR

The Complete DBA Guide to Oracle Cursor Sharing, Hard Parsing & Library Cache



Introduction — The Hard Parsing Crisis Most DBAs Don't See Coming

Hard parsing is one of Oracle's most insidious performance problems. Unlike a slow query — which is visible in AWR, in execution plans, in wait events — hard parsing hides in aggregate statistics that look harmless individually but represent catastrophic waste at scale. A Java application making 50,000 database calls per minute, each with a slightly different literal in the WHERE clause, generates 50,000 hard parses per minute. Each hard parse acquires multiple latches, consumes CPU, allocates shared pool memory, and builds an execution plan from scratch. At 50,000 hard parses per minute, the database can spend 40% of its CPU budget on work that produces no business value.

CURSOR_SHARING is Oracle's server-side workaround for this problem — a parameter that controls when and how the database shares SQL cursors between statements that are logically identical but textually different due to literal values. Understanding exactly what EXACT, FORCE, and SIMILAR do — and the subtle but critical differences between them — is essential knowledge for any Oracle DBA.

 

⚡  THE HARD PARSE PROBLEM IN NUMBERS:

   Enterprise OLTP database, Java application WITHOUT bind variables:

   Hard parses per second:     2,847

   CPU wasted on hard parsing: ~38% of total database CPU

   Shared pool allocations:    2,847 new child cursors per second

   Shared pool fragmentation:  Library cache fills in ~4 hours

   This is the environment where CURSOR_SHARING=FORCE is not optional — it is emergency medicine.

Background — How Oracle Parses SQL: The Full Pipeline

To understand CURSOR_SHARING, you must first understand what Oracle does every time it receives a SQL statement. The parsing pipeline has multiple stages, and CURSOR_SHARING intervenes at a very specific point in that pipeline.

 

Parse Stage

Description

Cost

When Occurs

Syntax Check

Is the SQL grammatically correct Oracle SQL?

Trivial — microseconds

Every parse

Semantic Check

Do the referenced objects and columns exist? Does the user have privileges?

Low — data dictionary lookup

Every parse (soft or hard)

Shared Pool Lookup

Is there an existing cursor in the library cache with identical SQL text?

Low — hash lookup

Every parse — this is where CURSOR_SHARING intervenes

Hard Parse (if no match)

Build execution plan from scratch: statistics lookup, optimizer analysis, plan generation, compile

High — milliseconds to seconds. Acquires latch

Only when Shared Pool Lookup finds no match

Soft Parse (if match)

Validate the cached cursor is still usable (objects not changed)

Very low — microseconds

When Shared Pool Lookup succeeds

Execute

Actually run the query using the parsed and compiled plan

Query-dependent

After any parse

 

The key insight: CURSOR_SHARING modifies the SQL text BEFORE the Shared Pool Lookup step. By replacing literal values with bind variable placeholders in the SQL text, CURSOR_SHARING makes textually different statements look identical to the shared pool — enabling cursor reuse and eliminating the hard parse.

 

💡  Library Cache vs Shared Pool:

   Shared Pool = memory area in the SGA containing the Library Cache and Data Dictionary Cache

   Library Cache = the section of Shared Pool that stores parsed SQL and PL/SQL code

   Library Cache Latch = the synchronisation mechanism that protects Library Cache access

   Hard Parse = acquires Library Cache Latch for an extended period (contention under high concurrency)

   Soft Parse = briefly checks Library Cache — much shorter latch hold time

 

  ⚖  CURSOR_SHARING = EXACT  [ THE DEFAULT — PURE CORRECTNESS ] 

 

CURSOR_SHARING = EXACT — The Default Setting

CURSOR_SHARING=EXACT is the Oracle default. With EXACT, Oracle shares a cursor only when the SQL text being submitted is byte-for-byte identical to an existing cursor in the library cache — including identical whitespace, identical case, identical literal values, and identical comment text. If even one character differs, Oracle treats it as a completely new SQL statement and performs a full hard parse.

 

How EXACT Works — Step by Step

With CURSOR_SHARING=EXACT, the parsing pipeline is unchanged. Oracle computes a hash of the exact SQL text and looks it up in the library cache. Only an exact hash match results in a soft parse and cursor reuse.

 

-- ════════════════════════════════════════════════════════════

-- CURSOR_SHARING = EXACT: Behaviour Demonstration

-- AIDBAHUB | www.aidbahub.com

-- ════════════════════════════════════════════════════════════

 

ALTER SESSION SET CURSOR_SHARING = EXACT;

 

-- These three statements are DIFFERENT cursors under EXACT:

-- Statement 1

SELECT employee_id, salary FROM employees WHERE department_id = 10;

 

-- Statement 2 — different literal

SELECT employee_id, salary FROM employees WHERE department_id = 20;

 

-- Statement 3 — different literal again

SELECT employee_id, salary FROM employees WHERE department_id = 30;

 

-- Check library cache: 3 separate cursors created

SELECT sql_text, parse_calls, executions,

       loads, invalidations

FROM   v$sql

WHERE  sql_text LIKE '%employees WHERE department_id%'

  AND  sql_text NOT LIKE '%v$sql%'

ORDER  BY first_load_time;

 

-- Result: 3 rows — one cursor per literal value

-- Each row has loads=1 (hard parsed separately)

 

-- With BIND VARIABLES (the correct solution):

-- This is what EXACT expects and rewards

VARIABLE dept_id NUMBER;

EXEC :dept_id := 10;

SELECT employee_id, salary FROM employees

WHERE department_id = :dept_id;

 

EXEC :dept_id := 20;

SELECT employee_id, salary FROM employees

WHERE department_id = :dept_id;

 

-- Result: 1 cursor, 2 executions — perfect cursor sharing!

 

When EXACT Is the Right Choice

•       Applications that already use bind variables consistently — EXACT provides optimal performance and plan stability.

•       Databases where data distribution is highly skewed — EXACT allows the optimizer to generate column-value-specific plans for different literals.

•       Systems where SQL is generated programmatically and literals represent meaningful data ranges — the optimizer should generate different plans for different values.

•       New application development — forces developers to write correct, bind-variable-aware SQL from the start.

 

When EXACT Becomes a Problem

EXACT becomes catastrophically harmful when used with applications that generate literal-embedded SQL — which is the overwhelming majority of legacy Java, PHP, Ruby, and .NET applications written before bind variable awareness was standard practice. Every variation of the literal becomes a separate cursor, and the library cache fills with thousands of nearly-identical SQL statements that differ only in the literal value.

 

-- Detecting the hard parse problem (run this when DB is slow):

SELECT stat.name, stat.value

FROM   v$sysstat stat

WHERE  stat.name IN ('parse count (hard)',

                     'parse count (total)',

                     'parse count (failures)',

                     'execute count',

                     'session cursor cache hits')

ORDER  BY stat.name;

 

-- KEY RATIO: hard_parses / total_parses

-- < 1%  : healthy

-- 1-10% : moderate concern — investigate top literal SQL

-- > 10% : serious problem — CURSOR_SHARING=FORCE is needed

-- > 30% : crisis — immediate action required

 

-- Find the worst offending literal SQL (under EXACT)

SELECT sql_text,

       parse_calls,

       executions,

       ROUND(parse_calls / NULLIF(executions, 0) * 100, 1) parse_ratio_pct,

       sharable_mem / 1024                                  sharable_kb

FROM   v$sql

WHERE  parse_calls > 100

  AND  executions < parse_calls * 1.5   -- high parse:execute ratio

ORDER  BY parse_calls DESC

FETCH  FIRST 20 ROWS ONLY;

 

✅  The Hard Parse Ratio Formula:

   RATIO = parse count (hard) / parse count (total) × 100

   Healthy OLTP database:    < 1% hard parse ratio

   Alert threshold:          > 5% hard parse ratio

   Crisis threshold:         > 15% hard parse ratio

   If your hard parse ratio is > 5%, investigate immediately.

   The root cause is almost always literal SQL from an application layer.

 

  ⚖  CURSOR_SHARING = FORCE  [ THE EMERGENCY LEVER — MAX SHARING ] 

 

CURSOR_SHARING = FORCE — Maximum Cursor Sharing

CURSOR_SHARING=FORCE is Oracle's most aggressive cursor sharing mode. When set to FORCE, Oracle intercepts every SQL statement before the shared pool lookup and replaces all literal values (numbers, strings, dates) with system-generated bind variable placeholders (:SYS_B_0, :SYS_B_1, etc.). The SQL text that reaches the shared pool is therefore the same regardless of the literal values in the original statement — enabling full cursor sharing across all variations.

 

How FORCE Works — The Literal Substitution Mechanism

The substitution happens transparently, below the application layer. The application sends a SQL with literals. Oracle intercepts it, replaces the literals with bind variable placeholders, computes a hash of the modified SQL text, and performs the library cache lookup. The application never knows this happened — it receives the correct result regardless.

 

-- ════════════════════════════════════════════════════════════

-- CURSOR_SHARING = FORCE: Behaviour Demonstration

-- ════════════════════════════════════════════════════════════

 

ALTER SESSION SET CURSOR_SHARING = FORCE;

 

-- Application sends these three 'different' statements:

SELECT employee_id, salary FROM employees WHERE department_id = 10;

SELECT employee_id, salary FROM employees WHERE department_id = 20;

SELECT employee_id, salary FROM employees WHERE department_id = 30;

 

-- Oracle internally rewrites each to:

-- SELECT employee_id, salary FROM employees

--   WHERE department_id = :SYS_B_0

-- (with :SYS_B_0 = 10, then 20, then 30)

 

-- Check library cache: ONE cursor for all three!

SELECT sql_text,

       parse_calls,

       executions,

       loads,

       version_count

FROM   v$sql

WHERE  sql_text LIKE '%employees%SYS_B%'

  AND  sql_text NOT LIKE '%v$sql%';

 

-- Result: 1 cursor with loads=1 (hard parsed ONCE)

--         parse_calls=3, executions=3, version_count=1

 

-- Monitor the impact on hard parses:

SELECT stat.name, stat.value

FROM   v$sysstat stat

WHERE  stat.name IN ('parse count (hard)', 'parse count (total)');

 

-- Check library cache hit ratio (should improve dramatically):

SELECT ROUND(

  (SUM(pinhits) / NULLIF(SUM(pins), 0)) * 100, 2

) AS library_cache_hit_pct

FROM v$librarycache

WHERE namespace = 'SQL AREA';

 

The Version Count Problem Under FORCE

CURSOR_SHARING=FORCE introduces a subtle but important phenomenon: child cursor versioning. When Oracle creates a shared cursor with FORCE, it creates a PARENT cursor (the literal-substituted SQL text) and potentially multiple CHILD cursors (different versions of the execution plan). Child cursors are created when the same parent SQL needs different plans — for example, when different sessions use different optimizer settings, different NLS parameters, or when the bind variable values happen to require genuinely different plans due to bind variable peeking.

 

-- Check version count on cursors (flag if > 20)

SELECT sql_id,

       version_count,

       parse_calls,

       executions,

       SUBSTR(sql_text, 1, 80) sql_preview

FROM   v$sqlarea

WHERE  version_count > 5

ORDER  BY version_count DESC

FETCH  FIRST 10 ROWS ONLY;

 

-- Investigate why multiple children exist:

SELECT sql_id,

       child_number,

       reason   -- shows WHY a new child was created

FROM   v$sql_shared_cursor

WHERE  sql_id = '&problem_sql_id'

ORDER  BY child_number;

 

-- Common reasons for new children:

-- BIND_MISMATCH:       Bind type/length changed

-- OPTIMIZER_MISMATCH:  Different optimizer settings in sessions

-- NLS_MISMATCH:        Different NLS character settings

-- ROLL_INVALID_MISMATCH: Statistics changed, plan invalidated

 

The Bind Variable Peeking Risk Under FORCE

This is the most important risk with CURSOR_SHARING=FORCE. When the first execution of a literal-substituted query runs, Oracle peeks at the bind variable value and generates an execution plan optimised for that specific value. All subsequent executions reuse that plan — even if they have completely different bind values that would benefit from a different plan.

Example: first execution has department_id=50 (1 employee). Oracle generates an index range scan plan. Second execution has department_id=1 (50,000 employees). Oracle reuses the index range scan plan — which is now suboptimal. A full table scan or parallel query would be better. With EXACT and literals, Oracle could have generated the correct plan for each value. With FORCE and bind peeking, one plan is frozen for all.

 

🔴  CRITICAL: Bind Variable Peeking + FORCE = Potential Plan Instability

   The plan frozen on first execution may be catastrophically wrong for subsequent values.

   Mitigation options:

   1. Adaptive Cursor Sharing (ACS) — Oracle 11g+ detects when bind values cause plan issues

   2. SQL Plan Management (SPM) — lock known-good plans

   3. CURSOR_INVALIDATION=IMMEDIATE — invalidate cursors when statistics change

   4. Ensure STATISTICS_LEVEL = TYPICAL or ALL (enables ACS)

 

  ⚖  CURSOR_SHARING = SIMILAR  [ DEPRECATED — AVOID IN PRODUCTION ] 

 

CURSOR_SHARING = SIMILAR — The Deprecated Middle Ground

CURSOR_SHARING=SIMILAR was Oracle's attempt at a 'smart' version of FORCE — sharing cursors when the optimizer determines the plan would be the same regardless of the literal value, but generating separate plans (not sharing) when the optimizer detects that different literal values would benefit from different plans (e.g., due to histograms showing data skew).

The intent was elegant: get most of FORCE's sharing benefits without the plan quality risk. The reality was far less elegant: SIMILAR introduced complex, unpredictable behaviour, poor performance in edge cases, and bugs that were difficult to diagnose. Oracle deprecated SIMILAR in Oracle Database 12c and strongly recommends against its use.

 

How SIMILAR Works — and Why It's Complicated

With SIMILAR, Oracle replaces literals with bind variable placeholders (like FORCE) but then checks whether the optimizer considers the literal 'safe to share'. Safety is determined by examining whether column histograms exist and whether they show that different values would produce different plans. If the optimizer decides sharing is safe, the cursor is shared. If not, a separate child cursor is created for this literal value combination.

 

-- ════════════════════════════════════════════════════════════

-- CURSOR_SHARING = SIMILAR: Behaviour (DEPRECATED — DO NOT USE)

-- Shown here for educational completeness only

-- ════════════════════════════════════════════════════════════

 

ALTER SESSION SET CURSOR_SHARING = SIMILAR;

 

-- Oracle evaluates whether each literal is 'unsafe to share'

-- based on the presence of histograms on the filtered column.

 

-- Scenario A: No histogram on department_id

-- → Oracle treats as FORCE → creates one shared cursor

SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM employees WHERE department_id = 20;

-- Result: 1 cursor (same as FORCE behaviour)

 

-- Scenario B: Histogram EXISTS on department_id

-- (because data is skewed — most employees in dept 10)

-- → Oracle creates SEPARATE cursors for each literal

SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM employees WHERE department_id = 99;

-- Result: 2 cursors (different plans for different skews)

 

-- The problem: you get SIMILAR's complexity WITH FORCE's risks

-- Unpredictable: you can't easily tell which mode it's using

-- for any given query without running it and checking v$sql

 

-- IMPORTANT: SIMILAR is DEPRECATED in 12c+

-- Oracle documentation states:

-- 'The SIMILAR setting has been deprecated.'

-- 'Oracle recommends using FORCE instead.'

 

Why SIMILAR Was Retired

•       Unpredictable cursor proliferation: Under some workload patterns, SIMILAR generates MORE child cursors than EXACT, defeating its own purpose.

•       Poor interaction with Adaptive Cursor Sharing: ACS (introduced in 11g) provides the 'smart sharing' behaviour that SIMILAR attempted, but does it correctly. SIMILAR and ACS conflict.

•       Complex latch behaviour: The additional logic required to evaluate histogram safety introduces more shared pool latch activity than FORCE.

•       Diagnostic complexity: When SIMILAR is set, diagnosing performance problems requires understanding which mode each cursor used — adding a layer of complexity that most DBAs find unmanageable.

•       Oracle support burden: SIMILAR generated a disproportionate number of Oracle Support bugs and MOS (My Oracle Support) service requests.

 

🔴  VERDICT: DO NOT USE CURSOR_SHARING = SIMILAR

   It is deprecated in Oracle 12c and later versions.

   It provides no benefits over FORCE that Adaptive Cursor Sharing (ACS) does not provide better.

   If you are currently using SIMILAR: migrate to FORCE immediately.

   If you are on 12c or later: ACS handles the plan quality concern automatically.

 

Head-to-Head: EXACT vs FORCE vs SIMILAR — Decision Matrix

 

Criterion

EXACT

FORCE

SIMILAR

Default value?

YES — Oracle default

No — must be set explicitly

No — must be set explicitly

Hard parse reduction

None (requires bind vars in app)

Dramatic (95–99% reduction possible)

High — but unpredictable

Shared Pool memory usage

High — one cursor per literal variation

Low — one cursor for all literal variations

Variable — depends on histograms

Library cache latch contention

High — if app uses literals

Low — fewer hard parses = fewer latch acquisitions

Complex — more latch activity than FORCE

Execution plan quality

Optimal — different plans for different values

Risk — one plan for all values (bind peeking)

Variable — histogram-dependent

Interaction with ACS (11g+)

Full ACS support

Full ACS support — ACS mitigates bind peeking risk

Conflicts with ACS behaviour

Interaction with histograms

Full histogram benefit — literal-specific plans

Bind peeking sees first execution value

Attempts to use histograms — unpredictably

Dynamic SQL support

Works correctly

Works — rewrites dynamic SQL literals too

Works — same rewrite mechanism

Diagnostic ease

Easy — each literal is its own cursor

Moderate — SYS_B_N visible in v$sql

Hard — unpredictable sharing decisions

Oracle recommendation

Preferred when app uses bind variables

Use when app cannot use bind variables

DEPRECATED — do not use

Version availability

All Oracle versions

All Oracle versions

Deprecated from 12c — avoid all versions

SQL_ID consistency

Consistent — same SQL = same ID

Consistent — rewritten SQL = consistent ID

Inconsistent — ID depends on sharing decision

 

Practical Diagnostics — Detecting and Resolving Hard Parse Problems

Step 1: Identify If You Have a Hard Parse Problem

-- ════════════════════════════════════════════════════════════

-- DIAGNOSTIC SCRIPT 1: Hard Parse Health Check

-- ════════════════════════════════════════════════════════════

 

-- Compute hard parse ratio (key metric)

SELECT

  hard.value                                    hard_parses,

  total.value                                   total_parses,

  ROUND(hard.value / NULLIF(total.value, 0)

    * 100, 2)                                   hard_parse_ratio_pct,

  CASE

    WHEN ROUND(hard.value / NULLIF(total.value, 0) * 100, 2) < 1

      THEN 'HEALTHY ✓'

    WHEN ROUND(hard.value / NULLIF(total.value, 0) * 100, 2) < 5

      THEN 'MODERATE — Monitor'

    WHEN ROUND(hard.value / NULLIF(total.value, 0) * 100, 2) < 15

      THEN 'PROBLEM — Investigate'

    ELSE 'CRISIS — Immediate Action Required'

  END AS health_status

FROM

  (SELECT value FROM v$sysstat WHERE name = 'parse count (hard)')  hard,

  (SELECT value FROM v$sysstat WHERE name = 'parse count (total)') total;

 

Step 2: Find the Worst Literal-Using SQL Statements

-- ════════════════════════════════════════════════════════════

-- DIAGNOSTIC SCRIPT 2: Find Worst Literal SQL Offenders

-- ════════════════════════════════════════════════════════════

 

-- Find SQL statements with high parse:execute ratio

-- (indicates literal SQL being re-parsed constantly)

SELECT

  SUBSTR(sql_text, 1, 100)        sql_preview,

  parse_calls,

  executions,

  loads                           hard_parse_count,

  ROUND(parse_calls /

    NULLIF(executions, 0) * 100, 1) parse_pct,

  sharable_mem / 1024              mem_kb,

  last_active_time

FROM   v$sql

WHERE  parse_calls > 1000

  AND  loads       > 100

  AND  UPPER(sql_text) NOT LIKE '%V$SQL%'

ORDER  BY loads DESC

FETCH  FIRST 10 ROWS ONLY;

 

-- Identify duplicate SQL (same logic, different literals)

-- Group by SQL without literals (force_matching_signature)

SELECT

  COUNT(*)                  literal_variants,

  SUM(parse_calls)          total_parses,

  SUM(sharable_mem) / 1024  total_mem_kb,

  SUBSTR(MAX(sql_text), 1, 80) sample_sql

FROM   v$sql

WHERE  parsing_schema_name NOT IN ('SYS','SYSTEM','DBSNMP')

GROUP  BY force_matching_signature

HAVING COUNT(*) > 10          -- 10+ literal variants of same SQL

ORDER  BY total_parses DESC

FETCH  FIRST 10 ROWS ONLY;

 

Step 3: Monitor Library Cache Health

-- ════════════════════════════════════════════════════════════

-- DIAGNOSTIC SCRIPT 3: Library Cache & Shared Pool Health

-- ════════════════════════════════════════════════════════════

 

-- Library Cache hit ratio (target > 99%)

SELECT

  namespace,

  ROUND(pinhits / NULLIF(pins, 0) * 100, 2)  hit_ratio_pct,

  reloads,

  invalidations

FROM   v$librarycache

WHERE  namespace IN ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER')

ORDER  BY namespace;

 

-- Shared Pool free memory (alert if < 10%)

SELECT

  name,

  ROUND(bytes / 1048576, 1)  size_mb,

  ROUND(bytes /

    SUM(bytes) OVER () * 100, 1) pct_of_pool

FROM   v$sgastat

WHERE  pool = 'shared pool'

  AND  name IN ('free memory', 'sql area', 'library cache',

                'dictionary cache', 'miscellaneous')

ORDER  BY bytes DESC;

 

Step 4: Apply and Validate FORCE

-- ════════════════════════════════════════════════════════════

-- APPLYING CURSOR_SHARING = FORCE

-- ════════════════════════════════════════════════════════════

 

-- Check current setting

SHOW PARAMETER CURSOR_SHARING;

 

-- Option A: System-wide (requires restart for SPFILE)

ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE=BOTH;

 

-- Option B: Session-level test first (NO restart needed)

ALTER SESSION SET CURSOR_SHARING = FORCE;

-- Test your most problematic SQL here

-- Check v$sql for SYS_B_N substitution

 

-- Option C: In specific query with hint

SELECT /*+ CURSOR_SHARING_EXACT */ *    -- force EXACT for this query

FROM   employees WHERE department_id = :dept_id;

 

-- Verify the substitution happened:

SELECT sql_text, parse_calls, executions, loads

FROM   v$sql

WHERE  sql_text LIKE '%SYS_B_%'

  AND  sql_text LIKE '%employees%'

ORDER  BY last_active_time DESC;

 

-- After applying FORCE, flush shared pool (optional):

-- This forces existing cursors to be re-evaluated

-- CAUTION: Causes all cursors to be hard-parsed on next use!

-- ALTER SYSTEM FLUSH SHARED_POOL;  -- production: use with care

 

Decision Guide — Which Setting Should You Use?

Scenario

Recommended Setting

Reasoning

New application, developer controls SQL

EXACT

Force developers to use bind variables from the start. Best plans, best performance.

Legacy Java/.NET app with literal SQL, hard parse ratio > 5%

FORCE

Immediate relief. ACS (11g+) mitigates bind peeking risk. Monitor version_count.

Legacy app, immediate production crisis (hard parse >30%)

FORCE at session/system level immediately

Emergency medicine. Apply now, optimise later.

Reporting database, one execution per SQL

EXACT

Reports run once with specific parameters. No benefit from sharing. Avoid bind peeking.

Mixed OLTP + analytics workload

FORCE + ACS enabled

FORCE for OLTP, ACS prevents plan degradation for analytics.

Currently running SIMILAR

FORCE (migrate immediately)

SIMILAR is deprecated. FORCE + ACS provides superior and predictable behaviour.

Exadata / In-Memory database

EXACT (prefer bind vars in app)

Advanced hardware reduces hard parse cost. Plan quality is paramount.

Oracle 12c+ with ACS enabled

FORCE (ACS mitigates bind risk)

ACS in 12c+ makes FORCE significantly safer than in earlier releases.

 

Adaptive Cursor Sharing (ACS) — The Modern Complement to FORCE

Adaptive Cursor Sharing (ACS), introduced in Oracle 11g, is the mechanism that makes CURSOR_SHARING=FORCE safe for production use in modern Oracle databases. ACS monitors the execution statistics of shared cursors and detects when a bind variable value is producing a suboptimal plan. When it detects this, it marks the cursor as 'bind sensitive' and allows Oracle to generate an additional child cursor optimised for that value range — all transparently and automatically.

 

-- Check if Adaptive Cursor Sharing is active

SELECT is_bind_sensitive, is_bind_aware,

       is_shareable, child_number,

       SUBSTR(sql_text, 1, 60) sql_preview

FROM   v$sql

WHERE  sql_text LIKE '%SYS_B_%'

  AND  sql_text NOT LIKE '%v$sql%'

ORDER  BY child_number;

 

-- is_bind_sensitive = Y: Oracle is monitoring this cursor

-- is_bind_aware     = Y: Oracle has detected bind value variation

-- is_shareable      = Y: Cursor can still be reused

 

-- Check ACS execution history

SELECT bind_set_hash_value,

       plan_hash_value,

       executions,

       rows_processed,

       cpu_time,

       elapsed_time

FROM   v$sql_cs_statistics

WHERE  sql_id = '&problem_sql_id';

 

-- Enable ACS prerequisites

ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL SCOPE=BOTH;

-- (ACS requires TYPICAL or ALL statistics level)

 

Best Practices Summary — The CURSOR_SHARING Decision Framework

CURSOR_SHARING is not a performance silver bullet — it is a targeted solution for a specific problem (literal SQL from applications that cannot use bind variables). Applied correctly, it is one of the most impactful single-parameter changes a DBA can make to a suffering system. Applied incorrectly, it trades one set of problems for another.

 

1.      Long-term fix is always application-level bind variables. CURSOR_SHARING is a workaround, not a solution. The only permanent fix for hard parsing is application code that uses bind variables (PreparedStatement in Java, parameterized queries in .NET, bind variables in PL/SQL).

2.      On Oracle 11g+: Enable ACS (STATISTICS_LEVEL=TYPICAL) before setting FORCE. ACS makes FORCE safe.

3.      Monitor version_count. If any cursor exceeds 100 child versions, investigate v$sql_shared_cursor immediately. Excessive versioning indicates FORCE is causing more overhead than it saves.

4.      Test at session level first. Before applying system-wide, test CURSOR_SHARING=FORCE in a session and validate both the hard parse reduction and query plan quality.

5.      Never use SIMILAR in Oracle 12c+. Migrate any SIMILAR setting to FORCE immediately.

6.      Use CURSOR_SHARING_EXACT hint for critical queries. For queries where plan quality is critical and bind peeking is a concern, use /*+ CURSOR_SHARING_EXACT */ to exempt that query from FORCE substitution.

7.      Set at appropriate scope. SCOPE=BOTH applies immediately to running instance and persists after restart. SCOPE=MEMORY applies immediately but reverts after restart (useful for testing).


 Continue Reading