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
- Query – FULL Text – Case sensitive
- Literals / Bind Variables
- 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
- Range of bind values
- Fluctuating statistics
- Changing Environments
- 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