LEARN. PRACTICE. GROW.Identify exactly what executed
An APS incident includes two attachments: EXPLAIN PLAN collected by a developer and a high application response time. The first describes an estimated plan in the context where it was produced; it does not establish the path or rows processed by the slow execution. Seek SQL_ID, child_number, PDB, relevant values and the affected execution interval. In the lab every query has an identifying comment and is located by exact text and schema. Collection confirms one executed child before calling DISPLAY_CURSOR. This discipline avoids accidentally displaying the diagnostic query’s own plan. In production, cursor retention and available privileges can limit what you can recover.
Collect counters with explicit scope
ALLSTATS LAST requests statistics from the last execution, but does not retrospectively create counters that were never collected. The lab runs queries with gather_plan_statistics, completely consumes the aggregate row and displays the identified cursor. Synthetic users receive SELECT on the four fixed views needed for diagnosis; they do not receive SYSDBA. The separate EXPLAIN PLAN presents estimated Rows without A-Rows. If an application only consumes part of the results, or another execution replaces the relevant observation, qualify the evidence. Do not fill missing actual values from estimates. Record how instrumentation was enabled and assess its cost before expanding collection across an actual service.
Read the operation, starts and window
In the rare query, table access produces 100 rows in one start. After repeating the exact SQL, the view shows two executions, two cumulative starts and 200 cumulative output rows; last-execution fields still show one start and 100 rows. Comparing cumulative 200 with a single-execution estimate would create a false conclusion. For restarted operations, relate A-Rows to Starts before assessing E-Rows; an average per start can help but can also hide variation between iterations. Do not indiscriminately add times or buffers from every plan line as though they were independent costs. Keep the tree and each counter’s semantics in the diagnostic record.
Relate predicates and access paths
The plan includes the operation and predicate locating or filtering rows. In the rare example with a histogram, the index locates STATE=REVIEW and the table supplies AMOUNT for the sum. The existing index contains only STATE; observing ROWID access does not establish an index-use failure. In the frequent query, full access produces 9,900 rows and the result remains correct. Analyze selectivity, projection and observed work before proposing another index or a hint. For a composite index, column order and available predicates matter; paths such as skip scan exist, so absolute rules without context can mislead decisions. This lab does not execute all those paths.
Deliver diagnosis that supports a decision
Prepare a summary for APS and development: business impact, identified cursor, observed discrepancy, hypothesis, bounded change and rollback or acceptance criteria. Cost is an internal optimizer estimate; it is not milliseconds and does not directly compare arbitrary queries as an SLA table. Both lab runs passed 36 checks without concurrent load, AWR or SQL Tuning Advisor. Logs demonstrate the described sequence, not production capacity. For a release, add representative load, authorized real parameters, reconciled results and service metrics. Preserve the distinction between documentation, observation and hypothesis. Diagnosis ends when evidence supports the agreed decision, not when the plan looks visually simpler.
-- Execute and fully fetch the target query with statistics collection enabled.
SELECT /*+ gather_plan_statistics */ SUM(amount) FROM payments WHERE state='REVIEW'
-- Obtain the intended SQL_ID and child_number before displaying its evidence.
SELECT plan_table_output FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR('<captured_sql_id>',0,'ALLSTATS LAST +PREDICATE'));
-- Replace both cursor identifiers with the captured values, not another session's defaults.After two executions: OUTPUT_ROWS=200 and STARTS=2; LAST_OUTPUT_ROWS=100 and LAST_STARTS=1.
Common pitfalls
EXPLAIN as actual execution; ALLSTATS as retrospective collection; comparing cumulative values with one execution; Cost as SLA time.
Related topics: Optimizer statistics · Performance diagnosis · Operational change acceptance
Identify the cursor, collect appropriate counters and connect each conclusion to the scope actually supported by evidence.
Reference: DBMS_XPLAN · 1Z0-183 public objectives inspected 2026-09-30; revision date not published