Adminadvanced
Performance Tuning: End-to-End Diagnosis
Explain a systematic approach to diagnosing OBIEE performance problems across the full stack — network, Presentation Services, BI Server, RPD, and database.
"The dashboard is slow" spans five layers. The method is to localise before tuning, because each layer has different tools and different fixes.
It's like diagnosing why a package is arriving late: before assuming it's the delivery truck's fault (the database), you check whether it left the warehouse late (BI Server query generation), got stuck at a sorting facility (connection pool queuing), or was simply given the wrong, longer route to begin with (poor aggregate navigation) — the tracking log (nqquery.log) shows you exactly which leg of the journey actually took too long.
Key Concepts
1
The layers, and what makes each slow.
browser / network a large payload, many views rendering
Presentation
Services many analyses on one page, session overhead
BI Server a poor plan, federation joining large sets,
no cache hit
RPD model no aggregate used, wrong content levels,
combining facts badly
database the physical SQL itself2
Localise with two numbers. Usage tracking gives total elapsed time per analysis and the time spent in
the database.
3
total 40s, database 38s -> a database or SQL problem
total 40s, database 2s -> the BI Server or the model
analysis fast, page slow -> the page: too many analyses, or
the browser4
That single comparison directs everything that follows, and doing it first avoids tuning the wrong layer.
5
Then, per layer.Database. Take the physical SQL from the query log and run it directly. Explain plan, statistics, indexes
— ordinary tuning.
6
BI Server and model. Read the generated physical SQL. Did it use the aggregate you expected? Is it issuing
several queries and joining them in memory? Both are model questions.
7
Presentation. Count the analyses on the page. Fifteen small ones are slower than three dense ones, and
views within one analysis are free.
8
Caching. Check the hit rate. Seeded cache that never hits means the seeding queries do not match what
users run.
9
The most common findings, in rough order: a page firing too many queries; an aggregate that exists and
is not being used because a content level is wrong; a filter applied late so the database returns far too
many rows; and stale statistics on the warehouse.
10
And the discipline. Change one thing, measure again, and keep the original number. A tuning session
where three changes were made together tells you nothing about which helped — or that one helped while
another hurt.
11
What the interviewer is probing.1. "Where do you start on a slow dashboard?" Probing: the method. Stalls: "The database."
Moves up: split the time — browser rendering, Presentation Services, BI Server, then the database
— because tuning the wrong layer achieves nothing.
12
2. "What tells you it is the database?" Probing: the diagnosis. Stalls: "A guess." *Moves
up:* the session log showing the physical SQL and its elapsed time against total request time.
13
3. "What points at a modelling problem rather than a slow query?" Probing: the depth.
Stalls: "Nothing." Moves up: several physical queries where you expected one, or the server
joining results itself — both come from the model, not the database.
14
4. "What is the most common fix in practice?" Probing: experience. Stalls: "An index."
Moves up: constraining the query — default prompt values and an aggregate at the right grain
usually beat any database tuning.