Parent and Child Cursors in Oracle Database

What are parent and child cursor in Oracle database server? Why do we have cursors? This post is going to explain them in simple terms.

When we say a cursor in database, the iterator over a result set of a database query comes into mind. True, at the client side, cursor is an iterator. However, at the server side, a cursor is a data structure that the database server creates for SQL query.

At the server, each SQL query received go through a life cycle. It is important to have a rough idea of what this life cycle is in order to better understand cursors. Let me summarize what happens when you submit a query to Oracle database. It goes through the following phases.

1. Open
2. Parse - syntax analysis, semantic analysis, privilege check, save
3. Bind - think of as passing input parameters for a function
4. Define - how do you want the output
5. Execute
6. Fetch

Remember SQL is a language. Similar to other languages, we need a compiler, optimzier and code generator. It is expensive to go through all of these operations each time when a query is submitted. If we can save the compiled objects, if a same or similar query is re-submitted instead of going through compile-optimize-code-generate phases we can simply re-use these compiled objects. This is exactly what cursors try to do.

During the parse phase, after successful syntax analysis, semantic analysis, and privilege check, Oracle database server allocates a data structure for the statement in UGA memory belonging to the session if a matching structure is not already available. This structure is called a cursor. A cursor holds the parsed statement and other information for processing.

For each SQL statement, Oracle actually associates it with two cursors; parent and child cursors. Why two cursors? For the same SQL statement, there could be differences such as two different schemas, different bind values, different literals, etc. The parent holds the SQL statement and each child holds information related to the differences.

You can access parent cursors from V$SQLAREA view and child cursors from V$SQL. Let me explain these cursors through an example.

SQL> conn / as sysdba
SQL> create user u1 identified by p1;
SQL> create user u2 identified by p2;
SQL> grant dba to u1, u2;
SQL> conn u1/p1
SQL> create table t(c int);
SQL> conn u2/p2
SQL> create table t(c int)
-- Notice that we have the same in both u1 and u2's schemas.

-- Remove saved cursors (for demonstration purposes)
SQL> conn / as sysdba
SQL> alter system flush shared_pool; 

SQL> conn u1/p1
SQL> select * from t;
-- The above statement creates a parent and child cursor. 
SQL> select sql_id from v$sqlarea where sql_text like 'select * from t%';

SQL_ID
-------------
27uhu2q2xuu7r

SQL> select child_number from v$sql where sql_id = '27uhu2q2xuu7r';

CHILD_NUMBER
--------------------
                           0

-- Now u1 executing the same statement again
SQL> select * from t;

If you look at v$sqlarea and v$sql, you won't find any new entries, but rather, Oracle is reusing the previous cursors as nothing has changed.


SQL> conn u2/p2
-- Now u2 executing the same statement
SQL> select * from t;

SQL> select sql_id from v$sqlarea where sql_text like 'select * from t%';

SQL_ID
-------------
27uhu2q2xuu7r

SQL> select child_number from v$sql where sql_id = '27uhu2q2xuu7r';

CHILD_NUMBER
------------
           0
           1

Since it is the exact same statement that u1 executed, all statements share the same parent cursor. However, notice that their are two child cursors. Due to various reasons, child cursors may not be shared. In this specific case, even though the SQL statement is exactly the same, they are executed on two different schemas. Hence, two child cursors. v$sql_shared_cursor view has information regarding as to why cursors cannot be shared. The following picks a few reasons from the above view:

SQL> select child_number, bind_mismatch, optimizer_mode_mismatch, literal_mismatch, auth_check_mismatch from v$sql_shared_cursor where sql_id = '27uhu2q2xuu7r';

CHILD_NUMBER B O L A
------------ - - - -
           0 N N N N
           1 N N N Y

As you can see, in this case, two different cursors are created as authentication checks mismatch.

Notice that both have the same exact query plan:
SQL> select * from table( dbms_xplan.display_cursor('27uhu2q2xuu7r', 0 ) );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3688435342

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |


SQL> select * from table( dbms_xplan.display_cursor('27uhu2q2xuu7r', 1 ) );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  27uhu2q2xuu7r, child number 1
-------------------------------------
select * from t1

Plan hash value: 3688435342

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |

What if u1 execute the following two exactly the same statements?
select * from t;
select * from u1.t;

SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select * from %t';

SQL_ID          SQL_TEXT
------------------------------------
b0pqkh4zgw6xt   select * from u1.t1
27uhu2q2xuu7r   select * from t1

It creates two parent cursors. But why? it does not make sense to have two as they are the same. Wait! if you look carefully they are semantically different. In the first statement, t is resolved be a table in the executor's schema whereas in the second statement, t is always in u1's schema.

What if u1 execute the following two statements? Will we have two parent cursors?
SQL> conn u1/p1
SQL> select * from t where c = 5;
SQL> select * from t where c = 10;

SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select * from %t';

SQL_ID          SQL_TEXT
------------------------------------------------------
9gkwnw4g49hbt   select * from t1 where c1 = :"SYS_B_0"
3n9xkpf0ja5b6   select * from t1 where c1 = 10

We still have two parents. Why? The answer depends on the cursor_sharing parameter.

SQL> show parameter cursor_sharing;

NAME             TYPE        VALUE
---------------- ----------- ------
cursor_sharing   string      EXACT

The default cursor_sharing criteria is EXACT. For each different SQL statement, it creates a new parent cursor. Since the above two SQL texts are different, we have two parent cursors. Can't we change this behavior and save some more processing by sharing the parent cursor in situation like this? Yes, we can. We can use FORCE or SIMILAR cursor_sharing criteria to instruct Oracle to share cursors in such situations.

-- Let's change the cursor_sharing parameter to force.
SQL> conn / as sysdba
SQL> alter system set cursor_sharing = force;

Now u1 executing the above two statements again.
SQL> conn u1/p1
SQL> select * from t where c = 5;
SQL> select * from t where c = 10;

SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select * from %t';

SQL_ID          SQL_TEXT
------------------------------------------------------
9gkwnw4g49hbt   select * from t1 where c1 = :"SYS_B_0"

Now there is only one parent cursor for both statement even though they do not have exact SQL text. They only differ by a literal. Notice that it is replaced by a bind variable and both generate the same query plan.