EXPLAIN PLAN  «Prev  Next»

Lesson 9Analyzing complex execution plans
ObjectiveAnalyze a complex Oracle 26ai execution plan by interpreting its operation tree, predicates and available runtime statistics.

Analyze Complex Execution Plans in Oracle AI Database 26ai

A long execution plan becomes understandable when you separate it into branches and explain what each branch contributes. Begin with the SQL statement and its intended result, identify how the plan obtains and combines rows, then use execution evidence to investigate expensive work. Counting operations or looking for a particular join method is not enough to establish performance.

Oracle 26ai uses cost-based optimization to choose among eligible plans. This lesson focuses on interpreting those choices, rather than the historical rule-based optimizer or an obsolete graphical tool. The same method applies whether you inspect formatted text in SQLcl or SQL*Plus, or a graphical plan in a database development tool.

A four-step method for reading a plan

The diagram summarizes the process. Its small hash-join branch is an instructional illustration, not captured execution output or a prediction that every similar SQL statement will use this plan.

Four steps for analyzing an Oracle 26ai execution plan: locate the statement, follow parent and child operations, match predicates to operation IDs, and check collected execution statistics.
  1. Locate the statement: distinguish an estimated plan displayed with DBMS_XPLAN.DISPLAY from the relevant executed cursor identified by SQL ID and child number and displayed with DBMS_XPLAN.DISPLAY_CURSOR.
  2. Follow the tree: identify parent and child operations, access paths and join inputs. In this illustrative branch, the two table-access operations supply rows to a hash join.
  3. Read predicates: match each predicate's operation ID to the tree. Here operation 1 carries the join condition and operation 2 carries the employee salary filter.
  4. Check execution evidence: compare estimated and actual rows while considering starts and buffer activity. ALLSTATS LAST displays statistics collected during execution; it does not collect missing statistics retrospectively. Confirm the correct cursor and fetch the intended results.

Cost and estimated rows describe optimizer estimates. Runtime evidence helps assess performance. This diagram is an instructional illustration; no SQL was executed to produce it.

1. Locate the statement and identify the evidence

Before interpreting an operation, establish which statement and environment the plan describes. Record the SQL text, relevant bind values, schema and database context. For a cached cursor, record the SQL ID and child number. Different child cursors can have different plans, and an application's environment can differ from the session used for a manual EXPLAIN PLAN.

An estimated plan describes the optimizer's proposed operations for the explained statement. EXPLAIN PLAN stores plan rows without executing the target SELECT. DBMS_XPLAN.DISPLAY formats those rows. Estimated Rows, Bytes, Cost and Time are not measurements of a completed execution.

A cached cursor plan describes a cursor loaded in the database. DBMS_XPLAN.DISPLAY_CURSOR can show that plan and runtime statistics when those statistics were collected. Merely displaying a cursor does not guarantee that actual row-source statistics are available, or that its last execution represents the slow application request you are investigating.

Generate a labeled estimate

The following example requires the optional HR sample schema and authorized access to its tables. It retrieves employees earning more than 10,000 together with their department names. The commands are instructional and have not been executed for this lesson.

EXPLAIN PLAN SET STATEMENT_ID = 'L9_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 > 10000;

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

Oracle normally provides the default plan-table infrastructure. Explain and display in the same session, and use a fresh statement label for each comparison. The explicit label prevents accidentally selecting another statement's plan. Do not expect the sample query to produce the diagram's exact operations: data volume, indexes, statistics and optimizer settings influence the choice.

2. Follow parent and child operations

A plan is a tree of row sources. Child operations supply rows to their parent; indentation communicates those relationships. Operation IDs identify plan lines and connect them to predicates, but they are not a universal chronological execution sequence. Do not read a complex plan as a numbered script that executes once from top to bottom.

The diagram uses this simplified shape:

0  SELECT STATEMENT
1    HASH JOIN
2      TABLE ACCESS FULL  EMPLOYEES
3      TABLE ACCESS FULL  DEPARTMENTS

Operations 2 and 3 are children of operation 1. They provide employee and department rows to a hash join. Operation 1 supplies joined rows toward the statement result. For a conventional hash join, Oracle builds a hash structure from one input and probes it with the other. The complete plan and execution context determine the details; the drawing alone does not establish memory usage, spills or elapsed time.

A useful branch description is: “Read qualifying employees, obtain department rows, and join the two streams by department ID.” This describes the work without pretending that a compact view changes how the query executes. For a larger plan, write one such sentence for each important branch, then connect the sentences at their common parent.

Recognize operations without labeling them automatically good or bad

  • Table and index access: identify where rows originate. An index operation may be followed by table access to obtain additional columns. Evaluate that combined work, not the index line alone.
  • Nested loops: use outer rows to drive inner work. This can be efficient for selective lookups, but many repeated inner starts can accumulate substantial cost.
  • Hash joins: combine inputs through hashing. Consider row volume and available work-area memory when investigating expensive execution.
  • Merge joins: combine appropriately ordered inputs. Determine whether a sort supplies that order or an access path already provides it.
  • Sorts and aggregations: organize or reduce rows. Check the amount of incoming data and any collected memory or temporary-space evidence before concluding that they are bottlenecks.

A full table scan can be the appropriate choice, especially when much of a table is needed. Conversely, an index range scan can be expensive when it produces many scattered table lookups. A long plan is not necessarily slow, and a short plan is not necessarily efficient.

Handle additional layers in a complex plan

Views, subqueries and optimizer transformations can make the physical plan look different from the written SQL. Use object aliases and query-block information to connect branches to their source expressions. A subquery may be transformed into a join; its location in the SQL text does not dictate a separate execution step for every outer row.

For partitioned access, examine the partition operations and start/stop information. For parallel plans, account for distribution and coordinator operations rather than interpreting every line as serial work. If the plan includes adaptive alternatives, read its notes and, when needed, request an adaptive display. A branch shown in an adaptive plan is not proof that it performed work during the execution under review.

3. Connect predicates to the tree

The predicate section explains conditions applied at specific operations. In the diagram, the illustrated predicate information is:

1  access(e.department_id = d.department_id)
2  filter(e.salary > 10000)

Operation 1 uses the equality to match employee and department rows. Operation 2 rejects employees that do not satisfy the salary condition before supplying its output to the join. These IDs apply only to the illustrative tree; use the IDs in your own plan when analyzing a real statement.

Access predicates help locate or match rows through an access operation or join. The word access does not necessarily mean an index lookup. Filter predicates reject candidate rows before an operation returns them. A filter is not automatically evidence of a problem.

Ask where each selective condition takes effect. If many rows are retrieved from a table and most are rejected afterward, investigate whether earlier filtering is possible without changing the result. With an index, distinguish conditions that guide access from those checked after candidate entries or table rows have been obtained.

Inspect functions and implicit conversions carefully. They can affect index eligibility and estimates, but removing them without understanding datatypes can change semantics. Outer joins, null handling and duplicate rows also constrain safe rewrites. A faster query that returns a different answer is not a successful optimization.

4. Compare estimates with collected execution statistics

To investigate actual work, collect row-source statistics during an authorized test execution. A statement-level GATHER_PLAN_STATISTICS hint is one method:

SELECT /*+ GATHER_PLAN_STATISTICS */ /* L9_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 > 10000;

Fetch the intended results, then identify the SQL ID and child number for this test statement through your client or authorized cursor views. Confirm the SQL text and execution context rather than selecting an arbitrary cursor with similar text. The extra comments make this test statement distinct from the application's original SQL.

Use the verified identifiers in the following SQL*Plus or SQLcl substitution template. Enter the SQL ID without surrounding quotes and a numeric child number when prompted:

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

DISPLAY_CURSOR requires appropriate access to the underlying dynamic performance views. Use authorized privileges rather than assuming a routine lesson requires a SYS login. ALLSTATS LAST displays collected statistics for the selected child's last execution; it does not reconstruct missing statistics. A cached plan is not a complete historical record, and another execution can change what “last” refers to.

Evidence to compare at relevant plan operations
FieldInterpretation
E-RowsOptimizer estimate of rows produced by an operation. Compare it with actual output while accounting for starts.
A-RowsActual rows produced in the displayed statistics. This is not necessarily every row or index entry examined internally.
StartsHow often the operation started. Repeated inner work can be costly even when each start returns few rows.
BuffersLogical buffer activity. It is not the same as physical disk reads or a count of distinct blocks.
A-Time and memory detailsCollected execution evidence when available. Read its scope carefully; parent and child statistics can overlap.

For example, an inner operation estimated to return one row per start can correctly produce 100 actual rows across 100 starts. Comparing one directly with 100 would falsely suggest a large estimate error. Inspect starts and the operation's role before interpreting a discrepancy.

Look for the earliest important divergence between estimates and observed row flow, then follow its consequences downstream. Underestimated input can lead to much more repeated lookup work than expected. Large intermediate results can also increase join, sort or aggregation work even when the final result is small.

Do not add all plan-line times or buffer counts indiscriminately because parent statistics can include child work. Record elapsed time separately under comparable conditions. A partial fetch, different bind values, concurrent load or different cache state can make two otherwise similar tests incomparable.

Why a short dictionary query can have a long plan

A query against data dictionary views can expand into many underlying operations. The complexity reflects the view definitions and transformations, not necessarily a complicated application requirement. This is why an old plan for internal dictionary objects should not be treated as a stable Oracle 26ai example.

The earlier lesson example sought users without matching role-grant records. Its intent can be expressed explicitly with NOT EXISTS:

SELECT u.username
FROM dba_users u
WHERE NOT EXISTS (
  SELECT 1
  FROM dba_role_privs r
  WHERE r.grantee = u.username
);

This requires authorized dictionary-view access and must be interpreted in the relevant database/container context. It identifies users without a matching role-grant row in the queried view. It does not prove that they have direct object privileges, have no effective privileges, or cannot access data through other mechanisms.

The general difference between NOT IN and NOT EXISTS matters when nullable values are involved; do not replace one mechanically without checking the relevant definitions and intended result. Neither spelling guarantees a particular join method or faster execution. See the subquery lesson for the distinction between logical dependency and physical execution.

Turn observations into a testable diagnosis

  1. Describe the symptom: specify the slow statement, representative inputs and required result.
  2. Identify the evidence: distinguish an estimate from a verified cursor and collected execution statistics.
  3. Explain the branches: identify row sources, join inputs, predicates and any repeated work.
  4. Form a hypothesis: connect a concrete observation to a possible cause, such as an estimate error, many table lookups or excessive intermediate rows.
  5. Test a focused change: preserve semantics and compare results and measured work before adopting it.

Do not begin by forcing an index, changing global optimizer parameters or gathering statistics indiscriminately. First establish what is wrong and whether the proposed change addresses it. Graphical tools can help navigate large plans, but a collapsed display must not hide predicates, repeated starts or expensive child operations.

The next lesson, the EXPLAIN PLAN module conclusion, brings these techniques together: obtain the right evidence, understand how rows flow through the plan, and validate performance with representative execution measurements.

Oracle 26ai documentation


SEMrush Software 9 SEMrush Banner 9