Database Performance Tuning for EBS: Statistics and Indexing Strategy

Explain EBS-specific considerations for database statistics gathering and indexing that differ from generic Oracle DBA practice

While EBS runs on a standard Oracle Database, its statistics-gathering and indexing strategy has important EBS-specific nuances that differ from generic DBA best practice. Oracle strongly recommends using FND_STATS (a wrapper around DBMS_STATS that's aware of EBS's specific table types, including special handling for tables with volatile data like concurrent processing and interface tables) rather than calling DBMS_STATS directly against APPS-owned schemas, since FND_STATS incorporates EBS-specific logic (like recognizing which tables should use fixed/locked statistics rather than being re-gathered on a normal schedule).

Using generic DBMS_STATS on EBS interface tables is like taking a single photo of a stadium parking lot at 3 AM (empty) and using that snapshot to plan traffic flow for game day (fully loaded) — FND_STATS and statistics locking are like instead using a photo taken during a typical game day crowd, giving the optimizer a much more representative picture to plan around.

Key Concepts

Some EBS tables — particularly certain interface and temporary-pattern tables that are frequently near-empty but occasionally loaded with large volumes during batch processing — benefit from locked/fixed statistics reflecting their typical loaded state, preventing the optimizer from generating poor execution plans based on a snapshot taken when the table happened to be empty (a classic EBS-specific gotcha that generic DBA statistics practices don't anticipate).

interface and temporary-pattern tableslocked/fixed statistics

Indexing strategy in EBS must account for the framework-level realities already discussed elsewhere — DFF/flexfield ATTRIBUTE columns are rarely indexed by default, ORG_ID (multi-org) filtering is pervasive and usually benefits from being part of composite indexes on heavily-queried transactional tables, and custom indexes added to seeded tables must be carefully evaluated against Oracle's patching process (custom indexes on seeded tables are generally supported, but must be named following Oracle's custom-object naming conventions and documented, since a patch could theoretically drop and recreate the underlying table structure in rare cases).

DFF/flexfield ATTRIBUTE columns are rarely indexed by defaultORG_ID

Interviewers often ask a candidate to explain why FND_STATS is preferred over plain DBMS_STATS in an EBS context, and to describe the practical scenario of locking statistics on a volatile interface table to stabilize execution plans — testing whether the candidate has genuine EBS-specific DBA experience versus only generic Oracle DBA knowledge.