Oracle Child Cursors – Part1

What does Child_Number=0 mean. Does it mean that there are no child cursors for the given SQL ID?

There has been an often misconception towards the value “0” associated with Child_Number in V$SQL for a cursor.  Before we deep dive into child cursors, let’s get into cursors.

In a cursor / Query‘s life cycle the major components are

1. Query – FULL Text – Case sensitive
2. Literals / Bind Variables
3. Statistics
4. Execution Plan

There is a One-To-Many relationship between Query Text and other components within the SQL Life Cycle i.,e For a given Query (SQL) there can be

1. Range of bind values
2. Fluctuating statistics
3. Changing Environments
4. N number of execution plans

Based on the changing and non changing components of a cursor they are splitted into parent and child cursors, which looks like below

          Thus for every cursor, Oracle internally treats them in a parent child format. It is just the value given as “0” for the initial child cursor.  The parent cursor is a representation of the Hash Value and the child cursor represents the metadata for the SQL. When the metadata associated with the SQL starts changing it leads to a creation of a  new child cursor. Every cursor has one parent and one or more child cursors. Therefore under these circumstances the 1:1 relation b/w parent and child cursor becomes 1:n i,.e as below