Cursor_Sharing Parameter in Oracle

CURSOR_SHARING determines what kind of SQL
statements can share the same cursors. It is an init.ora parameter which
decides whether a SQL send from user is a candidate for fresh parsing
or will use an existing plan.
It has three values
FORCE, SIMILAR, EXACT (Default)
EXACT

Setting
CURSOR_SHARING to EXACT allows SQL statements to share the SQL area
only when their texts match exactly. This is the default behavior. Using
this setting, similar statements cannot shared; only textually exact
statements can be shared.

SIMILAR and FORCE

When
CURSOR_SHARING is used set to SIMILAR or FORCE , Oracle first checks
the shared pool to see if there is an identical statement in the shared
pool. If an identical statement is not found, then Oracle searches for a
similar statement in the shared pool. If the similar statement is
there, then the parse checks continue to verify the executable form of
the cursor can be used. If the statement is not there, then a hard parse
is necessary to generate the executable form of the statement.

Using
CURSOR_SHARING = SIMILAR (or FORCE) can significantly improve cursor
sharing on some applications that have many similar statements,
resulting in reduced memory usage, faster parses, and reduced latch
contention.
(Source: Oracle Wiki)
The difference between SIMILAR and FORCE is
that SIMILAR forces similar statements to share the SQL area without
deteriorating execution plans. Setting CURSOR_SHARING to FORCE forces
similar statements to share the executable SQL area, potentially
deteriorating execution plans. Hence, FORCE should be used as a last
resort, when the risk of suboptimal plans is outweighed by the
improvements in cursor sharing.
Note:  The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per Oracle Metalink Note 1169017.1
Soruce (Oracle Metalink Note)
  • April 11, 2018 | 15 views
  • Comments