Oracle 26ai EXPLAIN PLAN: Module Review and Next Steps

1. Know what EXPLAIN PLAN can establish

The introductory lesson separated explanation from execution. EXPLAIN PLAN records the optimizer's estimated operations for a statement in the explaining environment. It does not execute the target SELECT, return its application rows or measure its elapsed time. A separate display query formats the recorded operations.

This distinction remains important after a query has run. Explaining the same text afterward generates another estimate; it does not recover the plan used by the earlier request. Schema resolution, bind types and values, statistics and optimizer settings can differ between an application and your diagnostic session.

Use an estimate to understand candidate operations and formulate questions. Use the relevant cursor plan and collected execution statistics when investigating actual work. If the required cursor has left the cache or statistics were not collected, state that limitation instead of presenting a new estimate as historical evidence.

2. Preserve the meaning of declarative SQL

3. Use the plan table for its intended purpose

The PLAN_TABLE lesson distinguished three components. EXPLAIN PLAN generates estimated operation records, PLAN_TABLE stores those records, and DBMS_XPLAN.DISPLAY formats them. The table does not generate the plan or execute the application query.

Oracle normally supplies the default temporary plan-table infrastructure and synonym. The definition is shared, but the temporary rows are session-private. Keep generation and display in the same connection. Two editor tabs can use different sessions even when both show the same account name.

A schema-local PLAN_TABLE can override the public synonym. If output is missing or incompatible, check name resolution, permissions, successful generation and the statement label before recreating objects. Creating a custom output table with the version-appropriate supplied script is an optional workflow, not a mandatory first step for every learner.

Use fresh statement labels for separate experiments. A STATEMENT_ID is your label for plan-table records; it is not Oracle's SQL_ID for a cached statement. Keep the SQL text and relevant environment details with saved output so that comparisons remain understandable later.

4. Generate, display and read an estimate

The EXPLAIN PLAN statement lesson made the sequence explicit. This review example assumes that the optional HR schema is installed and your account has appropriate access. It is instructional SQL, not a report of executed tests.

EXPLAIN PLAN SET STATEMENT_ID = 'M5_REVIEW_01' FOR
SELECT e.employee_id, e.last_name, d.department_name
FROM hr.employees e
JOIN hr.departments d
  ON d.department_id = e.department_id
WHERE e.department_id = 50;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY(
  'PLAN_TABLE', 'M5_REVIEW_01', 'TYPICAL +ALIAS +PREDICATE'
));

The second SELECT returns formatted plan text, not employee data. No fixed plan is promised: indexes, statistics and data distribution influence the optimizer. A different access path in your training database is not necessarily an error.

Read indentation as parent-child structure. Children supply rows to parents, and a join combines its inputs into another row source. Operation IDs identify nodes and connect them to predicates; they are not a universal execution timeline. Reading every line bottom-to-top or in ascending ID order cannot describe all repeated, parallel or adaptive processing.

Read estimated rows, objects, predicates and notes together. Cost is an optimizer comparison value, not elapsed seconds. Estimated Time is also not a stopwatch measurement. Parent costs can include child work, so adding every displayed cost does not establish total duration.

5. Evaluate full-table scans in context

The full-table scan lesson began with recognition: locate TABLE ACCESS FULL and identify the object. Then ask whether reading that data is appropriate for the requested result. A small table, broad report or aggregate can reasonably use a full scan.

For a conventional heap table, a completed full scan reads the relevant formatted blocks below the segment's high-water mark. Predicate evaluation determines which rows are returned. Reading blocks is different from returning every row, and it is not a separate scan of each column. Partition pruning can further limit which segments are accessed.

Investigate an unexpected scan by checking the predicates, datatypes, available indexes, statistics and actual workload. An index is not automatically preferable when it leads to many scattered table lookups. Conversely, a missing selective condition or an unintended conversion may create unnecessary work worth addressing.

Distinguish logical buffer activity from physical reads and elapsed time. Data can already be cached, and competing workload can affect response time. Do not flush production caches simply to make a comparison look controlled. State the test conditions and use measurements appropriate to the application.

6. Separate join order, join method and access path

The join-diagnosis lesson treated these as related but distinct decisions. Join order determines which row sources are combined. Join method describes how they are combined. Access paths determine how their underlying rows are obtained.

Nested loops can efficiently drive selective inner lookups, but many outer rows can cause extensive repeated work. Hash joins build and probe a hash structure and may suit larger inputs. Merge joins combine ordered streams, potentially requiring sorts. No method is universally fastest, and the inner side of nested loops is not guaranteed to use an index.

Check the join predicates and row counts before applying a hint. Unexpected multiplication can arise from an incorrect relationship or nonunique matches. Large estimate errors can also influence the selected strategy. A USE_HASH hint is not a general cure for a slow nested-loops plan, and a supplied hint is not proof that Oracle used it.

When relevant, inspect hint and adaptive-plan information rather than inferring behavior from SQL comments or a single operator name. Keep changes focused: verify result correctness, collect comparable evidence and evaluate the complete statement rather than one attractive-looking branch.

7. Distinguish subquery meaning from execution order

The subquery lesson defined correlation by references. A correlated subquery refers to a column from an outer query block; a non-correlated subquery does not. Those definitions do not prescribe how many physical executions or scans Oracle performs.

Eligible subqueries can be unnested into join strategies. Membership queries may use semijoins, which preserve qualifying outer rows without multiplying them for additional inner matches. Non-correlated subqueries are not restricted to NOT IN: they can appear in positive membership, scalar comparisons and other valid contexts.

Negation requires particular care. A NULL returned by a NOT IN subquery can prevent otherwise expected rows from qualifying through SQL's three-valued logic. NOT EXISTS tests for the absence of matching rows, but it is not a mechanical replacement without reviewing the intended treatment of nullable values.

Also describe dictionary queries accurately. A user without a matching role-grant record is not thereby proven to have direct object privileges or no effective privileges. Keep database/container context and the meaning of the queried views separate from claims about performance.

8. Interpret index range scans and table lookups together

The index range scan lesson explained ordered traversal of a B-tree interval. A range scan can return zero, one or many entries. Equality on a nonunique key can use this operation, so it is not limited to numeric inequalities or BETWEEN predicates.

For BETWEEN, both endpoints are inclusive. For a composite index, examine key order and how access and filter conditions are used. A selective final result can still require examining many candidates. When required columns are absent from the index, associated table access adds work that must be included in the diagnosis.

TABLE ACCESS BY INDEX ROWID BATCHED describes table retrieval, not a separate kind of index range scan. INDEX UNIQUE SCAN, FULL SCAN, FAST FULL SCAN and SKIP SCAN describe different strategies. Learn their roles instead of treating all index operations as equivalent evidence of efficiency.

An ordered index path may avoid a sort when it satisfies the query's requirements, but ORDER BY does not force that access path. Nullable keys, direction, expressions and intervening operations matter. Preserve an explicit ORDER BY whenever the application requires ordered results; index order alone is not a result-order contract.

9. Reduce complex plans to understandable branches

The complex-plan lesson combined four actions: locate the statement, follow the tree, read predicates and check execution evidence. Describe each important branch in plain language before deciding that a particular operation needs replacement.

Access predicates help locate or match rows; filter predicates reject candidates. Match their IDs to the operation tree and inspect where selective conditions take effect. Filters are normal, and the word access does not necessarily indicate an index. Aliases help connect transformed plans to the original SQL.

Dictionary views can expand into many internal operations. Parallel distribution, partition access and adaptive alternatives can add further layers. A long display is not inherently slow, and a compact display does not reduce execution work. Retain enough detail to identify expensive child operations and repeated starts.

Connect the plan to runtime evidence

In an authorized test, collect row-source statistics during execution, for example with the statement-level GATHER_PLAN_STATISTICS hint. Fetch the intended results, identify the correct SQL ID and child cursor, then request the collected statistics. This SQL*Plus or SQLcl template expects an unquoted SQL ID and a numeric child number at its prompts:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  '&target_sql_id', &target_child_number,
  'ALLSTATS LAST +ALIAS +PREDICATE'
));

Appropriate performance-view privileges are required. ALLSTATS LAST displays available statistics for the selected child's last execution; it does not collect missing statistics retrospectively. Confirm that the selected execution and fetch behavior match the investigation. Another execution can change which run is represented by “last.”

Compare E-Rows and A-Rows in the context of Starts. An inner operation estimated to return one row per start may correctly return 100 rows across 100 starts. A-Rows describes output, not necessarily every entry examined. Buffers reflects logical activity, not a count of distinct blocks or physical disk reads.

Do not indiscriminately sum plan-line times or buffer counts because parent and child statistics can overlap. Look for substantial differences between estimated and observed row flow, then investigate their effect on later work. A lower estimated cost is a reason to test a change, not evidence that application response time improved.

A repeatable investigation checklist

Key terms to retain

Check your understanding before continuing

Explain why a full scan can be appropriate, why an index range scan is not proof of good performance, and why a join can change the multiplicity of an IN query. Then describe the difference between a statement label in PLAN_TABLE and a SQL ID used to inspect a cursor.

Finally, consider an operation with many starts and few rows per start. What additional evidence would you need before calling it inexpensive? Your answer should account for accumulated work, fetch completeness and representative inputs rather than estimated cost alone.

The next module introduces SQL optimizer modes. Carry this discipline forward: understand the required result, distinguish estimates from measurements, and validate changes in the context of the application.

Explain Plan - Quiz

Oracle 26ai references

• Explaining and displaying execution plans
• DBMS_XPLAN package reference
• Optimizer access paths and join methods