Search Results reset_global_pref_defaults

Overview

SYS.DBMS_STATS is the Oracle-supplied PL/SQL package responsible for gathering, managing, and maintaining optimizer statistics for database objects. Within Oracle E-Business Suite 12.1.1 and 12.2.2, it underpins the Cost-Based Optimizer (CBO) by providing the statistics that drive execution plan selection for every SQL statement executed against the EBS schema. Although the package does not perform a business function in the traditional EBS sense, it is a foundational infrastructure component: inaccurate or stale statistics directly affect the performance of all EBS modules, from Order Management to General Ledger.

The package is owned by SYS and is classified in ETRM as an "OTHER" API. Its status is VALID, and it exposes 151 documented procedures and functions. EBS DBAs invoke DBMS_STATS both directly and indirectly through FND_STATS and the concurrent manager's Automatic Statistics Gathering programs, which are wrappers around this package.

Key Procedures and Functions

The documented procedures fall into several functional groups:

• Preference management: SET_PARAM, GET_PARAM, RESET_PARAM_DEFAULTS, SET_GLOBAL_PREFS, GET_PREFS, RESET_GLOBAL_PREF_DEFAULTS, SET_TABLE_PREFS, DELETE_TABLE_PREFS, EXPORT_TABLE_PREFS, IMPORT_TABLE_PREFS, SET_SCHEMA_PREFS, DELETE_SCHEMA_PREFS, EXPORT_SCHEMA_PREFS, IMPORT_SCHEMA_PREFS, SET_DATABASE_PREFS, DELETE_DATABASE_PREFS, and EXPORT_DATABASE_PREFS control statistics-gathering behavior at the database, schema, and table levels.
• Column value preparation: PREPARE_COLUMN_VALUES, PREPARE_COLUMN_VALUES_NVARCHAR, and PREPARE_COLUMN_VALUES_ROWID convert raw column data into formats suitable for histogram construction, supporting the gathering routines.
• Statistics gathering and management: The remaining documented procedures cover gathering, deleting, exporting, importing, locking, and restoring statistics across tables, indexes, columns, schemas, and the database at large.

ETRM records 45 dependent packages that reference DBMS_STATS, including DBMS_STATS_INTERNAL, DBMS_SQLTUNE_INTERNAL, DBMS_REDEFINITION, UTL_RECOMP, and DVSYS.CONFIGURE_DV, confirming its role as a shared infrastructure service across the database.

Tables Accessed

The ETRM metadata does not list application tables accessed through APPS synonyms, which is expected given the package's infrastructure nature. DBMS_STATS maintains its own dictionary-managed repositories: the COLDICTTAB, RAWCTAB, SELTAB, and CTAB structures and the DS_VARRAY_4_CLOB collection type are directly referenced in the dependency list. These support column preparation and histogram processing. Persisted statistics surface through data dictionary views such as DBA_TAB_STATISTICS, DBA_TAB_HISTOGRAMS, DBA_PART_HISTOGRAMS, and their ALL_ and USER_ counterparts, all of which reference DBMS_STATS. In EBS, statistics are stored in these dictionary objects rather than in application tables, with no direct inserts into EBS transactional tables during normal operation.

Usage Notes

DBMS_STATS is typically invoked through one of three channels in EBS. First, the concurrent program "Gather Statistics" (FND_STATS wrapper) uses it to collect statistics on EBS schemas on a scheduled basis, often run during maintenance windows. Second, DBAs invoke it manually to refresh statistics after large data loads, patches, or upgrades, using schema-level or table-level calls. Third, Automatic Statistics Gathering, controlled by the scheduler, calls it during defined maintenance windows. Custom code should avoid hard-coding parameter combinations; instead, EBS best practice is to use FND_STATS procedures so that EBS-specific logic (such as excluding temporary or interface tables) is applied consistently. Because DBMS_STATS writes to the data dictionary, careful scheduling is required to avoid contention, and table preference settings should be exported before upgrades to preserve optimizer behavior.