cswork / libraryOpen practice workspace

Articles

Oracle Execution Plan Interview Questions: Estimated Rows, Actual Rows, and Bind Variables

Practice Oracle execution plan interview questions with E-Rows, A-Rows, Starts, DISPLAY_CURSOR, bind selectivity, and evidence-led tuning decisions.

An Oracle execution plan interview often turns on one mistake: treating the plan you explained as proof of the work the database actually performed. A useful answer identifies the executed cursor, compares estimates with runtime rows at the correct scope, and explains how bind values could change the best access path.

Official facts below come from Oracle's SQL Tuning Guide and package reference. Candidate reports: none are used to claim a particular employer asks these questions. Original practice: the tenant query, plan numbers, and diagnostic choices are invented exercises. They illustrate reasoning, not a benchmark or a captured production plan. Rehearse this distinction with Optimize a SQL query plan tree.

Why can EXPLAIN PLAN be misleading?

EXPLAIN PLAN describes a plan produced for explanation; it does not execute the statement and measure its runtime row sources. Oracle documents circumstances where an explained plan can differ from the executed plan. Use it to discuss an intended access path, then seek the actual cursor when diagnosing a real execution. Oracle's execution-plan guide.

An interview answer might be: “This is the optimizer's estimate under the explain context. Before concluding that production uses this plan, I would inspect the relevant SQL ID and child cursor, its predicates, and collected runtime statistics.” That establishes what the evidence can support.

Do not assume a changed plan proves a changed query. Session settings, parsing context, statistics, and cursor selection can matter. Conversely, the same apparent plan shape does not guarantee the same amount of work. A nested-loops operation receiving ten outer rows behaves differently from one receiving a hundred thousand.

Plan cost is an optimizer estimate used to compare alternatives under its model. It is not measured milliseconds or a percentage of wall-clock time. A low-cost plan can run slowly when its cardinality assumptions are wrong or its runtime environment differs.

How do you capture the executed cursor?

Official package behavior: DBMS_XPLAN.DISPLAY_CURSOR displays a cached cursor's plan; runtime statistics require that they were collected. ALLSTATS LAST requests the available statistics for the last execution, while an explicit SQL ID and child number avoid accidentally displaying an unrelated statement. DBMS_XPLAN reference.

This laboratory outline assumes appropriate privileges and a SQL client that supports bind variables. The query must be executed and fetched to the extent you intend to measure. Do not infer full-query work from a client that fetched only its first page.

VAR tenant_id NUMBER
EXEC :tenant_id := 42;

SELECT /*+ gather_plan_statistics */ /* interview_orders */
       order_id, amount
FROM orders
WHERE tenant_id = :tenant_id
  AND status = 'OPEN';

-- After identifying the query's actual SQL ID and child cursor:
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  'replace_with_sql_id',
  0, -- replace with the observed child number
  'ALLSTATS LAST +PREDICATE +PEEKED_BINDS'
));

This is a collection pattern, not executable end-to-end setup: the table, index, data, and observed cursor identifiers must exist in your test environment. The gathering hint has overhead; use approved diagnostic procedures rather than enabling broad production collection casually.

If runtime columns are absent, first check collection and privileges. Missing statistics do not mean zero work. If Starts is zero for an operation, ask whether that branch was executed in the observed run; do not divide by zero or treat an unused branch as a measured bottleneck.

Record SQL ID, child number, bind class, execution scope, and fetch behavior alongside the output. LAST refers to the cursor's recorded last execution, so a busy shared cursor needs careful correlation with the execution you intended to diagnose.

Exercise: where does the estimate first go wrong?

Suppose the following simplified synthetic row-source extract comes from a fully fetched serial execution. It adds a customer-key lookup to the earlier orders-only demonstration; it is not output from that snippet. It omits elapsed time, buffers, predicates, and other necessary diagnostics to keep the cardinality question focused.

Operation                         Starts  E-Rows  A-Rows
NESTED LOOPS                            1      10  100000
 TABLE ACCESS BY INDEX ROWID ORDERS      1      10  100000
  INDEX RANGE SCAN ORDERS_TENANT_I       1      10  100000
 INDEX UNIQUE SCAN CUSTOMERS_PK     100000       1  100000

The tenant index scan estimates ten rows and produces a hundred thousand. That is the earliest visible cardinality divergence in this extract. The parent operations inherit the larger input. The customer lookup produces a hundred thousand rows overall, but it starts a hundred thousand times.

Official statistic scope: Oracle's runtime views distinguish operation starts from output rows for the last execution. The output-row count covers work across those starts. Oracle's runtime statistics reference.

Our arithmetic: for the customer lookup, 100000 / 100000 = 1 output row per start. Against the one-row estimate, that is consistent. Comparing A-Rows = 100000 directly with E-Rows = 1 would falsely identify a hundred-thousand-fold error at the inner lookup.

For the tenant scan, Starts = 1, so actual output per start remains a hundred thousand versus ten estimated. Its estimate mismatch is ten thousandfold. That is a hypothesis about why the chosen join strategy may be expensive, not proof that a hash join is always better.

Ask for buffers and timing before selecting the intervention. The unique lookup may be accurately estimated yet dominate work because it repeats so often. Accuracy and expense are different questions.

What should you inspect after a row mismatch?

Start with the predicate and data distribution, not the operator name. Is the tenant unusually large? Does status = 'OPEN' correlate with tenant? Are functions or implicit conversions changing selectivity or access? Are statistics representative of the data being queried?

Observation in the exerciseEvidence to request next
Tenant scan returns far more rows than estimatedActual tenant distribution and relevant statistics for that predicate.
Individual predicates appear plausible, combined estimate does notEvidence of correlation between tenant and status.
Index exists but access is unexpectedly broadPredicate section, column types, index leading columns, and residual filtering.
Inner lookup is accurate but repeated heavilyBuffer work and elapsed evidence correlated with outer-row count.
Similar executions vary considerablyBind classes, child cursors, fetch behavior, cache conditions, and concurrent load.

A strong answer separates observation from inference: “The estimate is wrong here. Skew or correlation could explain it. I would check those before changing statistics or indexing.” A weak answer says “stale statistics” without evidence and immediately requests a global refresh.

A statistics change can alter other plans. An index can increase write and storage costs. A rewrite can change semantics. Describe the smallest experiment capable of distinguishing your hypotheses and the regression cases that would protect correctness.

Why do bind variables complicate the answer?

Official behavior: Oracle can peek at a bind value during hard parsing. The resulting plan may not suit every value when data is skewed. Adaptive cursor sharing allows a statement with binds to use multiple plans; bind-sensitive and bind-aware state can be inspected through documented views. Oracle's cursor-sharing guide.

For the fictional orders query, tenant 42 might return ten open orders while tenant 900 returns a hundred thousand. An index-led plan can be appropriate for one class and expensive for the other. The SQL text's stability does not remove the selectivity difference.

Do not say Oracle “reoptimizes for every bind.” Nor should you promise adaptive cursor sharing will immediately solve every skewed workload. Explain what you would observe: child cursor identity, bind-sensitive or bind-aware flags, relevant distributions, and performance across representative executions.

PEEKED_BINDS describes values associated with parsing when available. It is not a universal audit log of each execution's actual bind values. Collect execution context through an appropriate permitted mechanism, and avoid exposing private parameter values in diagnostic material.

Multiple child cursors do not by themselves prove adaptive cursor sharing is the cause. Identify why cursors differ rather than assigning all versioning to one feature. A plan hash value is useful context, but does not replace cursor details and measured work.

Should you force an index or replace the binds?

Treat both as proposed interventions requiring evidence. “Force the index” may improve a selective tenant and harm a large one. Replacing every bind with literals can affect cursor reuse and parsing workload; it is not a general response to skew.

A defensible experiment uses at least a small and a large tenant, the same result contract, and comparable execution conditions. Capture returned-row counts, buffer work, elapsed distribution, and write implications where relevant. If an index includes additional columns, explain which lookups it removes and what maintenance it adds.

Consider whether the query needs every returned row. A product requirement for a paged interface differs from a fully exported report. Do not “optimize” an export by silently fetching fewer rows. If you change pagination or ordering, document the changed contract and evaluate the resulting query separately.

If the root problem is correlated predicates, investigate statistics that represent the relationship under your version and workload. If the problem is an invalid type conversion, repair the type contract. If the workload genuinely has distinct selectivity classes, evaluate the engine's cursor behavior before imposing one plan on all of them.

Build a fair before-and-after comparison

Use a short evidence packet rather than two unrelated screenshots. It should contain the same SQL contract, test data class, bind class, cursor identity, collection method, and fetch behavior for each run. State whether caches were warm and whether concurrent workload changed.

One dramatic run is not a stable latency result. Repeat enough comparable executions to understand variation, while respecting the cost of the test. If the concern is p95 latency, a single average does not answer it. If the concern is total export time, explain client and network time separately from database work.

Do not add operation times mechanically to calculate query duration. Parent and child accounting can overlap, and parallel work complicates elapsed interpretation. Use runtime views and the relevant tool semantics rather than treating the plan as a list of independent stopwatches.

Your closing answer should make a conditional recommendation: “I would test the change across both tenant classes and retain it only if the correctness and performance evidence support it. I would monitor regressions rather than treating a lower displayed cost as success.”

Practice the diagnostic, not a memorized operator list

Reusing a few general plan drills is intentional: apply Oracle cursor and Starts semantics to the follow-ups here rather than importing another engine's terminology.

Complete practice questionOracle-focused follow-up
Optimize a SQL query plan treeLocate the earliest estimate divergence in an executed cursor.
Explain Database Index Benefits and Write CostsTest both selective and broad bind classes.
Optimize SQL to minimize scansDecide whether a scan is appropriate for the actual result size.
Defend a Project's Schema, Indexes, and Concurrency ModelConnect a tuning change to operational costs.
Reason About Composite Join Keys and Predicate PlacementCheck whether the query's selectivity assumption matches its key contract.

Start with Optimize a SQL query plan tree, then explain why the synthetic customer lookup above is accurately estimated despite its large total A-Rows.

Sources and Further Reading