CURSOR_SHARING_EXACT Hint

Introduction

In this article, we explore the Oracle SQL hint CURSOR_SHARING_EXACT as part of our series on Oracle hints. The CURSOR_SHARING_EXACT hint forces Oracle to match SQL statements only when they are exactly identical, including the case, whitespace, and use of literals. This behavior overrides the CURSOR_SHARING initialization parameter when set to FORCE or SIMILAR.  As always, when using hints, test, test, test.

Before we go on to explore an example, lets just familarise ourselves with how cursor sharing works.

How Oracle Handels Cursor Sharing

Oracle's cursor sharing mechanism allows the database to reuse execution plans for SQL statements that are structurally the same. This is done to reduce the overhead of repeated hard parsing and to promote plan cache reuse.

Oracle uses one of the following modes for cursor sharing

• EXACT - Only identical SQL text can share a cursor.

• FORCE - Oracle replaces literals with bind variables to promote sharing.

• SIMILAR - Deprecated SINCE 12.1, but in essence it attempts to strike a balance between EXACT and FORCE.
  
  

When CURSOR_SHARING is set to FORCE, Oracle will internally rewrite SQL statements.  See below

SELECT *

FROM employees 

WHERE department_id = 10

/

will be re-written to 

SELECT * 

FROM employees 

WHERE department_id = :SYS_B_0

/

This allows multiple statements with different literals to share a single cursor. While this improves cursor reuse and reduces parsing overhead, it can also limit the optimizer’s ability to generate the most efficient plan for each literal value.

To mitigate this, Oracle uses a technique called bind peeking. During the initial hard parse of a query using bind variables, the optimizer "peeks" at the value of the bind variable on first execution and chooses a plan based on that specific value. However, if the data distribution is skewed, that plan might not perform well for other values used in subsequent executions. In such cases, cursor sharing and bind peeking together can lead to performance instability. Oracle later introduced adaptive cursor sharing to help address this, but in some scenarios, forcing exact matching with CURSOR_SHARING_EXACT is still the more predictable and best approach.

Using the CURSOR_SHARING_EXACT Hint Example

Let’s look at an example where we want to retain literal values and prevent Oracle from replacing them with bind variables during parsing

SELECT /*+ CURSOR_SHARING_EXACT */

    employee_id,

    first_name,

    last_name

FROM employees

WHERE department_id = 10

/

In this case, Oracle will not rewrite the query to use a bind variable, even if CURSOR_SHARING = FORCE is set at the system level. This allows Oracle to optimize the execution plan specifically for department_id = 10.  Below shows a detailed query plan that can be produced.

Plan hash value: 927387651

---------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |   100 |  3200 |     5  (0) | 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES    |   100 |  3200 |     5  (0) | 00:00:01 |

|   2 |   INDEX RANGE SCAN           | EMP_DEPT_IDX |   100 |       |     2  (0) | 00:00:01 |

---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1 / EMPLOYEES@SEL$1

   2 - SEL$1 / EMPLOYEES@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      USE_INDEX(@"SEL$1" "EMPLOYEES"@"SEL$1" "EMP_DEPT_IDX")

      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" "EMP_DEPT_IDX")

      CURSOR_SHARING_EXACT

      OPTIMIZER_FEATURES_ENABLE('19.1.0')

      DB_VERSION('19.1.0')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      END_OUTLINE_DATA

  */

Note

-----

  - dynamic sampling used for this statement (level=2)

Considerations

Before using the CURSOR_SHARING_EXACT hint, keep the following in mind:

• Use it when literal-specific optimization is important for performance.

• It disables any sharing of SQL cursors unless they are identical.

• It may increase the number of child cursors in the shared pool and could impact memory usage.

• This hint is especially useful in systems with dynamic SQL or ORMs that generate many similar queries with different literals.

• Be very cautious, excessive use may lead to cursor cache bloat and reduced performance due to increased hard parses.
  
  

Further Reading

• You can find more information on using the hint in the documentation 

Published on 

Published 8th May 2025