Reportingadvanced
Report Performance Tuning and Data Model Optimization
Explain strategies for diagnosing and improving slow-running reports across BI Publisher, OTBI, and FSG
"The invoice report ran in 30 seconds and now takes 20 minutes." The method is to locate the bottleneck before changing anything.
Diagnosing a slow report is like figuring out why a food delivery is late: is it the kitchen taking too long to prepare the order (data model SQL), or is the delivery driver taking an inefficient route with too many unnecessary stops (rendering/template complexity)? You need to isolate which stage is actually the bottleneck before trying to fix it.
Key Concepts
1
Three places it can be.
the data model SQL usually. Poor joins, missing indexes,
predicates that defeat them.
the volume returned the query returns far more than the
template displays.
the template excessive nested loops or sub-templates.2
It is the first one nearly every time, and the diagnosis is ordinary SQL tuning.
3
Get the query out and run it on its own.
extract the data model SQL
run it in SQL*Plus with the same parameters
EXPLAIN PLAN, or trace it4
If the query is slow standalone, the template is irrelevant. If it is fast and the report is slow, then
look at the template.
5
The EBS-specific causes worth checking.Flexfield attribute columns in the WHERE clause. ATTRIBUTE1 is almost never indexed and holds
different things in different rows. Filtering on one usually means a full scan.
WHEREATTRIBUTE1
6
Querying a view instead of the tables. EBS views join broadly for general use. A report needing three
columns may be paying for six joins it does not use.
7
Missing org_id. On a multi-org table it is indexed and highly selective, and leaving it out reads far
more than necessary even though the result is filtered correctly.
org_id
8
Stale statistics. FND_STATS, not plain DBMS_STATS.
FND_STATSDBMS_STATS
9
Volume is the second thing to check. A data model selecting forty columns when the template prints six
transfers and formats the rest for nothing. On a hundred thousand rows that is measurable.
10
For OTBI it is different, because users generate the SQL without seeing it. The levers are subject area
design and user behaviour — filter early, on selective indexed dimensions, rather than pulling everything
into a pivot and filtering there.
11
An unfiltered analysis over a year of transactions is not just slow for its author; it runs against the
transactional database everyone else is using.
12
And the step that is skipped. Test against production-scale data.
13
A report tuned against a development instance with five thousand rows tells you nothing about its behaviour
at five million. "It was fine in test" is usually this, and it is why the report was fine for two years and
then was not.
14
What the interviewer is probing.1. "Where is the time usually spent?" Probing: the method. Stalls: "Rendering." Moves up:
in the data model query far more often than in formatting — so trace the SQL before touching the
template.
15
2. "What template choice affects performance?" Probing: the depth. Stalls: "None." *Moves
up:* very large outputs — scalable mode and avoiding complex nested RTF structures, since the
formatter holds data in memory.
16
3. "Why is returning fewer rows better than filtering in the template?" Probing: the
principle. Stalls: "It is not." Moves up: everything fetched is transferred and held; filtering
at the database means the work is never done.
17
4. "What is the quick win you look for first?" Probing: practical experience. Stalls: "An
index." Moves up: a query without the ledger, org or date predicate that would let an existing
index be used — very common in copied reports.