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

Query – FULL Text – Case sensitive

Literals / Bind Variables

Statistics

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