PARENT AND CHILD CURSORS IN ORACLE

Database, Uncategorized
  In this post I will discuss about parent and child cursors – what do they contain, when are they created and how and why can we ensure that they can be shared.
(Please click here for the video of this post)
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.
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
Let us see how multiple parent parent/child cursors can be created for SQL statements.
Multiple Parent Cursors
- Created because of differences in SQL statement text
- The following statements all require separate parent cursors:
  . SELECT COUNT(*) FROM employees;
  . Select count(*) from employees;
  . SELECT COUNT(*)  FROM EMPLOYEES;
SYS>ALTER SYSTEM FLUSH SHARED_POOL;
HR>SELECT COUNT(*) FROM employees;
   Select count(*) from employees;
   SELECT COUNT(*) FROM EMPLOYEES;
– Let’s check v$SQLAREA for parent cursors for above statements.
– Note that
   .there is one record for each statement in v$sqlarea as   one parent cursor is created for each sql statement since  each of these statements differ in their text.
   . Each statement has different SQL_ID/HASH_VALUE
   .There is one child per parent cursor (version_count=1)
SYS>col sql_text for a30 word_wrapped
    SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
    FROM V$SQLAREA
    WHERE LOWER(SQL_TEXT) LIKE ‘%select count(*) from employees’
      AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';
SQL_TEXT                            SQL_ID        VERSION_COUNT HASH_VALUE
—————————— ————- ————- ———-
SELECT COUNT(*) FROM  2ybtna401c2x2             1                 1444770
employees
SELECT COUNT(*) FROM  4ba5cdz25czfq             1                  3294002646
EMPLOYEES
Select count(*) from               99a1j2spyr81k             1                  736862258
employees
– Let’s check v$SQL for child cursors for above statements.
- Note that
  . Each statement has one child (CHILD# = 0)
  . Each statement uses same execution plan (same PLAN_HASH_VALUE)
SYS>col child_number for 99
    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE, PLAN_HASH_VALUE
     FROM V$SQL
    WHERE LOWER(SQL_TEXT) LIKE ‘%select count(*) from employees’
      AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';
SQL_TEXT                           SQL_ID            CHILD# HASH_VALUE PLAN_HASH_VALUE
—————————— ————-    ———- —————— ————————-
SELECT COUNT(*) FROM 2ybtna401c2x2          0    1444770              3580537945
employees
SELECT COUNT(*) FROM 4ba5cdz25czfq           0 3294002646            3580537945
EMPLOYEES
Select count(*) from              99a1j2spyr81k          0  736862258            3580537945
employees
Although same execution plan is being used by the 3 statements still they were individually parsed and their parent cursors are consuming memory.
Hence to enable sharing of the parent cursors, we should follow standard formatting of the sql statements.
Multiple Child Cursors for the same parent cursor
- Can be created for a number of reasons . In this post, I will discuss creation of multiple child cursors due to  differences in:
  . System / Session parameters
  . Object translation
  . Bind variables (Name and value)
  . NLS parameters
-- Difference in System / Session Parameters
Multiple c hild cursors maybe creatd for the same SQL due to difference in system / session parameters
- Let’s verify that different optimizer modes require separate child cursors
- Issue same SQL statement with different values of the parameter optimizer_mode
SYS>ALTER SYSTEM FLUSH SHARED_POOL;
HR>ALTER SESSION SET optimizer_mode = CHOOSE;
   SELECT COUNT(*) FROM EMPLOYEES;
- Change optimizer mode to ALL_ROWS
HR>ALTER SESSION SET optimizer_mode = ALL_ROWS;
   SELECT COUNT(*) FROM EMPLOYEES;
– Let’s check v$SQLAREA for parent cursors for above statements.
– Note that
   .there is ONLY one record for both the statements in v$sqlarea as  one parent cursor is created for both sql statements since  each of these statements are identical in their text.
   . Both the statements have same SQL_ID/HASH_VALUE
   .There are two  childs per parent cursor (version_count=2)
SYS>col sql_text for a30 word_wrapped
    SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
    FROM V$SQLAREA
    WHERE LOWER(SQL_TEXT) LIKE ‘%select count(*) from employees’
      AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';
SQL_TEXT                            SQL_ID        VERSION_COUNT   HASH_VALUE
——————————       ————-    ———————   —————–
SELECT COUNT(*) FROM 4ba5cdz25czfq               2                3294002646
EMPLOYEES
- Let’s check v$SQL for child cursors for above statements.
- Note that
  . The statement has two children (CHILD# = 0,1)
  . OPTIMIZER_MODE is stored in V$SQL for each child cursor
SYS>col child_number for 99
    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
           child_address, OPTIMIZER_MODE OPT_MODE
     FROM V$SQL
    WHERE LOWER(SQL_TEXT) LIKE ‘%select count(*) from employees’
      AND LOWER(SQL_TEXT) NOT LIKE ‘%hash%';
SQL_TEXT                                SQL_ID            CHILD# HASH_VALUE CHILD_AD OPT_MODE
——————————            ————-        ———- —————– ———— ———-
SELECT COUNT(*) FROM     4ba5cdz25czfq          0      3294002646      4C202DB0 CHOOSE
EMPLOYEES
SELECT COUNT(*) FROM     4ba5cdz25czfq          1      3294002646      4C1FEBF8 ALL_ROWS
EMPLOYEES
– Let’s find out from V$SQL_SHARED_CURSOR why the child cursor was not shared
   Note that in second child cursor was created because of mismatch in  optimizer mode
(OPTIMIZER_MODE_MISMATCH = Y)
SQL>col OPTIMIZER_MODE_MISMATCH for a25
    SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS, C.OPTIMIZER_MODE_MISMATCH
    FROM   V$SQL S, V$SQL_SHARED_CURSOR C
    WHERE LOWER(S.SQL_TEXT) LIKE ‘%select count(*) from employees’
      AND LOWER(S.SQL_TEXT) NOT LIKE ‘%hash%’
      AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;
SQL_TEXT                                   CHILD#  CHILD_AD OPTIMIZER_MODE_MISMATCH
——————————             ———–     ——–      ———————————
SELECT COUNT(*) FROM                0       4C202DB0                       N
EMPLOYEES
SELECT COUNT(*) FROM                1       4C1FEBF8                       Y
EMPLOYEES
OBJECT TRANSLATIONS
If a statement references different objects with the same name then multiple child cursors can be generated
For example, we will create two tables with same name  t1 with same structure in HR and SCOTT schema and issue identical SQl statement referring to the two tables.
SYS>ALTER SYSTEM FLUSH SHARED_POOL;
HR>CREATE TABLE t1 (c1 NUMBER);
SELECT c1 FROM t1;
SCOTT>CREATE TABLE t1 (c1 NUMBER);
SELECT c1 FROM t1;
The statement SELECT c1 FROM t1 will have a shared parent cursor, but multiple child cursors
                Parent (select c1 from t1;)
                    +
     +————–+————-+
     |                               |
   Child I                    Child II
  (HR.t1)                   (SCOTT.t1)
– Let’s check v$SQLAREA for parent cursors for above statements.
It can be seen that only one parent cursor has been created with 2 child cursors (version_count=2)
SYS>col sql_text for a30 word_wrapped
    SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
    FROM V$SQLAREA
    WHERE SQL_TEXT LIKE ‘%SELECT c1 FROM t1%’
      AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';
SQL_TEXT                                                       SQL_ID        VERSION_COUNT HASH_VALUE
————————————————-         ————- ————- ————————–
SELECT COUNT(*) FROM EMPLOYEES 4ba5cdz25czfq             2         3294002646
- Let’s check v$SQL for child cursors for above statements.
SYS>col child_number for 99
    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
           PLAN_HASH_VALUE, USERNAME
     FROM V$SQL S, DBA_USERS U
    WHERE SQL_TEXT LIKE ‘%SELECT c1 FROM t1%’
      AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%’
      AND U.USER_ID = S.PARSING_USER_ID;
SQL_TEXT                             SQL_ID            CHILD# HASH_VALUE PLAN_HASH_VALUE USER
——————————     ————- ———- ———- ————— —————– ——-
SELECT c1 FROM t1             68buhgvjct1pg          0   3805054639        3617692013                 HR    SELECT c1 FROM t1            68buhgvjct1pg          1   3805054639        3617692013          SCOTT
- Note that
  . The statement has two children (CHILD# = 0,1) because they refer to different objects
  . ID of the user executing the statement is stored in V$SQL (parsing_user_id)
    for each child cursor

– Find out why the child cursor was not shared

  SQL>col TRANSLATION_MISMATCH for a25
    SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS, C.TRANSLATION_MISMATCH
    FROM   V$SQL S, V$SQL_SHARED_CURSOR C
    WHERE SQL_TEXT LIKE ‘%SELECT c1 FROM t1%’
      AND LOWER(S.SQL_TEXT) NOT LIKE ‘%HASH%’
      AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;
SQL_TEXT                       CHILD_NUMBER CHILD_AD TRANSLATION_MISMATCH
—————————— —————— ————- ———————————
SELECT c1 FROM t1                         0            4C3181C8                       N
SELECT c1 FROM t1                         1            4C3EE02C                      Y
 Note that in second child cursor was created because of translation mismatch
   (TRANSLATION_MISMATCH = Y)
BIND VARIABLES (SIZE AND VALUE)
Multiple child cursors are created for different lengths and values of bind variables .
For example , we will issue the same statement containing  bind variable twice but with different size of the bind variable.
SYS>ALTER SYSTEM FLUSH SHARED_POOL;
HR>DROP TABLE T1 PURGE;
   CREATE TABLE t1 (c1 VARCHAR2(50),c2 NUMBER);
VARIABLE v1 VARCHAR2(30);
SELECT c2 FROM t1 WHERE c1 = :v1;
VARIABLE v1 VARCHAR2(40);
SELECT c2 FROM t1 WHERE c1 = :v1;
– Let’s check v$SQLAREA for parent cursors for above statements.
– Note that two child cursors are created (VERSION_COUNT = 2)

SYS>col sql_text for a35 word_wrapped

    SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
    FROM V$SQLAREA
    WHERE sql_text LIKE ‘SELECT c2 FROM t1 WHERE c1 = %';
SQL_TEXT                            SQL_ID        VERSION_COUNT HASH_VALUE
———————————– ————- ————- ———————–
SELECT c2 FROM t1     c0w72qhanq1zs             2                            357238776
WHERE c1 = :v1

- Let’s check v$SQL for child cursors for above statements. 

- Note that
  . The statement has two children (CHILD# = 0,1) because size of bind variables is different although the execution plan is same (same PLAN_HASH_VALUE)

SYS>col child_number for 99

    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
           PLAN_HASH_VALUE
     FROM V$SQL
    WHERE sql_text LIKE ‘SELECT c2 FROM t1 WHERE c1 = %';
SQL_TEXT                            SQL_ID            CHILD# HASH_VALUE PLAN_HASH_VALUE
———————————– ————- ———- ———- ——————————
SELECT c2 FROM t1     c0w72qhanq1zs          0                357238776              3617692013
WHERE c1 = :v1
SELECT c2 FROM t1     c0w72qhanq1zs           1               357238776                    3617692013
WHERE c1 = :v1
– Find out why the child cursor was not shared
   Note that in second child cursor was created because of bind variable length mismatch
   (BIND_LENGTH_UPGRADEABLE = Y)
SQL>col BIND_LENGTH_UPGRADEABLE  for a25
    SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS,BIND_LENGTH_UPGRADEABLE
    FROM   V$SQL S, V$SQL_SHARED_CURSOR C
    WHERE sql_text LIKE ‘SELECT c2 FROM t1 WHERE c1 = %’
      AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;
SQL_TEXT                            CHILD_NUMBER CHILD_AD BIND_LENGTH_UPGRADEABLE
———————————– —————–   ——– —————————————–
SELECT c2 FROM t1                         0                     4C26D7E0             N
WHERE c1 = :v1
SELECT c2 FROM t1                         1                      4C252500             Y
WHERE c1 = :v1
NLS PARAMETERS
Multiple child cursors are created for the same statement for different values of NLS parameters. Only a subset of SQL statements are affected which use
Dates
Currency
Ordering
For example, we will issue the same SQL statement  with different values of NLS_LANGUAGE
HR>VAR b1 VARCHAR2(30);
EXECUTE :b1 := SYSDATE;
ALTER SESSION SET NLS_LANGUAGE = ‘AMERICAN‘;
SELECT TO_CHAR (TO_DATE (:b1,’DD-MON-YYYY’),’yyyymmdd’) FROM dual;
ALTER SESSION SET NLS_LANGUAGE = ‘GERMAN';
SELECT TO_CHAR (TO_DATE (:b1,’DD-MON-YYYY’),’yyyymmdd’) FROM dual;
– Let’s check v$SQLAREA for parent cursors for above statements.
– Note that two child cursors are created (VERSION_COUNT = 2)
SYS>col sql_text for a35 word_wrapped
    SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE
    FROM V$SQLAREA
    WHERE sql_text LIKE ‘SELECT TO_CHAR (TO_DATE (:b1%';
SQL_TEXT                                              SQL_ID              VERSION_COUNT HASH_VALUE
—————————————– ————-           —————————- ———-
SELECT TO_CHAR (TO_DATE             5588uyghqp14w             2                     3781854364
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
- Let’s check v$SQL for child cursors for above statements. 
- Note that
  . The statement has two children (CHILD# = 0,1) because of the difference in NLS_LANGUAGE parameter
SYS>col child_number for 99
    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
           PLAN_HASH_VALUE
     FROM V$SQL
   WHERE sql_text LIKE ‘SELECT TO_CHAR (TO_DATE (:b1%';
SQL_TEXT                                        SQL_ID          CHILD# HASH_VALUE PLAN_HASH_VALUE
———————————– ————- ———- ———- ————————————–
SELECT TO_CHAR (TO_DATE      5588uyghqp14w          0           3781854364      1388734953
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
SELECT TO_CHAR (TO_DATE             5588uyghqp14w    1           3781854364      1388734953
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
– Find out why the child cursor was not shared
   Note that in second child cursor was created because oflanguage  mismatch
   LANGUAGE_MISMATCH = Y)
SQL>col LANGUAGE_MISMATCH  for a25
    SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS,LANGUAGE_MISMATCH
    FROM   V$SQL S, V$SQL_SHARED_CURSOR C
   WHERE sql_text LIKE ‘SELECT TO_CHAR (TO_DATE (:b1%’
      AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;
SQL_TEXT                                         CHILD_NUMBER CHILD_AD  LANGUAGE_MISMATCH
—————————————- ———— —————————————————
SELECT TO_CHAR (TO_DATE                        0            4A0EFF20                  N
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
SELECT TO_CHAR (TO_DATE                        1            4A0EFADC                 Y
(:b1,’DD-MON-YYYY’),’yyyymmdd’)
FROM dual
Conclusion:
- To enable sharing of parent cursors we should follow code formatting standards
- To enable sharing of child cursors we should ensure that
  . Optimizer mode is not changed in sessions.
  . Length of bind variables used should be same.
  . Values of NLS parameters should be same.
————————————————————————
Related links :

Home

Database Index
Tuning Index
Cursor Sharing Demystified 
Library Cache Lock And Pin Demonstrated
Latches, Locks, Pins And Mutexes 
Shared Pool Architecture 

                                 

                                                   ————————-

                                 

19 thoughts on “PARENT AND CHILD CURSORS IN ORACLE”

  1. Hello Anju,

    I am a regular reader of your valuable post and really appreciate the knowledge you posses and help other dbas
    to understand complex topics. You are really wonderful.

    Just wanted to point out that in your posts, the right most pane of the website (where Categories,Archives) cover the Text area and we cant read the full line. Could you please look into it.

    Thanks & Regards,
    Manish.

    Reply

    2. Hi Anju,
      can I relate below ..

      SQL_ID= sql statement ( means oracle do internal algorithm and generate a identifier)
      HASH_VALUE = parent cursor value
      PLAN_HASH_VALUE= child cursor value

      I am bit confused in SQL_ID, HASH_VALUE, PLAN_HASH_VALUE,CHILD#,VERSION_COUNT,CHILD_ADDRESS..

      Regards,
      Pawan

      Reply

      1. Hi Pawan,
        HASH_VALUE – Hash value of the parent statement in the library cache
        SQL_ID – SQL identifier of the parent cursor in the library cache
        For further details on HASH_VALUE and SQL_ID, pls refer to:
        https://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/
        PLAN_HASH_VALUE – Numerical representation of the SQL plan for the child cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same
        CHILD_NUMBER – Number of the child cursor
        VERSION_COUNT – Number of child cursors that are present in the cache under this parent
        CHILD_ADDRESS – Address of the child cursor

        Hope it helps

        Your comments and suggestions are always welcome.

        Regards
        Anju

        Reply

  2. Hi Anju,
    Your blog articles are very good with interesting explanation and examples .And it is very good.. ur mentioning the referenced articles too. expecting too many in future ….

    Best Regards
    Ashok Reddy

    Reply

  4. Hi Anju,
    Could you please explain the Differences between private sql area and shared sql area with examples

    Thanks
    Moses

    Reply

    1. Hi Moses,
      Pls refer to following link: https://docs.oracle.com/cd/E11882_01/server.112/e40540/memory.htm#CNCPT1237
      A private SQL area holds information about a parsed SQL statement and other session-specific information for processing. A private SQL area is divided into the following areas:
      The run-time area: This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.
      The persistent area: This area contains bind variable values.
      Shared SQL area, which stores execution plans in the SGA.
      Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. For example, 20 executions of SELECT * FROM employees in one session and 10 executions of the same query in a different session can share the same plan. The private SQL areas for each execution are not shared and may contain different values and data.

      regards
      Anju

      Reply

  7. Hi Anuj,

    Thank You very much for the wonderful post. The post easy to understand because of theoretical and practical explanations. Now i can say that i know cursors and their functionality.

    Reply

    1. Hi Naveed

      Child cursors of same parent cursor may contain same / different plans.

      A new cursor is created when earlier cursor could not be shared ,for example, due to change in some optimizer parameter.
      However, even in new environment, optimizer may generate same / different execution plan as earlier.

      Regards
      Anju Garg

      Reply

Leave a Reply to Anju Garg Cancel reply