Parent Cursor Vs Child Cursor

Parent Cursor Vs Child Cursor: This Section Describes the Cursor:

A cursor is a memory area in library cache allocated to a SQL statement which stores various info about the SQL statement like its text, execution plan, statistics etc.

Each SQL statement has

– One Parent cursor

– One or more child cursors

PARENT CURSOR: 

– It stores the sql text of the cursor. When two statements are identical textually, they will share the same parent Cursor.

– Externalised by V$SQLAREA: Contains one row for each parent cursor

CHILD CURSOR: 

– Each parent requires at least one child cursor but can have more than one child cursors

– The Child Cursor holds other required information, like: the identity of the objects referenced by the SQL Cursor; the names, type and length of the bind variables used..etc.

– Child cursor contains

Environment

Statistics

Execution Plan

Bind variables

– Externalised by V$SQL : Contains one row for each child cursor

– A child cursor takes up less space in the cursor cache. A child cursor doesn’t contain all of the information stored in a parent cursor, for example, the SQL text is only stored in the parent cursor and not in each child.

V$SQL vs. V$SQLAREA:

Each SQL statement has 1 parent cursor, 1 or more child cursors.

V$SQLAREA: parent cursor, it contains one row for each parent cursor.

V$SQL: each parent cursor can have one or more child cursors.

— Compared to parent cursor, child cursors have heap 6 (execution plan).

— Each parent requires at lest 1 child cursor.

— Multiple parent cursors are created because of differences in sql statement text.
The following statements all require separate parent cursors:
1 SELECT COUNT(*) FROM tab1;
2 select count(*) from TAB1;
3 SELECT /*comment*/ COUNT(*) FROM tab1;
4 SELECT COUNT(*) FROM tab1;

Cursor Sharing: EXACT, FORCE and SIMILAR

Note: Default is Exact

• Force: Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
• Similar: Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
• Exact: Only allows statements with identical text to share the same cursor.

— If the dynamic initialization parameter cursor_sharing is set to “EXACT”, this cursor sharing feature is disabled.  If it’s set to “FORCE” or “SIMILAR”, the feature is enabled.

— This parameter can be set at session or system level.

— You can explicitly disable cursor sharing at the SQL statement level by specifying the hint “cursor_sharing_exact”.

–Use “SELECT address, child_address, sql_text FROM v$sql WHEREsql_text LIKE ‘…..%’ to check if two sql statements are the same: use same parent and child cursor.
–The following two similar sql statements will have different results when running the above query to check parent and child cursors, when the cursor sharing paramenter is set to different values: EXACT and FORCE.
select col1 from tab1 where col1=0;
select col1 from tab1 where col1=1;

cursor_sharing=exact: 1 parent cursor with 2 child cursors

cursor_sharing=force:  1 parent cursor with 1 child cursor.  That means Oracle treats the above two sql statements as the same statement.

cursor_sharing=similar:
The histogram factor on the column statistics will have different impacts on the parent and child cursors.Without histogram, Oracle will treat the following 4 sql statements as one statement:

1 parent cursor vs. 1 child cursor:
select col1 from tab1 where col1=0;

select col1 from tab1 where col1=1;

select col1 from tab1 where col1=2;

===================================