Search Results dba_histograms

Overview

FND_STATS is the Oracle E-Business Suite statistics management package body owned by the APPS schema. It provides the supported interface through which EBS administrators, concurrent programs, and maintenance routines gather, copy, back up, restore, and purge optimizer statistics for database objects belonging to the E-Business Suite schema. Its principal business function is to enforce statistics-gathering policy that is specific to EBS rather than to generic Oracle Database maintenance. The package honors EBS-level exclusions and histogram definitions maintained in the applications data dictionary, restricts operation to registered Oracle schemas, records a history of statistics operations, and wraps the native DBMS_STATS and DBMS_SPACE APIs so that schema monitoring, backup, and restore activities behave consistently across releases such as 12.1.1 and 12.2.2. The object is documented as VALID in the ETRM repository and is not referenced by any database object, although it is referenced by 106 other packages, confirming its role as a broadly consumed infrastructure utility rather than a downstream dependency of application logic.

Key Procedures and Functions

The 42 documented procedures cover the full statistics lifecycle. Schema and object gathering is served by GATHER_SCHEMA_STATISTICS, GATHER_SCHEMA_STATS, GATHER_SCHEMA_STATS_SQLPLUS, GATHER_TABLE_STATS, GATHER_INDEX_STATS, GATHER_COLUMN_STATS, and GATHER_ALL_COLUMN_STATS. Monitoring is controlled by ENABLE_SCHEMA_MONITORING and DISABLE_SCHEMA_MONITORING, which toggle table modification tracking. Preservation and recovery of existing statistics are handled by BACKUP_SCHEMA_STATS, BACKUP_TABLE_STATS, RESTORE_SCHEMA_STATS, RESTORE_TABLE_STATS, RESTORE_INDEX_STATS, and RESTORE_COLUMN_STATS, together with CREATE_STAT_TABLE and TRANSFER_STATS for staging statistic sets. Manual overrides are provided by SET_TABLE_STATS and SET_INDEX_STATS. PURGE_STAT_HISTORY manages retention of the package's own operation log. The GATHER_SCHEMA_STATS_SQLPLUS entry point is notable in that it supports invocation directly from a SQL*Plus session, complementing the concurrent-program entry points.

Tables Accessed

The package reads and writes several EBS control tables. FND_EXCLUDE_TABLE_STATS identifies tables whose statistics must not be gathered. FND_HISTOGRAM_COLS and FND_EXTNSTATS_COLS define column-level histogram requirements. FND_TABLES and FND_ORACLE_USERID determine which schemas and objects are eligible for processing. FND_PRODUCT_INSTALLATIONS identifies installed products, and FND_STATS_HIST with FND_STATS_HIST_S stores the history and detail of statistics operations. From the data dictionary it queries DBA_HISTOGRAMS to detect existing column histograms, DBA_TABLES, DBA_INDEXES, DBA_IND_COLUMNS, DBA_TAB_COLUMNS, DBA_TAB_PARTITIONS, and DBA_TAB_STATISTICS for object and segment metadata, DBA_EXTERNAL_TABLES for external table awareness, and DBA_TAB_MODIFICATIONS for change tracking. Dynamic performance views V$INSTANCE and V$PARAMETER supply instance-level context, while DBMS_STATS, DBMS_SPACE, and DBMS_OUTPUT provide the underlying engine, space analysis, and diagnostics.

Usage Notes

FND_STATS is normally invoked by EBS concurrent programs and by the standard statistics-gathering jobs supplied with the applications, most commonly the "Gather Statistics" programs that accept schema and table selection criteria. Administrators also call GATHER_SCHEMA_STATS_SQLPLUS manually to reproduce a concurrent gathering from the command line. Custom code should always call FND_STATS rather than DBMS_STATS directly, because only FND_STATS applies the EBS exclusion rules, histogram definitions, and history logging. Because DBA_HISTOGRAMS is queried at runtime, the presence or absence of existing histograms influences whether the package recreates them, making this view a frequent reference point when troubleshooting statistics behavior.