Cursor Sharing

Cursor Sharing

Cursor sharing is an important component of Database System. 

It is mainly based on Hard and Soft parse mechanisms.

When the query is first executed, the optimizer generates a new execution plan and a cursor, storing sql information in the Shared Pool memory area (the RDBMS executes a hard-parse). 

When the same query will be executed again the plan is not more generated: the already evaluated plan stored in Shared Pool is used, saving time and resources 

(the RDBMS executes a soft-parse).

 In this second case, the Database System doesn’t need to create a new cursor because, thanks to cursor sharing, the DB re-uses the previous generated one.

The basic soft/hard parse mechanism and cursor sharing is explained in the following Diagram :-

Hard Parses can be largely reduced using Bind Variables.

Actually, Oracle differentiates between parent and child cursor stored in the Shared Pool area.

Every parsed SQL statement has a parent cursor and one or more child cursors. The parent cursor stores the text of the SQL statement. If the text of two statements is identical, then the statements share the same parent cursor. If the text is different, however, then the database creates a separate parent cursor.

Every parent cursor has one or more child cursors. A child cursor contains the execution plan, bind variables, metadata about objects referenced in the query, optimizer environment, and other information. In contrast to the parent cursor, the child cursor does not store the text of the SQL statement.

Oracle generates child cursors (linked to an unique parent cursor) for nearly 60 reasons. The optimizer decides whether to generate a cursor or not, for example, using cardinality feedbacks, that are sql execution statistics. In this way, for next sql executions, the Optimizer can make his choice evaluating more than one plan, without executing an actual Hard Parse. 

In this case we could say that the DB is executing an “Harder” Soft Parse:

This is very useful because can reduce cardinality impact on execution costs. The v$sql_shared_cursor view contains the reason why a certain cursor exists (and why it is not shared with existing child cursors). In fact, this view has more than 60 columns and each column identifies a specific reason why the cursor can’t be shared.

Bug 14176247: cursors issues using Adaptive Cursor Sharing with binds

The documented bug causes the creation of too much cursors and affects 12c release. 

As a Symptom - The bug noticed an huge concurrency in the database.This is How the OEM looks like -