Oracle DBA

Cursor sharing in Oracle is a feature that allows multiple sessions to share the same cursor, which can improve performance by reducing the overhead of parsing and optimizing statements.

Oracle can share cursors for the following types of statements:

• SELECT statements
• INSERT, UPDATE, and DELETE statements with bind variables
• PL/SQL blocks with bind variables

Oracle uses a library cache to store shared cursors. When a session executes a statement, Oracle first checks the library cache to see if there is a shared cursor that can be used. If there is, Oracle uses the shared cursor. If there is not, Oracle creates a new cursor and adds it to the library cache.

The following factors can affect whether or not two statements can share the same cursor:

• The text of the statement
• The values of the bind variables
• The optimizer mode
• The object definitions
• The session parameters

If any of these factors differ between two statements, Oracle will create a new cursor for each statement.

There are three settings for the CURSOR_SHARING initialization parameter:

• EXACT: Only statements with an exact text match will share the same cursor.
• FORCE: Oracle substitutes system-generated bind variables for all literals, thereby increasing the chances of a text match.
• SIMILAR: Deprecated in Oracle 11.2. Oracle substitutes system-generated bind variables for all literals, thereby increasing the chances of a text match. Oracle will force similar statements to share the SQL area without deteriorating execution plans.

The default setting for the CURSOR_SHARING parameter is EXACT.

Cursor sharing can be a very effective way to improve performance, but it is important to use it carefully. If you use the FORCE setting, you may end up sharing cursors for statements that have different meanings, which can lead to unexpected results.

Here are some tips for using cursor sharing effectively:

• Use bind variables in your SQL statements.
• Set the CURSOR_SHARING parameter to EXACT or FORCE, depending on your needs.
• Be aware of the factors that can affect cursor sharing.
• Monitor cursor sharing activity to ensure that it is not causing any problems.

If you are having performance problems with your Oracle database, you should consider enabling cursor sharing. It is a relatively simple way to improve performance without having to make any changes to your application code.

Cursor sharing in Oracle 19c remains consistent with previous versions, allowing multiple sessions to share cursors and enhancing performance by reducing the parsing and optimization overhead for SQL statements.

Oracle 19c introduces notable features related to cursor sharing:

1. Adaptive cursor sharing: This feature permits a single SQL statement with bind variables to adapt to multiple execution plans, which is particularly advantageous when the statement is executed with various bind variable values. It avoids the need for repetitive parsing and optimization for each distinct value.
2. Cursor sharing for temporary tables: Oracle 19c extends cursor sharing to private temporary tables within the same session, offering performance benefits for applications that frequently utilize temporary tables.

To leverage cursor sharing in Oracle 19c effectively, configure the CURSOR_SHARING initialization parameter to either EXACT or FORCE, with EXACT being the default setting for exact text matches and FORCE substituting literals with system-generated bind variables to enhance sharing possibilities. Utilize bind variables in SQL statements to facilitate cursor sharing and remain mindful of factors affecting it, such as optimizer modes, object definitions, and session parameters. Regularly monitor cursor sharing activity to prevent potential issues. Enabling cursor sharing is a straightforward means to enhance Oracle database performance without requiring extensive application code modifications.