PL/SQLadvanced
Performance Tuning Custom PL/SQL and SQL in EBS
Explain the standard toolkit and methodology for diagnosing and fixing slow custom code in EBS
"The custom program ran in 10 minutes and now takes 3 hours." The expected answer is a method, not a fix.
Performance tuning without tracing is like trying to fix a car by guessing which part is broken; tracing and TKPROF are like plugging in an actual diagnostic scanner that tells you precisely which component (SQL statement) is consuming the most 'fuel' (time/resources) before you start replacing parts.
Key Concepts
1
1. Reproduce with trace on. Tick "Enable Trace" on the concurrent program definition, or set the trace
profile, and run it.
2
That produces a raw trace file on the database server.
3
2. Format it with TKPROF. The output ranks every statement by elapsed time, with execution counts and
plans.
4
call count elapsed rows
Parse 1 0.01 0
Execute 40132 38.20 0 <- 40,132 executions
Fetch 40132 2.10 401325
Forty thousand executions of one statement is a SELECT inside a loop. You have found it without reading
the code.
6
3. Look at the worst statement's plan. A full table scan where you expected an index, or an estimate
wildly different from the actual row count.
7
4. Then, and only then, choose a fix.
a SELECT per row -> BULK COLLECT and FORALL, or a join
stale statistics -> FND_STATS
missing index -> add one, accepting the write cost
a predicate defeating one -> rewrite it8
FND_STATS, not DBMS_STATS. EBS provides its own wrapper because it understands the multi-org
partitioning and the seeded data patterns, and it maintains the histograms EBS's own queries rely on.
FND_STATSDBMS_STATS
9
FND_STATS.GATHER_TABLE_STATS(ownname => 'XXCUST',
tabname => 'XX_ORDERS');10
Gathering statistics on EBS tables with plain DBMS_STATS is a documented way to make things worse.
DBMS_STATS
11
The EBS-specific culprits worth knowing.Missing org_id. Multi-org tables are large and org_id is indexed. A query without it scans far more
than it needs, even though the VPD policy will filter the result correctly.
org_id
12
Flexfield attribute columns in WHERE. ATTRIBUTE1 and friends are almost never indexed, and they hold
different things in different rows. Filtering on one usually means a scan.
WHEREATTRIBUTE1
13
Row-by-row processing over interface tables, where the whole point was volume.
14
For OAF pages, the equivalent is View Object queries — unnecessary columns, and missing bind variables
producing a hard parse per request.
15
The point of the sequence. Each step is cheaper than the next and rules something out. Jumping to "add
an index" skips the possibility that the statistics were stale, which costs nothing to fix and carries no
ongoing write penalty.
16
What the interviewer is probing.1. "Where do you start?" Probing: the method. Stalls: "Rewrite the query." Moves up: find
the actual expensive statement from the trace or AWR — guessing at a 2,000-line package wastes days.
17
2. "What does a 10046 trace with TKPROF give you?" Probing: the tooling. Stalls: "A log."
Moves up: per-statement elapsed time, parse counts and waits, which separates a slow query from a
slow loop around a fast one.
18
3. "What is the most common cause you find?" Probing: experience. Stalls: "Missing indexes."
Moves up: row-by-row processing where a set-based statement would do, and queries written without
the org or ledger predicate that enables index use.
19
4. "Why do EBS statistics need care?" Probing: the platform specific. Stalls: "They do not."
Moves up: they should be gathered with the FND_STATS package, not plain DBMS_STATS, because EBS
expects its own sampling and histogram settings.