| Lesson 3 | Oracle PLAN_TABLE |
| Objective | Use the default PLAN_TABLE and explain when to create a schema-local output 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.
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.
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.
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.
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.
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.
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.
| Fields | Interpretation |
|---|---|
STATEMENT_ID, PLAN_ID | The supplied label and generated plan identifier. |
ID, PARENT_ID, DEPTH | Operation identifiers, parent relationships and nesting in the plan tree. |
OPERATION, OPTIONS | The operation and its variant, such as a particular access method. |
OBJECT_OWNER, OBJECT_NAME | The object associated with an operation, when applicable. |
COST, CARDINALITY, BYTES | Optimizer estimates of work, rows and data volume. |
ACCESS_PREDICATES, FILTER_PREDICATES | Conditions associated with locating or filtering rows. |
OTHER_XML | Additional 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.
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.
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.
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.
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.
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.