EXPLAIN PLAN   «Prev  Next»

Lesson 3Oracle PLAN_TABLE
ObjectiveUse the default PLAN_TABLE and explain when to create a schema-local output table in Oracle AI Database 26ai.

Using PLAN_TABLE in Oracle AI Database 26ai

PLAN_TABLE holds the operation records produced by EXPLAIN PLAN. In a normally configured Oracle AI Database 26ai database, you can use the supplied table infrastructure without first running a creation script. Creating your own output table is an optional setup choice for a particular workflow, not the first step every SQL developer must perform.

This distinction matters because older instructions often begin with utlxplan.sql and an Oracle7 connection transcript. Those instructions describe a manual setup path, but they are not a reliable description of the normal 26ai starting point. Begin by checking what your session can access, then explain a small statement and display its plan.

Three components with different responsibilities

A plan is a tree of operations, such as a table scan, index lookup, join or sort. The output table stores a record for each operation. These records describe how Oracle estimates it would process the statement; they are not the customer, employee or sales rows returned by the application query.

  1. EXPLAIN PLAN asks the optimizer to generate an estimated plan and writes its operation records into an existing output table.
  2. PLAN_TABLE is the default destination for those records. It does not generate the plan or execute the application query.
  3. DBMS_XPLAN.DISPLAY reads the output table and formats the records as a readable plan, including useful notes and predicates.

Keeping these roles separate prevents a common mistake: EXPLAIN PLAN does not create a fresh table every time it runs. The destination must already exist and be accessible. Likewise, displaying a saved estimate does not cause the explained query to run or measure its response time.

How the default PLAN_TABLE is supplied

Oracle creates the global temporary table SYS.PLAN_TABLE$ and the PLAN_TABLE synonym, with the necessary privileges granted to PUBLIC. The table definition is shared, while the temporary plan data is private to each session. This is not a separate permanent table physically created in every application user's schema.

Explain and display your statement in the same database session. Two editor tabs may use different connections, even when both show the same username. Consequently, a plan generated in one connection may not be visible in the other. The default temporary output is also not a durable archive for reviewing plans after disconnecting.

A schema-local object named PLAN_TABLE can take precedence over the public synonym. This is useful when deliberate, but an old local table can also explain unexpected compatibility problems. Do not assume every reference to the name reaches the same object in every schema.

Check the available table before creating one

In SQL*Plus or a compatible script interface, inspect the resolved object:

DESCRIBE PLAN_TABLE

DESCRIBE is a client command, rather than a SQL statement to embed directly in an application. A successful description shows that the name resolves to an object whose columns can be inspected. It does not prove that you ran utlxplan.sql, and it does not identify the creation history of that object.

If you need to distinguish a schema-owned object from an available synonym, these diagnostic queries provide useful starting information:

SELECT object_name, object_type
FROM user_objects
WHERE object_name = 'PLAN_TABLE';

SELECT owner, synonym_name, table_owner, table_name
FROM all_synonyms
WHERE synonym_name = 'PLAN_TABLE'
  AND owner IN (USER, 'PUBLIC');

An empty first result is not proof that PLAN_TABLE is unavailable. The session may use the public synonym instead. Check the connection and current schema as well, especially if your application changes its schema context. If access fails, work with the DBA to resolve the object or privilege issue before creating, dropping or renaming anything.

Generate and display a labeled estimate

The following example assumes that the optional HR sample schema is installed and that your account can query HR.EMPLOYEES. HR is not present in every database. In another training environment, substitute an accessible table and valid column names.

EXPLAIN PLAN
  SET STATEMENT_ID = 'M5L3_EMP_01'
FOR
  SELECT employee_id, last_name
  FROM hr.employees
  WHERE department_id = 60;

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

The FOR clause introduces the statement being explained. The first command records its estimated plan without executing that employee query. The second command executes a query against the table function to obtain the formatted plan. This display query is separate from the application statement being investigated.

The three DISPLAY arguments identify the output table, the statement label and the formatting level. Using the same label in both commands makes the intended output explicit. STATEMENT_ID is a label you supply, not Oracle's SQL_ID for a cached SQL statement. Use a fresh label for a new comparison rather than allowing repeated experiments to become difficult to distinguish.

No fixed plan output is promised here. Your indexes, data distribution, statistics and optimizer environment affect the result. A different cost or access method from another learner's database is not evidence that the commands failed. Verify the object and predicate first, then interpret the resulting estimate in context.

EXPLAIN PLAN is DML and does not implicitly commit. Avoid appending a casual COMMIT in an application session just to follow a tutorial: it can commit other pending work. Run these examples in a dedicated training session, display the output before changing connections, and manage transactions according to the environment's rules.

Selected columns and what they mean

The complete table definition is larger than the selection below. These fields help connect the underlying records to the formatted display without requiring you to memorize a historical column listing.

Selected PLAN_TABLE fields
FieldsInterpretation
STATEMENT_ID, PLAN_IDThe supplied label and generated plan identifier.
ID, PARENT_ID, DEPTHOperation identifiers, parent relationships and nesting in the plan tree.
OPERATION, OPTIONSThe operation and its variant, such as a particular access method.
OBJECT_OWNER, OBJECT_NAMEThe object associated with an operation, when applicable.
COST, CARDINALITY, BYTESOptimizer estimates of work, rows and data volume.
ACCESS_PREDICATES, FILTER_PREDICATESConditions associated with locating or filtering rows.
OTHER_XMLAdditional structured information about the plan.

CARDINALITY is an estimated row count, not a measurement of rows actually produced. COST is an optimizer comparison value, not elapsed seconds. Operation IDs identify nodes in the tree; they should not be read as a simple chronological execution log.

Column definitions have evolved. For example, the 26ai reference lists REMARKS as VARCHAR2(4000), OPTIONS as VARCHAR2(255), and object owner and name fields as VARCHAR2(128). Copying an old table definition with narrower columns can create avoidable compatibility problems. Use Oracle's version-appropriate definition when a custom table is required.

For routine reading, prefer DBMS_XPLAN to a hand-written query that attempts to reconstruct the tree. The package handles formatting and exposes useful plan details without making the lesson depend on a fragile reporting script.

When a schema-local output table is appropriate

You may need a separately named table for an established reporting process or a controlled comparison workflow. That is a reason to consider custom setup, not a reason to replace the normal infrastructure for everyone. Decide how records will be identified, retained and cleaned before using such a table as a repository.

Oracle supplies utlxplan.sql as the sample output-table creation script. Use the script appropriate to the database version rather than reconstructing its DDL from an old article. Confirm the account's privileges, inspect the script, and check for an existing local PLAN_TABLE before running it.

On an appropriately configured Unix or Linux Oracle installation, Oracle documents this SQL*Plus-style invocation:

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

The path and environment-variable handling depend on the client platform and installation. A remote connection does not expose the database server's filesystem to your workstation. The machine running SQL*Plus must be able to read the script. On Windows, use the verified path to the appropriate script rather than copying a Unix path literally.

Table creation is DDL and can affect transaction boundaries. Perform optional setup separately from application transactions. Ordinary learners should not connect as SYSDBA merely to run the primary example. Oracle's catplan.sql creates the global infrastructure and synonym; it belongs to a different administrative task, not this routine user workflow.

Use an explicitly named custom destination

If you deliberately created a new schema-local PLAN_TABLE and want a different name, you can rename that owned table. This example assumes the local table exists and the destination name is available:

RENAME PLAN_TABLE TO my_plan_table;

EXPLAIN PLAN
  SET STATEMENT_ID = 'M5L3_CUSTOM_01'
  INTO my_plan_table
FOR
  SELECT employee_id, last_name
  FROM hr.employees
  WHERE department_id = 60;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'M5L3_CUSTOM_01', 'TYPICAL')
);

INTO chooses the existing destination. DISPLAY must read that same destination. Renaming a local table does not rename the object behind the public synonym, and this example is not an instruction to modify SYS.PLAN_TABLE$.

After a database upgrade, review manually maintained output tables against the current supplied definition. Preserve records that your process needs before any controlled recreation. This review concerns custom objects; it does not mean every user should drop and rebuild Oracle's default global table.

Connect plan estimates to performance evidence

The cost-based optimizer considers the statement, available structures, statistics and optimizer environment when choosing a plan. EXPLAIN PLAN is useful for examining that choice in the explain environment, but the application may run under different conditions. Schema resolution, bind information, statistics and settings can all matter.

A full table scan is not automatically a tuning defect. If a query needs much of a small table, scanning it can be appropriate. An index may help a selective lookup yet add work for a broad request. Read predicates and row estimates, then compare them with the workload's actual requirements instead of treating the presence of an index operation as the definition of success.

For a statement that has executed, DBMS_XPLAN.DISPLAY_CURSOR can inspect a loaded cursor's plan. It reads cursor information rather than the output of your last EXPLAIN PLAN. Identify the relevant SQL_ID and child cursor, and obtain the required performance-view access through the DBA.

Actual row-source statistics must have been collected during execution. Asking for ALLSTATS LAST does not retroactively collect missing measurements. Likewise, re-explaining SQL after its cursor has left the cache generates a new estimate; it does not recover the original execution evidence. Keep this distinction in mind as later lessons introduce more detailed plan analysis.

Troubleshoot the workflow in order

  1. Confirm the connection. Check the database, account and schema context. Keep explain and display in the same session for the default temporary table.
  2. Confirm the destination. Check whether the default name resolves through a synonym or a local object. For custom output, match INTO with the DISPLAY table argument.
  3. Confirm successful generation. A failed EXPLAIN PLAN does not create the expected records. Resolve errors before investigating an empty display.
  4. Confirm the label. A mistyped statement identifier selects the wrong records or no records. Use clear, distinct labels for separate examples.
  5. Check compatibility and permissions. Investigate stale local definitions and missing privileges before recreating anything.

A plan display problem and a query performance problem are different investigations. First make sure you are reading the intended plan. Then determine whether its estimates and access choices explain the measured workload behavior. Changing indexes before completing those checks can obscure the original cause.

Review questions

Before proceeding, explain why DESCRIBE can succeed even when your schema does not own PLAN_TABLE. Then identify which command writes plan records and which formats them. Finally, consider why an estimate produced in one session cannot establish the actual elapsed time of an application request in another.

The normal 26ai sequence is to check the supplied PLAN_TABLE, explain a statement, and display its estimate in the same session. Create a local output table only when the workflow calls for one. The next lesson, using the Oracle EXPLAIN PLAN statement, builds on this foundation with statement syntax and plan inspection.

Oracle 26ai references


SEMrush Software 3 SEMrush Banner 3