Child Cursors

If you want to understand what is a child cursor, (like the ones you see when you query V$SQL, and which are identified by a unique pair of (SQL_ID, CHILD_NUMBER)), and why they are created, then check out these two great explanations by Tom Kyte:

1. From the asktom site
2. From Oracle Magazine

Keep in mind that:

• A parent cursor essentially represents the text of an SQL statement and is uniquely identified by an SQL_ID (prior to 10g was identified by address and hash_value). Parent cursors are externalized via dynamic view V$SQLAREA
• A child cursor represents the execution plan as well as the execution environment for a specific SQL_ID (i.e., a parent cursor) and it is uniquely identified by SQL_ID and CHILD_NUMBER (address, hash_value and child_number in older versions). Child cursors are externalized via V$SQL.
  
  From the 2nd point we infer that whenever the execution plan changes for a specific SQL statement (i.e., a parent cursor), then a new child cursor is created. However, this is true per database instance. Because in a RAC enviroinment (e.g., Exadata) where there exist multiple instances, if you query the dynamic view GV$SQL (which covers all available instances - instead of V$SQL), then you might see that a new execution plan might be executed by the same child cursor but in a different instance. In other words, for the same (SQL_ID, child_number) pair you might see more than one plan_hash_values (i.e., different execution plans) but each such pair (i.e., child cursor), will be loaded in the library cache of a different instance (therefore, the value in the GV$SQL.INST_ID column will be different).

Enjoy!
Oracle Learner.