Search Results fnd_extnstats_cols




The FND_EXTNSTATS_COLS table in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 is a critical repository object used for managing extended statistics on columns within the Oracle database. Extended statistics, introduced in Oracle Database 11g, allow for more sophisticated query optimization by capturing relationships between multiple columns or expressions, which standard single-column statistics cannot adequately represent. This table serves as a metadata repository for such statistics in the context of Oracle EBS applications.

Purpose and Functionality

The primary purpose of FND_EXTNSTATS_COLS is to store information about column groups and expression-based statistics that have been defined for Oracle EBS tables. These statistics help the Oracle Cost-Based Optimizer (CBO) generate more accurate execution plans by considering correlations between columns or complex expressions. For example, if two columns in a table are frequently queried together (e.g., ITEM_ID and ORGANIZATION_ID in inventory tables), extended statistics can improve query performance by providing the optimizer with better selectivity estimates.

Table Structure

The FND_EXTNSTATS_COLS table typically contains the following key columns:
  • TABLE_NAME: The name of the EBS table for which extended statistics are defined.
  • EXTENSION_NAME: A unique identifier for the extended statistics group or expression.
  • EXTENSION: The actual column group or expression (e.g., (ITEM_ID, ORGANIZATION_ID)).
  • CREATED_BY and CREATION_DATE: Metadata tracking the creation of the statistics.
  • LAST_UPDATED_BY and LAST_UPDATE_DATE: Metadata tracking the last modification.

Integration with Oracle EBS

In Oracle EBS 12.1.1 and 12.2.2, the FND_EXTNSTATS_COLS table is part of the Application Object Library (AOL) schema, which manages foundational metadata for the EBS environment. The table is populated and maintained through EBS-specific processes, such as the FND_STATS utility, which automates the gathering of optimizer statistics for EBS tables. When extended statistics are created using DBMS_STATS.CREATE_EXTENDED_STATS, relevant entries are recorded in this table to ensure consistency across EBS modules.

Usage Scenarios

  1. Performance Tuning: Database administrators (DBAs) use this table to identify and manage extended statistics for critical EBS tables, ensuring optimal query performance.
  2. Patch and Upgrade Management: During EBS upgrades or patches, the table helps verify that extended statistics are preserved or recreated as needed.
  3. Troubleshooting: When query performance degrades, DBAs can cross-reference this table to check if missing or outdated extended statistics are the cause.

Best Practices

  • Regularly review and update extended statistics using FND_STATS.GATHER_TABLE_STATS with the METHOD_OPT parameter to include column groups.
  • Document custom extended statistics in FND_EXTNSTATS_COLS to avoid conflicts during system changes.
  • Monitor the table for redundant or obsolete entries, especially after schema changes or module decommissioning.

Conclusion

The FND_EXTNSTATS_COLS table is a vital component of Oracle EBS 12.1.1 and 12.2.2, enabling advanced query optimization through extended statistics. By maintaining accurate metadata about column groups and expressions, it ensures the Oracle CBO can generate efficient execution plans, directly impacting the performance of EBS applications. Proper management of this table is essential for DBAs and developers working in Oracle EBS environments.