Oracle DBA Lesson 7B — Inspect Cursor Reuse Without Guessing

Oracle DBA · Lesson 7B

Inspect Cursor Reuse Without Guessing

Performance can suggest reuse. Cursor evidence establishes it. Use SQL_ID, child_number, and the right V$SQL counters — and read each counter for what it actually measures.

Watch on YouTube

Oracle DBA Lesson 7B — Inspect Cursor Reuse Without Guessing

AI Ops Pro

Performance is not proof

A fast response time is not proof that a cursor was reused. To establish reuse you need identity and activity together:

• SQL_ID — parent statement identity (same statement text).
• CHILD_NUMBER — compatible executable variant under that parent.
• EXECUTIONS / PARSE_CALLS / INVALIDATIONS — activity on that child.

Parent identity and compatible children

One parent (SQL_ID) can have several children. Child 0 and child 1 are compatible variants of the same parent text. Extra children often trace to differences in optimizer settings, object state, or bind behavior — not automatically to a fault.

V$SQL is a current cache snapshot

SELECT sql_id, child_number, executions,
       parse_calls, invalidations
FROM   v$sql
WHERE  parsing_schema_name = 'COURSE_OWNER'
  AND  sql_text LIKE 'SELECT employee_name ... :emp_id%'
ORDER  BY sql_id, child_number;

Different text or restricted visibility can return no row. Treat the result as a snapshot of what is in the cache now — not a lifetime audit log. Never invent live sample numbers; read them from your instance.

Read each counter for what it measures

• EXECUTIONS — child executions.
• PARSE_CALLS — all parse requests (includes reusable soft parses).
• INVALIDATIONS — cursor made unusable.

PARSE_CALLS is not the same thing as hard parses. A rise in parse_calls can still be soft reuse. Remember: parse request ≠ hard parse.

The shape of the cache points to the next question

• Several parents (for example employee_id = 101, 102, 103 as separate texts) → check application text (literals vs binds).
• One parent, several children (SQL_ID → child 0 / child 1) → investigate why variants exist (settings, objects, binds).

Do not flush the shared pool or add memory by reflex. Preserve the snapshot, then investigate the cause.

Gotchas

• Response time alone does not prove cursor reuse.
• PARSE_CALLS includes soft parses — a bump does not prove hard parses.
• Several near-identical parents usually means literal SQL; one parent with many children is a different problem.
• Read identity (SQL_ID / CHILD_NUMBER) and activity together before diagnosing.

Quick quiz

Choose one answer per question, then submit. You’ll see the correct answer and a short why.

1. A statement finishes in 12 ms. Does that prove cursor reuse?

Yes — sub-20 ms always means soft parse reuse Yes, if the table is small No — response time suggests; cursor evidence establishes Only inside a CDB root session

2. What does SQL_ID identify in this lesson’s model?

A single hard-parse event timestamp Parent statement identity (same statement text) Only the PGA workarea size The redo strand that wrote the commit

3. PARSE_CALLS increased by 2. Does that prove two hard parses?

No — PARSE_CALLS includes reusable soft parses Yes — every parse_call is a hard parse Yes, but only for SELECT statements Only if INVALIDATIONS is zero

4. V$SQL shows several parents that differ only by employee_id literals. Best next move?

Flush the shared pool immediately Double SHARED_POOL_SIZE with no inspection Ignore it; parents always multiply Check application text (literals vs binds); preserve snapshot, then investigate

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro