While investigating a performance problem, have you ever wondered why a parent cursor has many child cursors associated with it?

This was me last week.

I was deep into a tuning exercise, when I noticed that one of the SQL statements I was looking at, had many child cursors in the shared pool.

When I say many, I mean 52 child cursors, spread across three instances.

The SQL statement was using bind variables all the time, and the parent cursor should have been reused.
This got me thinking and wondering what was causing the creation of all these child cursors.

SQL>select inst_id, sql_id,CHILD_NUMBER 
    from gv$sql where sql_id='12wm4h2j0a2rp' 
    order by inst_id, child_number;

   INST_ID SQL_ID        CHILD_NUMBER
---------- ------------- ------------
         1 12wm4h2j0a2rp            0
         1 12wm4h2j0a2rp            1
         1 12wm4h2j0a2rp            2
...
         1 12wm4h2j0a2rp           14
         2 12wm4h2j0a2rp            0
         2 12wm4h2j0a2rp            1
         2 12wm4h2j0a2rp            2
...
         2 12wm4h2j0a2rp           18
         3 12wm4h2j0a2rp            0
         3 12wm4h2j0a2rp            1
         3 12wm4h2j0a2rp            2
...
         3 12wm4h2j0a2rp           17

52 rows selected.

There is a dictionary view, that will tell you the reason for the child cursor creation: V$SQL_SHARED_CURSOR
This view has many columns (over 50 columns), of CHAR(1), columns such as SQL_TYPE_MISMATCH, OPTIMIZER_MISMATCH, BIND_MISMATCH, and so on.

If any of these columns have a value of Y (yes), then that is the reason the for the creation of the child cursor. If you want even more details, then you can check out the column REASON, which is a CLOB type and stores an XML text.

I queried the view to find out the reason why I have so many child cursors in the shared pool.
The output is not reader friendly, therefore I will not include it here in the listing.

SQL >select * from gv$sql_shared_cursor where sql_id='12wm4h2j0a2rp'; 

What I will share with you is the column that had a setting of Y for the REASON the child cursors got created.

The ROLL_INVALID_MISMATCH column had a value of Y.

SQL >select SQL_ID, CHILD_NUMBER, ROLL_INVALID_MISMATCH   
     from v$sql_shared_cursor where sql_id='12wm4h2j0a2rp';

SQL_ID        CHILD_NUMBER R
------------- ------------ -
12wm4h2j0a2rp            0 N
12wm4h2j0a2rp            1 Y
12wm4h2j0a2rp            2 Y
...
12wm4h2j0a2rp           14 Y
...
52 rows selected.

The REASON column contained also the following message: ‘Rolling Invalidate Window Exceeded‘

This reason made me curious, as I have never heard of it before.

ROLL_INVALID_MISMATCH has to do with cursor invalidation, after gathering statistics on objects that are referenced by the cursor.

Cursor invalidation behaviour was different prior to Oracle 10g, compared to newer releases.
Prior to 10g, cursors in the shared pool were invalidated immediately after gathering stats on the referenced objects. This mass invalidation could have caused performance problems, due to hard parsing.

Starting with 10g cursor invalidation was/is handled differently. Cursors are market for rolling invalidation.
This means that you gather statistics on the tables that are referenced by the cursor. Next time the query executes for this cursor, Oracle will generate a random number between 0 and the value of _optimizer_invalidation_period parameter (which by default is set to 18,000 seconds).

The cursor will stay valid for the randomly generated number of seconds. After that the cursor gets invalidated, and a new child cursor is created.

This totally made sense. The database I was investigating the problem in, was gathering statistics on these objects every day.

No wonder then that these child cursors were created every day.

SQL>select SQL_ID,CHILD_NUMBER,
   
to_char(LAST_ACTIVE_TIME,' YY-MON-DD HH24:MI:SS') as LAST_ACTIVE_DATE 
    from gv$sql where sql_id='12wm4h2j0a2rp' 
    order by 3;

SQL_ID        CHILD_NUMBER LAST_ACTIVE_DATE
------------- ------------ -------------------
12wm4h2j0a2rp            0  18-AUG-20 03:00:38
12wm4h2j0a2rp            1  18-AUG-21 00:00:45
...
12wm4h2j0a2rp            9  18-AUG-31 11:00:35
12wm4h2j0a2rp           10  18-SEP-01 18:09:01
12wm4h2j0a2rp           11  18-SEP-02 14:00:37
12wm4h2j0a2rp           12  18-SEP-03 14:00:36
12wm4h2j0a2rp           13  18-SEP-04 14:00:39
12wm4h2j0a2rp           14  18-SEP-04 17:00:37

52 rows selected.

Another thing I’d like to point out is the fact that this was not a performance problem after all.
There was no corrective action to take for this behavior.