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 -
