jump to navigation

Oracle Child Cursors – Part1 March 25, 2012

Posted by puthranv in Parsing.

 Well the blog has been moved to www.puthranv.com . All the old articles are also moved.

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



No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: