Using EXPLAIN PLAN in Oracle AI Database 26ai

A SQL query describes the result you want, but it does not normally prescribe every operation used to produce that result. Oracle can scan a table, retrieve rows through an index, combine row sources, and sort intermediate results. Before changing a slow query, understand the work its plan proposes. EXPLAIN PLAN provides a practical starting point for that investigation.

In Oracle AI Database 26ai, EXPLAIN PLAN asks the optimizer to generate a plan for a statement and record its operations in a plan table. You then use DBMS_XPLAN.DISPLAY to format those records. The target query is not executed, so the display describes an estimated plan, not a measurement of completed work. This distinction connects to the previous lesson: optimization prepares an execution strategy; execution and fetching perform the work needed to return results.

The statement belongs to Oracle SQL. Submit it through a connected SQL client, such as SQL*Plus, rather than directly at an operating-system prompt. A graphical management tool can make plan inspection convenient, but it is not required. Understanding the command-based workflow helps you recognize what a tool is showing and ask useful questions about its output.

The example uses the optional HR.EMPLOYEES sample table. It must exist in your database, and your account must have permission to query it. Oracle installations do not necessarily include the HR sample schema. If it is unavailable, use a suitable training database or ask the administrator for the intended practice objects. An object-not-found error does not necessarily mean that EXPLAIN PLAN is broken.

Run the steps in the same database session. Modern Oracle supplies the default PLAN_TABLE through a synonym for a system-created global temporary table, with session-private data. You normally do not need to create the table yourself. If a local object named PLAN_TABLE shadows the supplied synonym, or an older custom plan table causes problems, investigate the object and its definition with the administrator before changing it.

Your account needs the privileges required to explain the target statement and to use the selected plan table. A custom table can have different access requirements. Routine practice does not require connecting as SYSDBA. Use the account and database service intended for the exercise so that object resolution and permissions are predictable.

These commands adjust SQL*Plus output width and page length. They are client presentation settings, not SQL statements executed by the database. Another client may provide equivalent display preferences.

SET LINESIZE 160
SET PAGESIZE 100

A statement identifier distinguishes this explanation from other plans stored in the same session. Use M2L5_DEPT50 for this example. Before repeating it, remove only earlier plan records carrying that label:

DELETE FROM plan_table
WHERE statement_id = 'M2L5_DEPT50';

This cleanup changes plan-table records, not employee data. It avoids leaving several demonstrations under the same label and makes the selected result easier to identify. To preserve an earlier explanation, choose another label instead and use it consistently in both the explanation and display commands.

Do not add an unconditional COMMIT merely to view the plan. Your session can read its own changes, and a commit can also commit unrelated work. Similarly, an automatic rollback could undo unrelated work. Manage the transaction according to your practice environment rather than treating either command as a required display step.

This query requests employee identifiers and last names for department 50, ordered by last name. The complete statement includes its selected columns, source table, filtering condition, and ordering requirement:

EXPLAIN PLAN SET STATEMENT_ID = 'M2L5_DEPT50' FOR
SELECT employee_id, last_name
FROM hr.employees
WHERE department_id = 50
ORDER BY last_name;

The SQL following FOR is the target statement. Oracle checks and optimizes it, then inserts records describing proposed operations into PLAN_TABLE. It does not return matching employees at this stage. An explanation-success message means that plan generation succeeded, not that the target query ran successfully or met a performance goal.

Keep the statement complete when copying it into a client. A SELECT list without its FROM clause cannot explain how a table is accessed. Avoid copying client prompts or line numbers into the SQL. These are instructional examples, not commands accompanied by claimed output from a database execution.

The plan depends on available indexes, table and column statistics, and other optimizer inputs. Your result may use a full table scan or an index-based path. Do not add an index or hint simply to make your display resemble someone else's. First understand why the optimizer chose the available strategy for your data.

Use Oracle's formatting package to display the records:

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'M2L5_DEPT50', 'TYPICAL')
);

The first argument identifies the plan table. The second selects the statement label used above. The third requests the typical display format. The TABLE expression lets SQL query rows returned by the table function. This query reads the explanation records; it does not execute the employee query.

An explicit label makes the example easier to repeat and review than relying on whichever explanation happens to be most recent. Keep the same connection open between generation and display. Another worksheet may use a different session and therefore different session-private records.

DBMS_XPLAN preserves context that a simple query of operation names can omit: indentation, predicate details, and applicable notes. Handwritten hierarchical queries can support specialized analysis, but introduce unnecessary complexity into this basic workflow.

TABLE ACCESS FULL describes a full table scan. It can be appropriate for a small table or a query needing a substantial part of the data. INDEX RANGE SCAN describes access to a range of index entries. When the index does not supply all requested columns, TABLE ACCESS BY INDEX ROWID can retrieve corresponding table rows.

SORT ORDER BY describes sorting to satisfy an ordering requirement. The query contains ORDER BY, but that does not guarantee a separate sort: an access path can sometimes provide the required order. Conversely, an index on a filtering column does not automatically satisfy ordering by another column.

For queries involving multiple tables, operations such as NESTED LOOPS and HASH JOIN describe different ways to combine row sources. The single-table example does not require a join. Recognize these names for later lessons without expecting every plan to contain them or assuming one join method is universally best.

Indentation expresses parent-child relationships. A parent consumes rows produced by its children, but the display is not a stopwatch timeline. Some operations request rows repeatedly, others pass rows along as they become available, and blocking operations may need substantial input before returning output. Parallel execution can further complicate timing relationships.

Do not interpret operation IDs as an execution counter or apply a universal bottom-to-top reading rule. Identify each operation's inputs and output. In a nested loops join, for example, the inner input can be visited repeatedly for rows from the outer input. The tree describes dependencies more usefully than a single linear ordering.

Avoid adding all displayed costs together to produce a supposed query duration. Costs reflect the optimizer's model and can represent work across an operation's subtree. Use estimates to understand choices within a relevant comparison, then measurements to evaluate application performance.

EXPLAIN PLAN helps you learn a statement's likely structure or examine a proposed rewrite before executing it. For an application query that is already slow, inspect the intended cached cursor as well. A separately generated explanation may not match the plan used by that execution.

Identify the intended SQL_ID and child cursor before using DISPLAY_CURSOR. Different child cursors can exist for a statement. Displaying an unrelated or merely recent cursor can lead to the wrong diagnosis. Cached-cursor inspection does not require retrieving a historical plan from AWR; these are different evidence sources.

The ALLSTATS LAST display option requests available statistics for the last execution of the selected cursor. It does not retrospectively collect measurements. Statistics must have been collected during execution, for example with a deliberately scoped gather_plan_statistics hint. Use the controlled approach introduced in the SQL tuning tool set lesson rather than enabling broad diagnostics merely to fill display columns.

For a SELECT, consider how many results the application fetched. Measuring a query that fetches only the first screen of rows can answer a different question from measuring complete result consumption. Match the measurement to the user's performance goal and record that context with the plan.