Difference between a child cursor and a parent cursor?

For basic information every sql statement in library cache has a parent cursor , it has a handle which is used to search hash value in library cache hash chains for the cursor handle. Mostly it points to its child cursors which are created due to optimizer mismatch and offcourse if semantics are different but the syntax is same. Check them in v$SQL_SHARED_CURSOR why childrens are not being shared.

Every cursor (aka sql statements)  gets two cursors in the library cache. One parent and one child cursor. This is the implementation for cursor multi versioning.

Each child cursor is also comprised of a handle and an object. The child object is comprised of two heaps numbered 0 and 6. Heap 0 contains all the identifying information for a particular version of the SQL statement and heap 6 contains the execution plan. This distinction between parent and child cursors is maintained even when there is only one version of each SQL statement.

The parent cursor contains the SQL statement text only, but no execution plan. Execution plans are found in child cursors. Child cursors are also called versions. 

Let us consider a case: A SQL statement was executed first time, then it would get a parent and child. If the same sql statement was executed again (with no change to the body of SQL keeping the same hash_value ), with a different non-shareable attributes, then a new child cursor will be added to this parent cursor. As you can see, you can have many child cursors associated with a parent cursor. Non-shareable attributed includes : such as different optimizer_mode, different session parameters etc.

In the absence of adaptive cursor sharing you would see lot of child cursors getting created if cursor_sharing was set to similar, for each unique value in the presence of histogram because of data skew after bind variable value peeking. Even if the plan is same you would see a unique child cursor for a distinct value. Even the length of the bind can cause a new child cursor to be created.

I did a following test on my laptop running 11G R2 on Rhel 5 on VM

SQL>create table karan (id number)

Table created.

SQL> select * from karan;

no rows selected

SQL> select sql_id, sql_text, child_number,optimizer_mode,

plan_hash_value from v$sql where sql_text like 'select * from kar%';

SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------ ---------- ---------------
c99yw1xkb4f1u
select * from karan
           0 ALL_ROWS        1357081020


SQL> col SQL_TEXT format a40
SQL> /

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
c99yw1xkb4f1u select * from karan                                  0 ALL_ROWS        1357081020


SQL> select sql_id, sql_text, child_number,optimizer_mode,plan_hash_value from v$sql where sql_text like 'select * from kar%'

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
c99yw1xkb4f1u select * from kar                                  0 ALL_ROWS        1357081020

SQL> show  parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

SQL> alter session set optimizer_mode=first_rows;

Session altered.

SQL> select * from karan;

no rows selected

SQL> select sql_id, sql_text, child_number,optimizer_mode,plan_hash_value from v$sql where sql_text like 'select * from kar%'
  2  ;

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
c99yw1xkb4f1u select * from karan                                  0 ALL_ROWS        1357081020
c99yw1xkb4f1u select * from karan                                  1 FIRST_ROWS      1357081020


SQL> select id+1 from karan;

no rows selected

SQL> select sql_id, sql_text, child_number,optimizer_mode,plan_hash_value from v$sql where sql_text like 'select id+1 from kar%';

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
0nxtnbrbf48rc select id+1 from karan                               0 FIRST_ROWS      1357081020

SQL> select id+1 from karan;

no rows selected

SQL> select sql_id, sql_text, child_number,optimizer_mode,plan_hash_value from v$sql where sql_text like 'select id+1 from kar%';

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
0nxtnbrbf48rc select id+1 from karan                               0 FIRST_ROWS      1357081020