ORACLE CURSORS BASICS

An SQL cursor is nothing but an area in user session memory which contains data about a specific statement. An SQL cursor actually has 2 parts.. The first heap which contains library cache metadata and second heap which is a larger heap contains executable representation of the cursor (usually called SQLAREA). Each heap is comprised of one or more chunks of memory.

SQL AREA can be thought of as a handle which points to a private sql area in UGA and the further pointing to the shared SQL area. All SQL cursors are allocated in 4K chunks. The bigger the cursor the more 4K chunks are allocated. An open cursor has already been parsed and the cursor handle is in library cache.

If the cursor is not in shared pool obviosuly it has to be reconstructed (hard parse) which requires a shared pool and library cache latch. A soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.

To reduce the mount of latching in your database DBA's configure a session cursor cache which contains instantiation of shared child cursors (closed session cursors) so that repeatable statements can reuse these cursors. You can store the reusable cursors in this session cursor cache. And the parameter session_cached_Cursors is independent of open_cursors. That means you can keep it higher than open_cursors because session cursors are not cached in open state. In 11G R2 default value of open_cursor is 300 and for session_Cached_cursors is 50

When the statement is in library cache, the associated cursor for the statement is placed on a hash chain. All these cursor handles are in shared pool inside hash chains. The parsing procedure actually has the following workings :=

1) find and execute an open cursor
2) Find a closed cursor in session cursor cache
3) search the hash chains for a soft parse
4) Construct the cursor (hard parse)

A Cursor is a handle to a specific PRIVATE SQL AREA. PRIVATE SQL AREA which is in the UGA keeps information about a parsed sql statement and session specific information for processing. When a statement is executed the process uses private sql area to store bind variable values, query execution work area and so on...
A Shared SQL area is different from private sql area. The shared sql area is in library cache containing execution plans in the SGA.

In the context of PL/SQL, the following is a notion of cursors

STATIC CURSORS

Static cursors are normal cursors like (explicit and implicit). For explicit cursors...A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement. A programmer can open, fetch data, close and check attributes of this kind of cursor.

Implicit cursors are more fast and they have less coding effort. And this one will never raise INVALID_CURSOR error and it cannot be opened outside the statement.

DYNAMIC CURSORS

Ref CURSORS created only when it is opened. A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc....)

Adaptive Cursor Sharing

ADAPTIVE CURSOR SHARING CONCEPTS FOR Oracle database 10G DBA's

The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the
cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.

BIND SENSITIVE CURSORS

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:

1) The optimizer has peeked at the bind values to generate selectivity estimates.
2) A histogram exists on the column containing the bind value.

BIND AWARE CURSORS

A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate. When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values.

If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:

1) Generates a new plan based on the new bind value.
2) Marks the original cursor generated for the statement as not shareable (V$SQL.IS_SHAREABLE is N). This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.

CURSOR MERGING

If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform cursor merging. In this case, the database merges cursors to save space in the shared SQL area. The database increases the selectivity range for the cursor to include the selectivity of the new bind. Suppose you execute a query with a bind value that does not fall within the selectivity ranges of the existing cursors. The database performs a hard parse and generates a new plan and new cursor. If this new plan is the same plan used by an existing cursor, then the database merges these two cursors and deletes one of the old cursors.