Oracle DBA Lesson 7C — Compare V$SQL Observations for Cursor Reuse

After this lesson, you can identify a cached executable child, compare two observations of its activity, and select a useful next investigation.

Oracle DBA Lesson 7C — Compare V$SQL Observations for Cursor Reuse

From a repeated lookup to evidence

An application retrieves an employee's salary using a fixed statement with a changing bind value. An administrator wants to understand how that statement was used. Oracle's library cache holds prepared executable code in shared-pool RAM. The V$SQL dynamic performance view exposes current statistics about those cached children.

A parent identifies the SQL text. Its children carry executable versions. V$SQL has one row per child. Several children can belong to the same parent, and sessions using one child contribute to its shared counters. These observations describe current cache contents. Employee data persists in physical data files on disk. The buffer cache holds working data blocks in RAM. See V$SQL.

Find the lookup

Assume Oracle Database 19c, one instance, and the intended practice pluggable database. APP_USER is a replaceable application-schema identity. The schema owns an authorized EMPLOYEES table with employee_id and salary columns. Employee identifiers 101 and 102 are example inputs. Verify that they exist in your practice data.

In the application's SQL*Plus session:

VARIABLE emp_id NUMBER
EXEC :emp_id := 101;
SELECT salary FROM employees WHERE employee_id = :emp_id;

VARIABLE declares a client bind with the NUMBER type. EXEC runs a short PL/SQL assignment. The SELECT uses the supplied identifier. Keep this SELECT on one line as shown when using the exact-text observation below. Its terminating semicolon is a SQL*Plus delimiter and is omitted from the comparison string.

In a separate authorized observer session in the same container:

SELECT con_id, sql_id, child_number,
       executions, parse_calls, invalidations
FROM v$sql
WHERE parsing_schema_name = 'APP_USER'
  AND sql_text = 'SELECT salary FROM employees WHERE employee_id = :emp_id'
ORDER BY con_id, sql_id, child_number;

The selected columns serve these purposes:

Column	Interpretation
CON_ID	Container scope of the row. Preserve it with the observation.
SQL_ID	Identifier for the parent SQL statement.
CHILD_NUMBER	Executable child within that parent.
EXECUTIONS	Executions counted since the object entered the library cache. A run can be partial or fail. Completed fetching has a separate counter.
PARSE_CALLS	Parse requests counted for this child. A request may reuse compatible preparation or need new preparation.
INVALIDATIONS	Times this child was invalidated, making its existing executable state unsuitable.

The schema condition selects the context used to parse the statement. It does not restrict the counters to one user's current session. The text condition locates this specific short statement. Matching is sensitive to case, spacing, and comments. SQL_TEXT exposes at most the first 1,000 characters. Longer statements require an appropriate SQL_FULLTEXT or identity-based inspection.

An empty result calls for checking the connected container, observer visibility, actual submitted text and parsing schema, whether the statement executed, and whether its child remains cached. If several children appear, preserve every row and follow the child or children whose counters changed.

Compare two observations

The following numbers are a hypothetical example, not captured database output. Assume both samples refer to the same container, SQL ID, and child, with a continuous cache lifetime and a controlled workload.

Measurement	Before	After	After - before
EXECUTIONS	20	22	+2
PARSE_CALLS	12	14	+2
INVALIDATIONS	0	0	0

Two additional executions and two additional parse requests were recorded for that child. A parse request can take the soft-parse path by reusing suitable code or the hard-parse path by preparing new code. Classifying the two parse requests requires additional evidence.

A relevant dependent-object change, such as a table-definition change, can invalidate prepared code. The unchanged invalidation counter here records no invalidation during the observed interval. It does not measure every other possible reason for preparation or cache loading. Invalidation timing also depends on the operation and its immediate or deferred behavior. See cursor sharing.

Respect cache lifetime and shared activity

Counter subtraction is meaningful only after establishing comparable identity and lifetime. Entries can age out, and executable portions can reload. Keep snapshot times and matching CON_ID, SQL_ID, and CHILD_NUMBER. For a stronger practice record, also capture CHILD_ADDRESS, LOADS, and LAST_LOAD_TIME. LOADS reports loads and reloads. Address and load-time observations help investigate discontinuities. FIRST_LOAD_TIME is the parent creation timestamp and should be interpreted accordingly.

If a row disappears, changes identity, reloads, or resets its counters, preserve that condition and establish a new baseline. Address reuse and activity between samples limit what two snapshots alone can establish. Several sessions may contribute during the interval, so isolate the practice workload and record concurrent activity. Statistics normally update after query execution, with periodic updates for long-running work. Take the after sample once the two short lookups have completed.

Choose the next investigation

Observed pattern	Focused next check
Several parent texts differing only in literal values	Inspect the SQL submitted by the application. Consider whether changing inputs can be supplied as binds for this workload.
Several children for one parent	Inspect their compatibility reasons and execution conditions. Different bind metadata, resolved objects, or optimizer environments may explain legitimate versions.
More parse calls	Collect controlled parse evidence for the application session over the same interval to distinguish preparation from reuse.
Changed or missing cache entry	Investigate continuity and visibility, then establish a fresh comparable baseline.

With separately authorized access to SYS.V_$SQL_SHARED_CURSOR, this optional template investigates compatibility flags. Replace both placeholders with values captured from your observation before execution:

SELECT con_id, sql_id, child_number,
       bind_mismatch, optimizer_mismatch, translation_mismatch
FROM v$sql_shared_cursor
WHERE sql_id = '<OBSERVED_SQL_ID>'
  AND con_id = <OBSERVED_CON_ID>
ORDER BY child_number;

A Y flag records the corresponding sharing reason for the child. Bind information, optimizer environment, and resolved-object identity are useful starting points. The view has many additional reasons. Preserve the actual flags and surrounding context before deciding on a change. Multiple children can use the same plan. See V$SQL_SHARED_CURSOR.

For an optional controlled parse investigation, an authorized observer can sample V$SESSTAT joined to V$STATNAME for the application session's parse count (total) and parse count (hard). Confirm the session identity, including the SID, serial number, and container, at both samples. These are session-wide statistics. Assignments, other statements, recursive work, and concurrent activity can contribute. Interpret their deltas in a controlled interval. Attributing hard parsing to one particular SQL statement may require a focused trace investigation in a later lesson. Avoid changing the cache to manufacture a result. See V$SESSTAT and V$STATNAME.

V$SQLAREA gives parent-level aggregation, including execution and parse totals across children and VERSION_COUNT for cached children. Choose the view according to whether the question concerns one executable child or its parent's aggregate. See V$SQLAREA.

Practice

Use an instructor-provided disposable practice database and scoped accounts. The application needs access to its own example table. The observer needs CREATE SESSION and explicitly authorized read access to SYS.V_$SQL. Optional extensions require their separate view grants. An administrator supplies grants. This exercise does not provision accounts or broaden privileges.

1. Confirm each session's user and connected container with SHOW USER and SHOW CON_NAME. Record the release and RU, instance, actual schema, and sample times.
2. Declare the numeric bind, assign an existing employee identifier, and submit the exact one-line lookup to establish its cache entry.
3. Take and retain the before observer snapshot. Record the matching child identity and, where available, its address and load information.
4. In the application session, assign an identifier and execute the identical lookup twice, fetching each result. Assignment statements are separate statements. Observe the selected salary lookup. Different values are allowed while the SQL text stays fixed.
5. Take the after snapshot. Compare matching continuously cached children, calculate the observed deltas, and explain their scope. Record empty rows, unchanged counters, different children, or reloads as observed conditions.
6. Choose one focused next check and explain the evidence that justifies it.

Success means interpreting the actual observations, including their limits. The expected +2 example is a teaching assumption. Your database can show different behavior. No live database execution was performed to produce this lesson's examples. The workload is read-only, so no data cleanup is needed. Preserve the observations and disconnect when finished. Cache flushing, parameter changes, DDL, memory resizing, and management-pack tools are outside this exercise.

Related shared-pool awareness

The optional server result cache stores eligible query or PL/SQL function results in shared-pool memory. Prepared-code reuse and reuse of a stored result serve different jobs. Configuration and eligibility determine result-cache use.

The shared-pool reserved area supports certain larger allocations. ORA-04031 reports a failed shared-memory allocation. Preserve the complete error, requested size, named pool or heap, and workload context. Investigate the actual allocation problem before selecting remediation. Detailed sizing and allocation diagnosis belong to later performance work. See shared pool and large pool and ORA-04031.

Recap

Identify one cached executable child, compare two observations only when the container, SQL ID, child number, and cache lifetime match, and choose the next check from the pattern you recorded. A rise in parse calls does not by itself establish hard parsing. If the observed child disappears or reloads, preserve that discontinuity and establish a fresh baseline.

Quiz

1. What does one V$SQL row represent?

Every statement executed since database creation One executable child of a parent statement Every child combined into one parent total One employee data block

2. Executions change from 20 to 22 for the same continuously cached child. What was counted?

Two additional executions Two new parent texts Two invalidations Two complete database backups

3. PARSE_CALLS rises by two. Did two hard parses occur?

Yes, every parse call is hard parsing Yes, if the invalidation counter stayed zero Not established; parse requests include reuse and new preparation No, every bind statement always uses soft parsing

4. The observed child disappears and a later row appears after a reload. What should you do?

Subtract any later counter from the old value Treat SQL ID alone as proof of continuous lifetime Flush the cache before continuing Preserve the discontinuity and establish a fresh comparable baseline

5. Several children exist under one parent. What is a useful next check?

Assume every child indicates a fault Investigate compatibility reasons in V$SQL_SHARED_CURSOR Replace all bind values with literals immediately Increase memory solely from the child count

The comparison numbers in this lesson are a hypothetical example, not captured database output. No live database execution was performed to produce them. Run the practice on the instructor-provided disposable database, confirm the release, and record what you actually observe.

AIOps Pro

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