Search Results gather_schema_stats

Overview

FND_STATS is the Oracle E-Business Suite statistics management package. It provides the PL/SQL interface through which the EBS application tier controls Oracle Cost Based Optimizer (CBO) statistics for the APPS schema and any registered custom schemas. In EBS 12.1.1 and 12.2.2, the optimizer depends on representative object statistics; without them, execution plans for concurrent programs, forms-based queries, and report SQL degrade severely. FND_STATS centralizes the gathering, backup, restoration, transfer, deletion, and manual setting of statistics so that administrators do not have to invoke DBMS_STATS directly against application objects. It also maintains the EBS-specific metadata that governs which columns receive histograms and which tables are excluded from statistics collection.

Key Procedures and Functions

The package exposes forty-two documented procedures and functions. The following groups are the most operationally significant.

• Collection: GATHER_SCHEMA_STATISTICS, GATHER_SCHEMA_STATS, GATHER_SCHEMA_STATS_SQLPLUS, GATHER_TABLE_STATS, GATHER_INDEX_STATS, GATHER_COLUMN_STATS, and GATHER_ALL_COLUMN_STATS collect CBO statistics at schema, table, index, and column level. The SQL*Plus variant is designed for invocation from a SQL*Plus session rather than a concurrent program.
• Backup and Restore: BACKUP_SCHEMA_STATS, BACKUP_TABLE_STATS, RESTORE_SCHEMA_STATS, RESTORE_TABLE_STATS, RESTORE_INDEX_STATS, and RESTORE_COLUMN_STATS copy statistics into a user statistics table and reinstate them, allowing rollback if newly gathered statistics cause plan regressions.
• Transfer: TRANSFER_STATS moves statistics between a user statistics table and the data dictionary using an action code, supporting staging and version migration.
• Purging: PURGE_STAT_HISTORY removes historical statistics records. This is the procedure targeted by the search term "purge_stat_history" and is typically used to reclaim space in the FND_STATS_HIST and FND_STATS_HIST_S tables after repeated collection cycles.
• Manual Overrides: SET_TABLE_STATS and SET_INDEX_STATS write caller-supplied values into the dictionary, bypassing collection for objects where estimates are known or collection is unsafe.
• Monitoring and Storage: CREATE_STAT_TABLE builds the user statistics repository; ENABLE_SCHEMA_MONITORING and DISABLE_SCHEMA_MONITORING toggle DML monitoring for a schema or for all schemas.

Tables Accessed

FND_STATS reads the Oracle dictionary views DBA_TABLES, DBA_INDEXES, DBA_IND_COLUMNS, DBA_TAB_COLUMNS, DBA_TAB_PARTITIONS, DBA_HISTOGRAMS, and DBA_EXTERNAL_TABLES to identify objects, partitions, and existing statistics. EBS-specific control data resides in FND_STATS_HIST and FND_STATS_HIST_S, which store the history that PURGE_STAT_HISTORY trims; FND_EXCLUDE_TABLE_STATS, which lists tables that must be skipped; FND_HISTOGRAM_COLS, which names the columns eligible for histograms; and FND_EXTNSTATS_COLS, which extends column-level control to external or plugin tables. FND_TABLES supplies registered application table information, while FND_ORACLE_USERID and FND_PRODUCT_INSTALLATIONS identify valid schemas and the installed products against which collection runs.

Usage Notes

FND_STATS is invoked by the concurrent program "Gather Schema Statistics" and by the statistics maintenance programs scheduled by the system administrator, as well as by AutoConfig and patching utilities during upgrade and patch application. It is referenced by 106 other packages, so custom code should prefer it over direct DBMS_STATS calls to stay consistent with EBS rules. Because collection is resource-intensive and affects cursor invalidation, administrators normally run GATHER_SCHEMA_STATS during maintenance windows, monitor outcomes through FND_STATS_HIST, and invoke PURGE_STAT_HISTORY periodically to bound the growth of history tables.