Search Results fnd_plan_table

Overview

FND_PLAN_TABLE is an Oracle EBS Application Object Library (FND) table that serves as the execution plan repository used by the Oracle cost-based optimizer (CBO) and the SQL EXPLAIN PLAN facility. Within the EBS 12.1.1 and 12.2.2 environments, this table stores the hierarchical output produced when EXPLAIN PLAN FOR is executed against a SQL statement. Each row represents a single step in the execution plan, and the rows are linked parent-to-child through PARENT_ID to form the tree that describes how the database intends to satisfy the query.

The object is owned by the APPLSYS schema and contains fifteen documented columns. The ETRM metadata notes that the table is designated "Retrofitted" and is not implemented in the reference database, meaning it exists as a legacy artifact for standard Oracle plan-table compatibility but may not be physically present in every EBS instance. From a Data Vault modeling perspective, the heuristic classification of FND_PLAN_TABLE is standalone — the table does not function as a hub, link, or satellite in a source-integration model, because it holds transient diagnostic output rather than business entity data. The single foreign key to MSD_AUDIT_SQL_STATEMENTS associates plan rows with audited SQL statements when plan capture is integrated with the EBS statement-auditing facility.

Key Information Stored

The table's surrogate primary key is STATEMENT_ID, a numeric identifier that also carries the foreign key relationship to FND_PLAN_TABLE.STATEMENT_ID referencing MSD_AUDIT_SQL_STATEMENTS. All rows belonging to one explained statement share the same STATEMENT_ID value. Within the hierarchy, ID provides the unique step identifier and PARENT_ID references the ID of the parent step, with POSITION dictating the display order of sibling nodes. These three columns form the structural backbone of the plan tree.

The descriptive columns carry the optimizer's operational detail. OPERATION names the access path (for example, TABLE ACCESS, INDEX RANGE SCAN, SORT, NESTED LOOPS). OPTIONS qualifies the operation, such as FULL or BY INDEX ROWID. OBJECT_OWNER identifies the schema of the referenced segment, while OBJECT_NAME gives the table or index name. OBJECT_TYPE classifies the segment type, and OBJECT_INSTANCE distinguishes multiple references to the same object within one plan. OBJECT_NODE records the database link or instance for distributed execution. SEARCH_COLUMNS describes the predicate columns used in index access paths. REMARKS holds free-text annotations, TIMESTAMP records when the plan row was generated, and OTHER stores supplementary optimizer tags. Among these, OBJECT_NAME, OBJECT_OWNER, and OPERATION are the strongest business-identifying candidates for diagnostic queries, though no unique index is documented beyond the surrogate key structure.

Common Use Cases and Queries

The principal use case is performance troubleshooting. A DBA explains a suspect statement into FND_PLAN_TABLE, then queries the hierarchy to detect full table scans, Cartesian products, or unfavorable join orders. A typical pattern selects the top-level steps with no parent:

• SELECT id, parent_id, operation, options, object_name FROM fnd_plan_table WHERE statement_id = :sid AND parent_id IS NULL ORDER BY position;
• Recursive retrieval of the full tree using CONNECT BY PRIOR id = parent_id START WITH parent_id IS NULL to render an indented plan.
• Aggregation by OPERATION and OBJECT_NAME to identify the most frequently scanned segments across a set of audited statements joined through MSD_AUDIT_SQL_STATEMENTS.
• Comparison of plan snapshots before and after statistics gathering, using TIMESTAMP to separate generations of plan rows.

Reporting use cases include capacity reviews where large full-scan operations on high-volume EBS tables such as FND_LOG_MESSAGES are catalogued, and regression testing where stored plan output validates optimizer stability after patching or upgrade.

Related Objects

• MSD_AUDIT_SQL_STATEMENTS — referenced by the FND_PLAN_TABLE.STATEMENT_ID foreign key; supplies the audited SQL text and execution context for each plan.
• FND_LOG_MESSAGES and FND_LOG_METADATA — companion FND diagnostics tables frequently co-queried during tuning sessions.
• PLAN_TABLE — the standard Oracle counterpart; FND_PLAN_TABLE is effectively an EBS-schema variant populated by the same EXPLAIN PLAN mechanism.
• DBA_TABLES / DBA_INDEXES — joined on OBJECT_OWNER and OBJECT_NAME to enrich plan rows with segment size and statistics.
• FND_CONCURRENT_REQUESTS — links performance analysis back to the concurrent program that issued the SQL.
• DBMS_XPLAN — the packaged API commonly used to render FND_PLAN_TABLE rows in formatted report output.

Because the object is documented as not implemented in the reference database, administrators should verify its physical presence with DBA_TABLES before relying on it in scripts, particularly in 12.2.2 online-patching environments where object deployment may vary.