Lesson 6	Diagnosing table joins
Objective	Use Oracle 26ai execution plans to identify join order, methods and predicates, then validate suspected inefficiencies with collected execution statistics.

Diagnose Table Joins in Oracle AI Database 26ai

To diagnose a table join, identify the row sources being combined, how Oracle accesses them, and how much work each operation performs. EXPLAIN PLAN provides an estimated execution plan. DBMS_XPLAN.DISPLAY_CURSOR lets you inspect a cached cursor and, when statistics were collected during execution, compare estimates with measured row-source activity. These views answer related but different questions: what the optimizer expected and what the selected execution actually did.

A join is not inefficient simply because it uses a full scan, a hash join, or a different table order from the SQL text. Start with query correctness and the workload's performance requirement. Then use plan evidence to locate excessive repeated access, unexpectedly large intermediate results, or expensive sorting and work-area activity. The goal is to explain the work before choosing a change.

Separate join order, join method and access path

A join combines two row sources into another row source. Either input may itself be the result of an earlier join, a view, or another operation. The join order describes how these combinations are arranged. The join method, such as nested loops or a hash join, describes how a combination is performed. The access path describes how rows are obtained from an object, for example through a full table scan or an index followed by table access.

Join type is a separate property of the requested result. Inner joins return matching combinations; outer joins also preserve specified unmatched rows. Semi and anti joins implement existence or nonexistence relationships in applicable plans. Changing a method must preserve those semantics. Moving an outer-join predicate between ON and WHERE, for example, can change which rows survive.

In Oracle 26ai, do not arrange tables from largest to smallest in the FROM clause as a tuning rule. The cost-based optimizer considers estimated row counts after filtering, available access paths, statistics, transformations and legal join orders. A table with millions of stored rows may contribute only a few qualifying rows. Conversely, a small table may participate in many result combinations.

For a conventional nested-loop join, the outer input supplies rows used to access the inner input. This outer input is often called the driving row source^[1]. It is not necessarily the physically smallest table. Hints such as ORDERED or LEADING can constrain eligible join orders when applicable, but they belong in controlled investigations rather than an automatic prescription.

Generate an estimated join plan

The following training query uses the optional HR sample schema. It requires access to HR.EMPLOYEES and HR.DEPARTMENTS; those objects are not installed in every database. It returns employees above the chosen salary threshold together with their matching department names. The SQL is an instructional example, not a benchmark or a record of execution against your database.

EXPLAIN PLAN SET STATEMENT_ID = 'L6_JOIN_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.salary > 12000;

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

Run the explanation and display in the same session. Use a fresh statement label for each comparison so earlier results do not become confused with the current test. Oracle normally supplies the default PLAN_TABLE infrastructure; recreating it is not a routine prerequisite. The generated plan depends on the database's objects, statistics, settings and optimizer environment.

EXPLAIN PLAN does not execute the target SELECT. The displayed rows, bytes, cost and time are estimates. An estimated TempSpc value is not evidence that a particular execution spilled to disk. The plan selected for the actual application cursor may differ, especially when bind values, session settings or other parsing conditions differ.

Follow the row-source tree

Read indentation to establish parent and child relationships. Locate the join operation, then follow the operations supplying its inputs. A join operation commonly has an empty Name cell. Identify its tables and indexes through child operations and object aliases rather than looking for a single row listing both table names.

This is a simplified, hypothetical nested-loop shape. It is not captured output from the preceding SQL, and the actual optimizer may choose another method or order.

SELECT STATEMENT
  NESTED LOOPS
    outer input: filtered EMPLOYEES rows
    inner input: matching DEPARTMENTS rows

In this simple serial example, employee rows supply department keys for inner access. More outer rows generally mean more opportunities for repeated inner work. That does not imply one physical disk read per outer row: caching, batching, index/table operations and other implementation details affect the work. Real plans can contain additional nested-loop operations, filters and intermediate row sources.

Do not extend this small tree into a universal rule that the top child is always the smallest input or that every join appears as two adjacent table rows. Parallel execution, adaptive branches and transformed query blocks require reading their surrounding operations and notes. A plan is a tree of operations, not a chronological list that can always be understood by reading line numbers from top to bottom.

Recognize the join methods

Questions to ask when reviewing join operations
Operation	Behavior	Diagnostic focus
`NESTED LOOPS`	Uses outer rows to access an inner row source.	Outer cardinality, inner starts, and the cost of repeated inner access.
`HASH JOIN`	Builds a hash structure from one input and probes it with the other.	Input volumes, estimate accuracy, memory use and measured spill evidence.
`MERGE JOIN`	Combines suitably ordered inputs.	Whether sorting is needed and how much actual sort work occurs.
`MERGE JOIN CARTESIAN`	Forms combinations between its inputs.	Query intent, join predicates and the size of the intermediate product.

Nested loops can work well when a selective outer input can find matching inner rows efficiently. If the optimizer expects a few outer rows but receives many, repeated inner access may dominate. Investigate the first cardinality mismatch and the inner access path together. A missing or unsuitable index is one possibility, not a diagnosis established merely by seeing nested loops.

Hash joins often suit substantial equijoin inputs. The optimizer generally favors a smaller build input, but estimated and actual sizes can differ. Memory availability and workload also influence whether the operation can complete in memory. Neither HASH JOIN RIGHT OUTER nor a swapped-input outline entry proves that the join is inefficient. Confirm work-area behavior using collected runtime evidence.

Merge joins require suitably ordered inputs. A SORT JOIN may provide that order, but an existing ordered access path can sometimes avoid an explicit sort. Do not assume both inputs must always be sorted from scratch or that every inequality predicate forces this method. Inspect the actual plan and the resource use of its sorts.

A Cartesian operation deserves attention because its combinations can multiply rapidly. Check for omitted join conditions, but also consider an intentional cross join or an efficient intermediate combination of two small inputs that both connect to another table. The presence of CARTESIAN is a reason to investigate query intent and cardinality, not sufficient evidence of an error.

Read predicates and diagnostic reports

Match each entry in Predicate Information to its operation ID. For the training query, distinguish the department-key equality from the salary filter. An access predicate participates in locating or matching rows at an operation; a filter predicate excludes rows according to a condition. Their placement helps explain the chosen approach.

A filter on a join operation is not automatically a defect. Expressions or implicit datatype conversions can affect access opportunities, but the consequence depends on the expression, available indexes and transformations. Inspect the condition and its actual work before recommending a rewrite. Removing a conversion without understanding the underlying data may change results or expose invalid values.

The SQL Analysis Report can identify candidate issues such as an expensive Cartesian product or a predicate that limits an access opportunity. Its messages are conditional, and their absence does not prove that a query is optimal. Use the report alongside the SQL, predicates and execution evidence. The format options above request aliases, predicates and analysis information without requiring a long optimizer outline.

Collect runtime statistics for the selected cursor

To investigate actual work, execute a controlled test with row-source statistics collection enabled. The following query adds the GATHER_PLAN_STATISTICS hint and a recognizable comment to the same training query. Fetch all rows when comparing complete executions. A client that stops after its first result-grid page may leave incomplete work statistics.

SELECT /*+ GATHER_PLAN_STATISTICS */ /* L6_JOIN_RUNTIME */
       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.salary > 12000;

Instrumentation has overhead, so use a representative diagnostic execution rather than enabling detailed statistics indiscriminately for the entire system. Identify the statement's SQL_ID and CHILD_NUMBER through your client's cursor information or an authorized performance-view query. This lookup can help locate the marked statement:

SELECT sql_id, child_number, plan_hash_value, last_active_time
FROM v$sql
WHERE sql_text LIKE
      'SELECT /*+ GATHER_PLAN_STATISTICS */ /* L6_JOIN_RUNTIME */%'
ORDER BY last_active_time DESC;

Multiple children or executions may match. Confirm the SQL text, execution context and relevant child instead of blindly selecting the first row. An intervening client query can make the default last-statement display point to something else. Explicit cursor selection avoids that ambiguity, although you must still identify the execution you intended to inspect.

The following SQL*Plus or SQLcl template prompts for the verified SQL ID and numeric child number. It does not supply an existing cursor automatically. The SQL ID is quoted; the child number is not.

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

ALLSTATS LAST displays collected statistics for the last execution of the selected cursor. It does not collect them retroactively. In a shared workload, another execution may affect what “last” refers to. Missing runtime columns do not mean zero work. Confirm statistics collection, cursor selection and result consumption before drawing conclusions.

DISPLAY_CURSOR requires appropriate SELECT or READ privileges on its documented fixed views, including V$SQL, V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL and V$SESSION. Ask for suitable access through your DBA. Connecting as SYS is not a prerequisite for this lesson.

Compare estimates with actual work

Review E-Rows, A-Rows and Starts together. Estimates may be expressed per start while actual rows accumulate across starts. For a repeatedly started operation, compare actual rows per start, where appropriate, rather than treating its total as a single invocation. Do not divide by zero, and interpret parallel or adaptive statistics in their execution context.

For example, suppose an illustrative inner operation has an estimate of one row per start, starts 100 times, and returns 100 rows in total. That can agree with the per-start estimate; it is not automatically a hundredfold estimation error. Conversely, an outer input that produces far more rows than expected can multiply inner work even when each individual inner lookup is efficient.

Inspect buffers, available timing and work-area statistics alongside row counts. Parent figures can include child work, so adding every plan line may double-count activity. An estimated temporary-space requirement does not establish a measured spill. Compare equivalent bind values, result consumption and workload conditions, and distinguish first-row response time from completing the entire result.

Investigate the earliest material mismatch. Missing or stale statistics, skew, correlated predicates, expression selectivity and bind values are possible causes. Histograms and extended statistics solve different problems. Column-group statistics describe relationships among columns of one table; they are not a universal solution for cross-table correlation. A SQL plan baseline manages plan choice rather than directly repairing an inaccurate estimate.

Use adaptive and hint information carefully

When an adaptive plan exists, +ADAPTIVE can show alternatives and inactive operations. Read the displayed notes and legend to determine which path was used. Do not assume every query has adaptive branches, or that a compile-time explanation proves the final runtime choice. This is another reason to inspect the relevant executed cursor.

If you test join-order or join-method hints, inspect the Hint Report and resulting plan. Its status codes include U for unused, N for unresolved, and E for syntax error. A conflict is not automatically a syntax error. A hint in the SQL text is also not proof that the optimizer applied it. Preserve the unhinted baseline so a controlled comparison can establish whether the change helped.

A repeatable join-diagnosis checklist

Confirm correctness. Check join conditions, duplicate behavior, datatype compatibility and outer-join preservation before optimizing the result.
Map the plan. Identify each join's inputs, method and access paths using indentation, aliases and predicates.
Measure the selected execution. Collect statistics, fetch the intended result, identify the correct cursor and account for repeated starts.
Form a specific hypothesis. Locate unexpected cardinality, repeated access, sorting or work-area activity. Do not prescribe an index solely because a scan appears.
Test one justified change. Consider relevant statistics, SQL or datatype corrections, or an access-path improvement. Account for index storage and DML-maintenance costs.
Compare results and resources. Verify equivalent output and compare measured work under representative conditions. A lower estimated cost alone does not prove improvement.

The next lesson examines non-correlated subqueries. Apply the same evidence-first approach: SQL syntax alone does not establish whether a subquery runs once, whether it is transformed into a join, or whether rewriting it will improve performance.

[1] Driving row source: In the nested-loop discussion here, the outer input whose rows supply values for accessing the inner input. It may be a filtered table or the output of earlier operations.

Oracle documentation