Parse, Bind, Execute, Fetch; Library Cache, Shared SQL Areas, and Child Cursors

Learning outcomes

A ServiceHub API endpoint issues what developers believe is “the same query” thousands of times, yet the shared pool shows several SQL IDs and multiple child cursors. One team calls this “Oracle's plan cache being fragmented.” That phrase hides the actual mechanism. Oracle has session cursor state, parent SQL identities, child executable cursors, library-cache objects, optimizer environments, bind metadata, and result-fetch state. This lesson follows one statement through those layers.

1. Four application-visible phases: parse, bind, execute, fetch

Parse identifies the SQL statement, checks syntax/semantics, resolves referenced objects and privileges, and determines whether an existing shared cursor can be reused. A hard parse includes optimization and row-source generation; a soft parse reuses existing executable code after compatibility checks.

Bind associates externally supplied values with placeholders such as :status_code. Bind values are data; they are not textual SQL substitution.

Execute starts the row-source tree. For DML, this is where row changes and associated locking occur. For a query, execution establishes the work needed to produce rows.

Fetch transfers query rows to the client in one or more calls. Client arraysize/fetch size changes round trips and fetch behavior without changing the SQL text itself.

2. The shared pool is broader than SQL plans

The shared pool is part of the System Global Area (SGA). Its library cache contains executable/shared representations of SQL and PL/SQL plus related metadata. The shared SQL area holds shared information for a SQL statement, including parsed representation and execution-plan information. A session still has its own private/session cursor state that points to a shared child cursor.

A useful mental model is:

• Parent cursor: identifies a SQL statement in the library cache and groups executable child cursors for that statement.
• Child cursor: an executable version compatible with a particular set of bind metadata, optimizer environment, object state, authorization/environment conditions, and other sharing criteria.
• Session cursor: the session's private handle/state for executing and fetching from a child cursor.

One parent can therefore have multiple children without any corruption. The diagnostic question is whether those children are justified and stable.

3. SQL_ID identifies the parent statement; CHILD_NUMBER distinguishes executable children

V$SQL exposes one row per child cursor. V$SQLAREA summarizes statistics for the parent statement and reports values such as VERSION_COUNT. A single SQL ID can therefore appear on multiple V$SQL rows with different child numbers or plan hashes.

SELECT    sql_id,    child_number,    plan_hash_value,    executions,    parse_calls,    loads,    invalidations,    is_shareable,    is_bind_sensitive,    is_bind_aware,    substr(sql_text,1,100) AS sql_textFROM v$sqlWHERE sql_text LIKE 'SELECT /* sh10_l1 */%'ORDER BY sql_id, child_number;SELECT    sql_id,    version_count,    executions,    parse_calls,    loads,    invalidations,    sharable_mem,    substr(sql_text,1,100) AS sql_textFROM v$sqlareaWHERE sql_text LIKE 'SELECT /* sh10_l1 */%'ORDER BY sql_id;

V$SQLAREA aggregation is convenient for a parent-level overview; use V$SQL when child-level differences matter. These views are current-memory evidence: cursors can age out, be invalidated, or be reloaded.

4. Textual identity and child compatibility are different sharing gates

With CURSOR_SHARING=EXACT, Oracle expects SQL text to match for parent sharing. Adding a comment, changing literal text, or otherwise changing the SQL text can create another parent SQL identity even when the business result is equivalent. Conversely, identical SQL text can map to several children when the existing child is not compatible with the new execution environment.

SELECT /* sh10_l1 */ work_order_idFROM servicehub_cursor_work_orderWHERE status_code = :status_code;SELECT /* sh10_l1_variant */ work_order_idFROM servicehub_cursor_work_orderWHERE status_code = :status_code;

Do not standardize SQL text merely to make a dashboard show fewer SQL IDs. Standardization is valuable because it improves reuse and observability, but correctness, meaningful instrumentation tags, and driver behavior still matter.

5. Deliberately wrong: call every extra child “shared-pool fragmentation”

A second child can be created for a legitimate compatibility reason: optimizer-mode mismatch, bind metadata mismatch, statistics invalidation, authorization differences, Adaptive Cursor Sharing, or many other reasons. The supported diagnostic path is to inspect the children and, when version proliferation matters, query V$SQL_SHARED_CURSOR for mismatch flags.

SELECT    sql_id,    child_number,    plan_hash_value,    executions,    is_bind_sensitive,    is_bind_aware,    optimizer_env_hash_valueFROM v$sqlWHERE sql_text LIKE 'SELECT /* sh10_l1 */%'ORDER BY child_number;

Flushing the shared pool erases useful evidence and forces unrelated SQL to hard parse again. It is not a root-cause fix.

6. Hands-on lab: execute one bound statement repeatedly and inspect reuse

Run setup as SERVICEHUB_OWNER in the disposable course PDB. Use one SQLcl/SQL*Plus session for execution and a DBA/observer session for the V$ queries if the owner does not have fixed-view privileges.

DROP TABLE servicehub_cursor_work_order IF EXISTS PURGE;CREATE TABLE servicehub_cursor_work_order (    work_order_id NUMBER PRIMARY KEY,    status_code   VARCHAR2(12) NOT NULL,    amount        NUMBER(10,2) NOT NULL);INSERT INTO servicehub_cursor_work_orderSELECT    LEVEL,    CASE MOD(LEVEL,3)      WHEN 0 THEN 'OPEN'      WHEN 1 THEN 'CLOSED'      ELSE 'HOLD'    END,    MOD(LEVEL*17,10000)/10FROM dualCONNECT BY LEVEL <= 3000;CREATE INDEX sh10_l1_status_ixON servicehub_cursor_work_order(status_code);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(      USER,'SERVICEHUB_CURSOR_WORK_ORDER',cascade=>TRUE  );END;/

VARIABLE status_code VARCHAR2(12)EXEC :status_code := 'OPEN';SELECT /* sh10_l1 */ COUNT(*)FROM servicehub_cursor_work_orderWHERE status_code = :status_code;EXEC :status_code := 'CLOSED';SELECT /* sh10_l1 */ COUNT(*)FROM servicehub_cursor_work_orderWHERE status_code = :status_code;EXEC :status_code := 'HOLD';SELECT /* sh10_l1 */ COUNT(*)FROM servicehub_cursor_work_orderWHERE status_code = :status_code;

SELECT    sql_id,    child_number,    executions,    parse_calls,    plan_hash_value,    is_bind_sensitive,    is_bind_awareFROM v$sqlWHERE sql_text LIKE 'SELECT /* sh10_l1 */ COUNT%'ORDER BY child_number;

Expected evidence is repeated executions of the same tagged statement, usually with far fewer executable children than executions. Do not require exactly one child: bind peeking, environment, statistics, or other compatibility conditions can legitimately produce more.

DROP TABLE servicehub_cursor_work_order PURGE;

7. Production judgment

Instrument SQL so you can find it, but keep application SQL text stable where semantics are stable. Use bind variables for data. Investigate child-cursor growth through supported views rather than cache folklore. Treat SQL_ID as a statement identifier and CHILD_NUMBER as an executable-version discriminator—not as application business identifiers.

No restart, parameter change, paid option, management pack, or COMPATIBLE change is required. Lesson 2 now measures why literal-heavy SQL and repeated hard parsing cost CPU/concurrency, and why correct binding improves both reuse and injection resistance.

Authoritative references

• Improving Real-World Performance Through Cursor Sharing — cursor lifecycle, parent/child sharing and bind guidance
• V$SQL — child-cursor statistics and bind-sensitive/bind-aware flags
• V$SQLAREA — parent-level SQL-area statistics including version count
• Tuning the Shared Pool and the Large Pool — library cache/shared-cursor reuse
• Cursors Overview — session/private cursor concepts