Shared Pool: Parent & Child Cursors in Shared Pool

 

What are PARENT AND CHILD CURSORS IN ORACLE
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.
Dynamic View :- 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
Dynamic View:- 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.
Since we want to economize on the memory consumption, we would like that equivalent SQL statements should use the same cursor
e.g. select * from employees and SELECT * FROM EMPLOYEES achieve the same objective and have the same execution plan and hence only one cursor should be created and should be used when either of the statements is issued. But it won’t be so and two parent and hence two child cursors will be created since the  two statements are textually different .
If we have two textually identical statements, only one parent cursor will be created but multiple child cursors and hence multiple execution plans can be created if for example  bind variables have different values/sizes for different executions of the same statement.
When you have the same statement that has several versions (children), the view v$sql_shared_cursor shows the reason why the statement cannot be shared.
You may be able to find that for each child cursor except the first one, why it was not possible to share a previously created child cursor.
For several types of incompatibility there is a column that is set to either N (not a mismatch) or Y (mismatch).
The following table lists various columns which represent different types of incompatibilities which could lead to non sharing of the child cursors:

ANYDATA_TRANSFORMATION	Is criteria for opaque type transformation and does not match
AUTH_CHECK_MISMATCH	Authorization/translation check failed for the existing child cursor
BIND_MISMATCH	The bind metadata does not match the existing child cursor. Likely a difference in bind variable definition.
BIND_PEEKED_PQ_MISMATCH	Cursor based around bind peeked values
BIND_UACS_DIFF	One cursor has bind UACs and one does not
BUFFERED_DML_MISMATCH	Buffered DML does not match the existing child cursor
CURSOR_PARTS_MISMATCH	Cursor was compiled with subexecution (cursor parts were executed)
DESCRIBE_MISMATCH	The typecheck heap is not present during the describe for the child cursor
DIFF_CALL_DURN	If Slave SQL cursor/single call
DIFFERENT_LONG_LENGTH	Value of LONG does not match
EXPLAIN_PLAN_CURSOR	The child cursor is an explain plan cursor and should not be shared
FLASHBACK_CURSOR	Cursor non-shareability due to flashback
FLASHBACK_TABLE_MISMATCH	Cursor cannot be shared because there is a mismatch with triggers being enabled and/or referential integrity constraints being deferred
INCOMP_LTRL_MISMATCH	Cursor might have some binds (literals) which may be unsafe/non-data. Value mismatch.
INCOMPLETE_CURSOR	Cursor is incomplete: typecheck heap came from call memory
INST_DRTLD_MISMATCH	Insert direct load does not match the existing child cursor
INSUFF_PRIVS	Insufficient privileges on objects referenced by the existing child cursor
INSUFF_PRIVS_REM	Insufficient privileges on remote objects referenced by the existing child cursor
LANGUAGE_MISMATCH	The language handle does not match the existing child cursor
LITERAL_MISMATCH	Non-data literal values do not match the existing child cursor
LITREP_COMP_MISMATCH	Mismatch in use of literal replacement
LOGICAL_STANDBY_APPLY	Logical standby apply context does not match
LOGMINER_SESSION_MISMATCH	LogMiner Session parameters mismatch
MULTI_PX_MISMATCH	Cursor has multiple parallelizers and is slave-compiled
MV_QUERY_GEN_MISMATCH	Internal, used to force a hard-parse when analyzing materialized view queries
MV_REWRITE_MISMATCH	Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view
MV_STALEOBJ_MISMATCH	Cursor cannot be shared because there is a mismatch in the list of materialized views which were stale at the time the cursor was built
NO_TRIGGER_MISMATCH	Cursor and child have no trigger mismatch
OPTIMIZER_MISMATCH	A change to any of 33 supported parameters such as SORT_AREA_SIZE or OPTIMIZER_INDEX_COST_ADJUSTMENT and 151 unsupported parameters such as _unnest_subquery that change the optimizer environment.
OPTIMIZER_MODE_MISMATCH	Optimizer mode has changed (for example, ALL_ROWS vs CHOOSE)
OUTLINE_MISMATCH	The outlines do not match the existing child cursor
OVERLAP_TIME_MISMATCH	Mismatch caused by setting session parameter ERROR_ON_OVERLAP_TIME
PDML_ENV_MISMATCH	PDML environment does not match the existing child cursor
PLSQL_CMP_SWITCHS_DIFF	PL/SQL anonymous block compiled with different PL/SQL compiler switches. See DBMS_WARNING page of the library.
PQ_SLAVE_MISMATCH	Top-level slave decides not to share cursor
PX_MISMATCH	Mismatch in one parameter affecting the parallelization of a SQL statement. For example, one cursor was compiled with parallel DML enabled while the other was not.
REMOTE_TRANS_MISMATCH	The remote base objects of the existing child cursor do not match
ROLL_INVALID_MISMATCH	Marked for rolling invalidation and invalidation window exceeded
ROW_LEVEL_SEC_MISMATCH	The row level security policies do not match
ROW_SHIP_MISMATCH	Session does not support row shipping, but cursor built in one that did
SEC_DEPTH_MISMATCH	Security level does not match the existing child cursor
SLAVE_QC_MISMATCH	The existing child cursor is a slave cursor and the new one was issued by the coordinator (or, the existing child cursor was issued by the coordinator and the new one is a slave)
SQL_REDIRECT_MISMATCH	SQL redirection mismatch
SQL_TYPE_MISMATCH	The SQL type does not match the existing child cursor
STATS_ROW_MISMATCH	The existing statistics do not match the existing child cursor. May be caused by tracing
STB_OBJECT_MISMATCH	STB has come into existence since cursor was compiled
TOP_LEVEL_DDL_MISMATCH	Is top-level DDL cursor
TOP_LEVEL_RPI_CURSOR	Is top level RPI cursor
TRANSLATION_MISMATCH	The base objects of the existing child cursor do not match. For example objects in different schemas with the same name.
TYPCHK_DEP_MISMATCH	Cursor has typecheck dependencies
TYPECHECK_MISMATCH	The existing child cursor is not fully optimized
UNBOUND_CURSOR	The existing child cursor was not fully built (in other words, it was not optimized)
USER_BIND_PEEK_MISMATCH	Cursor is not shared because value of one or more user binds is different and this has a potential to change the execution plan