New Features for DBA’s

Subscribe to Posts by Email


 

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages
«  
  »

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

 

Next Post I will write about, cursors behaviors i.e when they will share, when they not, with examples.

-Thanks

Geek DBA

December 29th, 2012 | Tags: | Category: Performance Tuning, SQLTuning

Comments are closed.