HANA DBadvanced
HANA Performance Optimization & PlanViz
Diagnosing and resolving slow HANA queries using EXPLAIN PLAN, PlanViz, and column-store-aware optimization techniques.
Tuning on HANA is not general SQL tuning. The engine is a column store working in memory across many cores, so the usual instincts — add an index, avoid full scans — often do not apply.
Reading a plain textual EXPLAIN PLAN is like reading a play's script and imagining the performance; PlanViz is watching the actual recorded performance with a stopwatch on each scene, immediately showing you which scene (query operation) ran unexpectedly long, rather than requiring you to imagine it from the script alone.
Key Concepts
1
Start with the plan. EXPLAIN PLAN shows what the optimiser chose: join order, join algorithm, and
which operations run in the column engine rather than falling back to slower row-oriented processing.
EXPLAIN PLAN
2
PlanViz draws that plan with per-operation timing and row counts, which makes the dominant operation
obvious instead of buried in text.
3
The HANA-specific bottleneck is materialisation. Normally the engine pipelines work: each operation
streams results to the next, across all cores, without building the intermediate set.
4
Some patterns break that and force a full intermediate result into memory first:
5
pipelined filter -> aggregate -> join, streamed in parallel
materialised build 40 million intermediate rows, then continue6
Materialisation costs memory and stops the parallelism. PlanViz shows where it happens, and the cause is
usually a filter applied too late, an awkward join, or imperative logic between set operations in
SQLScript.
7
Push filters as early as possible. This is the single most effective HANA fix, and it is the same idea
as input parameters in calculation views: filtering at the end means every row was processed first.
8
Star join optimisation only fires if it can recognise the shape. HANA has a special path for one fact
table joined to several dimension tables — the standard analytical pattern.
9
It applies when the joins genuinely look like a star. A model where dimensions join to each other, or
where the fact table is buried behind an intermediate node, misses the optimisation silently. The query
still returns the right answer, considerably slower.
10
Finding slow queries after the fact.
M_EXPENSIVE_STATEMENTS statements over a configured threshold,
recorded with their runtime
M_SQL_PLAN_CACHE cached plans plus execution counts11
The plan cache matters for the same reason as in any database: sort by total time rather than worst case. A
statement taking 40 ms and running two million times costs far more than one taking 20 seconds nightly.
12
The discipline is the same as anywhere. Measure, find the one dominant operation, change one thing, and
measure again.
13
What differs on HANA is what you are looking for. Not a missing index — HANA needs very few — but a
materialised intermediate result, a filter applied too late, or a model whose shape prevents an
optimisation from applying.
14
What the interviewer is probing.1. "What do you look for first in PlanViz?" Probing: the method. Stalls: "The total time."
Moves up: the dominant operator and the rows flowing between nodes — a node producing far more
rows than expected is usually the cause.
15
2. "What does it mean if a view is materialised early?" Probing: interpretation. Stalls:
"Nothing." Moves up: push-down failed, so the engine computed a large intermediate result —
usually a union, a scalar function or a complex join condition.
16
3. "Why can a query be slow despite being in memory?" Probing: the insight. Stalls: "It
cannot." Moves up: memory removes IO, not computation — bad joins, missing filters and row-by-row
logic still dominate.
17
4. "What is the role of table partitioning in performance?" Probing: the connection. Stalls:
"None." Moves up: partition pruning limits what is scanned, and partitioning is required anyway
past the two-billion-row limit per partition.