Why Does a Single Oracle SQL Generate Multiple Child Cursors? Understanding Rolling Invalidation

Problem Encountered

While conducting performance testing, a SQL statement that uses bind variables was observed to have version_count = 2 in V$SQLAREA, indicating two child cursors. Both child cursors displayed identical execution plans, yet the ROLL_INVALID_MISMATCH flag differed (N for child 0, Y for child 1).

Root Cause: Rolling Cursor Invalidation

The behavior is explained by Oracle’s Rolling Invalidate Window mechanism, described in Oracle document 557661.1. Prior to Oracle 10g, gathering statistics with DBMS_STATS (unless no_invalidate is set to TRUE) invalidated all cached cursors, causing a “hard parse storm”. Starting with 10g, the no_invalidate parameter defaults to AUTO_INVALIDATE, allowing Oracle to stagger invalidations and reduce the storm.

DBMS_STATS no_invalidate Options

TRUE

: does not invalidate dependent cursors. FALSE: immediately invalidates dependent cursors. AUTO_INVALIDATE (default): Oracle decides when to invalidate.

Rolling Invalidation Process (Post‑10g)

When DBMS_STATS gathers statistics, all dependent cursors are marked with a rolling invalidation timestamp T0.

On the next parse of a marked cursor, Oracle assigns a random timestamp Tmax within the range defined by _optimizer_invalidation_period (default 18000 seconds ≈ 5 hours). The cursor is still reused; no hard parse occurs.

Each subsequent use checks the current time T2 against Tmax. If T2 exceeds Tmax, the cursor is invalidated, a new child cursor is created with an updated execution plan, and the new cursor’s ROLL_INVALID_MISMATCH flag is set to Y.

This sequence matches the observed situation where two child cursors exist with identical plans but different ROLL_INVALID_MISMATCH values.

Reproducing the Scenario

To observe the rolling invalidation behavior, the following steps can be performed in an Oracle 11g environment:

Set the invalidation period to a short interval for easier observation:

alter system set "_optimizer_invalidation_period"=60;

Create a test table and gather statistics:

create table X as select * from dba_tables;

exec dbms_stats.gather_table_stats(null,'X');

Execute the target SQL and inspect V$SQL_SHARED_CURSOR to see the initial child cursor.

Execute the same SQL again after the short interval; a second child cursor appears, and the ROLL_INVALID_MISMATCH flag changes to Y.

Additional MOS Scenarios

If a cursor is marked for rolling invalidation but never parsed again, it will eventually be evicted by LRU without explicit invalidation.

If a marked cursor is parsed only once more, it remains valid (timestamp only) and may later be evicted by LRU.

Frequently used cursors that exceed Tmax become invalid on the next parse, triggering a new child cursor.

Conclusion

The presence of multiple child cursors for the same SQL with identical execution plans is caused by Oracle’s rolling invalidation mechanism. The ROLL_INVALID_MISMATCH flag indicates whether a cursor has passed its invalidation window and been refreshed with updated statistics. Understanding this process helps DBAs diagnose unexpected hard parses and tune the _optimizer_invalidation_period parameter accordingly.