CURSOR_SHARING = EXACT vs FORCE vs SIMILAR - The Complete DBA Guide to Oracle Cursor Sharing, Hard Parsing & Library Cache SQL Performance
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).
Post a Comment: