Cursor_Sharing Parameter in Oracle

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)