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

EBS runs on Oracle Database and does not follow generic DBA practice for statistics. The differences are specific and they matter.

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

Use FND_STATS, not DBMS_STATS.

    FND_STATS.GATHER_SCHEMA_STATISTICS(schemaname => 'ALL');
    FND_STATS.GATHER_TABLE_STATS(ownname => 'AP',
                                 tabname => 'AP_INVOICES_ALL');

FND_STATSDBMS_STATS

FND_STATS wraps DBMS_STATS with EBS-specific knowledge: which tables need histograms, which have locked statistics, and how EBS's own queries expect the statistics to look.

FND_STATSDBMS_STATS

Gathering statistics on EBS schemas with plain DBMS_STATS is a documented way to make plans worse, and it is a common cause of "we gathered stats and the system got slower".

DBMS_STATS

Some tables should have statistics locked. Interface and temporary-pattern tables are usually empty and occasionally hold a million rows during a batch run.

    gather stats while empty  -> the optimiser plans for zero rows
                              -> a nested loop over a million rows
                                 during the batch

Fixed or locked statistics that describe the loaded state give a stable plan. This is the opposite of general advice, and it is right here because the table's size is bimodal rather than growing.

Indexing on seeded tables is constrained. You can add an index to an Oracle table, and you then own it: patches do not know about it, and it may conflict with one Oracle adds later.

So the sequence is to check whether an existing index can serve the query by reordering the predicates, before adding one. And any index you do add belongs in a documented list, because the next upgrade will ask.

Custom tables are yours, with ordinary practice — index the foreign keys, index what you filter on, and remember org_id if it is multi-org.

org_id

The EBS-specific query problems, which come up repeatedly:

    filtering on ATTRIBUTE columns   almost never indexed
    omitting org_id                  indexed and selective
    querying a seeded view           joins you do not need
    a SELECT inside a loop           found instantly by ST05-style
                                     tracing

The routine. Gather statistics on a schedule with FND_STATS, keep the locked ones locked, and re-evaluate plans after a significant data change — rather than gathering everything nightly because that is the generic advice.

FND_STATS

What the interviewer is probing.1. "Why gather statistics with FND_STATS?" Probing: the platform specific. Stalls: "Any method works." Moves up: it applies the sampling and histogram settings EBS expects, and it knows which objects to skip — plain DBMS_STATS can produce worse plans.

2. "Can you add a custom index to a seeded table?" Probing: the supportability question. Stalls: "Never." Moves up: yes, and it is supported, but it must be registered and reviewed at upgrade — and it will not survive a table rebuild unnoticed.

3. "What do you look at for a slow instance overall?" Probing: the method. Stalls: "The server." Moves up: AWR for top waits and SQL, then whether it is one bad statement, a missing statistic, or genuine capacity.

4. "What is special about EBS SQL you cannot change?" Probing: the constraint. Stalls: "Nothing." Moves up: seeded SQL cannot be rewritten, so you work with statistics, indexes, profiles and baselines instead.