Performance Analysis: SQL Tuning & Plan Analysis

You open PlanViz on a slow statement and want the fastest read on where the time went. Where do you look first, and what does it tell you?

A specific SQL statement from a packaged app (source you can't edit) consistently picks a bad plan. You want a fix that survives recompiles and needs no code change. Statement hint or explicit hint, and what decides?

Your query against M_SYSTEM_DATA_STATISTICS to survey statistics across all tables returns an error instead of a result set. Why, and what's the constraint you missed?

An incident happened 2 weeks ago and you go to M_LOAD_HISTORY_* for the load picture. Why might the data already be gone, and what's the danger of tightening the sampling to catch more?

A junior admin reads an SAP HANA execution plan top-to-bottom and concludes the first listed operator runs first. Why is that backwards, and how is the true order reconstructed?

After a failover to the secondary host, you open M_LOAD_HISTORY_HOST/SERVICE to investigate the pre-failover load and they're empty. Is the history lost, and where do you actually look?

A production statement performs badly, but re-typing its SQL into EXPLAIN PLAN gives a different (fine) plan. What EXPLAIN PLAN option gets you the plan the optimizer actually chose in production?

You need to tell whether the index server or another service is driving host CPU. Which of M_LOAD_HISTORY_HOST vs M_LOAD_HISTORY_SERVICE do you query, and what's the trap?

On a current system, a colleague tells you to watch the SAP HANA Studio load graph for live CPU/memory. Why is that advice stale, and what's left in Studio that still works online?

Two plans for the same query differ only in when they materialize (convert column data to rows). Which is generally faster, and why is 'convert once early' the wrong intuition?

You need the load trend over the past month, not the past few days. Which source do you query, and what resolution must you accept?

You want the expensive-statements trace to cover users whose names start with SAP but exclude any starting with TEST. What filter syntax do you write, and which one parameter can't take a list?

On a busy production HANA system you see a low plan-cache hit ratio and frequent recompiles. Which parameter do you check, and why is the default a likely culprit?

You suspect the SQL plan cache is undersized. Which view and which columns actually tell you, and which service do you care about?

A statement is slow and its plan's EXECUTION_ENGINE column shows the work bouncing between different HANA engines. What does that signal, and what's the tuning lever?

Two functionally identical statements that differ only in formatting (extra spaces, a comment, literal vs bind value) keep recompiling instead of sharing a cached plan. Why?

In PlanViz you can Visualize Plan via Execute or Prepare. For diagnosing why a statement is actually slow, which do you pick and what do you give up with the other?

An ABAP report runs millions of times with different WHERE values, yet you find only ONE plan-cache entry for it. Why does it share a single execution plan?

A statement that caused a problem an hour ago is no longer in M_SQL_PLAN_CACHE. Where else can you find its plan statistics, and why is it gone from the cache view?

Among the PlanViz sub-tabs (Timeline, Operator List, Tables Used, Performance Trace, Network), which one is useless on a single-node system, and what does it show?

Plans on a HANA system degraded overnight and an admin's first instinct is 'the optimizer statistics went stale.' Why is stale stats an unlikely root cause on HANA specifically?

You launch PlanViz on a statement that returns a very large result set and the session runs far longer than a normal Studio query. Why does PlanViz ignore the 1,000-row limit, and what's the operational risk?

You recorded an ST05 SQL trace and click to display it, but the list is empty or incomplete. What step did you skip, and what default record limit might also be hiding rows?

Reading a PlanViz operator named JEAggregate, how do you tell which engine is doing the work, and what does the JE prefix mean?

In EXPLAIN_PLAN_TABLE you see operators with NULL in TABLE_NAME, mostly near the top of the plan. Bug or normal?

On an S/4HANA system, a user reports a slow report. Which DBACOCKPIT Performance function shows what's running RIGHT NOW versus what was historically expensive, and what window does the expensive list cover?

A teammate says 'just turn on ST05, it's the HANA SQL trace.' Why is that conflation wrong, and what does ST05 actually trace?

In the ST05 trace display, what does the Explain function give you for a HANA statement, and how is the output read?

You enable the HANA expensive-statements trace but it misses statements that burn CPU or memory while running quickly. Which THREE thresholds define 'expensive,' and what unit traps you?

An admin assumes HANA evicts plans only under cache pressure and turns eager eviction off to retain plans. What are they missing about how eviction can work?

Two PlanViz operators: one has the highest inclusive time, another the highest exclusive time. Which is the real tuning candidate, and why is inclusive time misleading?

After a large data load doubles a table's size, several join-heavy queries suddenly pick worse plans. What invalidated the optimizer's input, and why is there no stats job to blame?

Two highly correlated columns (e.g., city and postal code) make the optimizer badly overestimate combined cardinality, so it picks slow plans. What user-defined statistic fixes this, and why do per-column stats fail here?

To trim the expensive-statements trace, someone proposes turning off trace_parameter_values. Why is that a bad idea for diagnosis?

You want a statement's plan but must NOT run it (it's expensive or modifies data). Which is the lightweight tool, and where does its result land?

An admin scripts a post-load 'UPDATE STATISTICS' step for HANA like they did on their old DB. What's wrong with that plan, concretely?

You leave an ST05 trace running for an hour on a busy program to be safe, then find the earliest records are missing. Why, and which parameter governs this?

You're sizing the SQL plan cache for a NON-ABAP HANA system. Which rule of thumb applies, and what's the giveaway that you'd be using the wrong one?

You want to disable a single expensive-statement threshold (say, memory) while keeping the others. What value do you set, and which value must you NEVER use?

In M_SQL_PLAN_CACHE the average/min/max execution-time and memory columns all read -1. What's not enabled, and what do you switch on?

You captured a PlanViz (.plv) file from the SAP HANA cockpit and try to analyze it there. Why does that fail, and where must the analysis happen?

You activate an ST05 trace across all app servers, but a colleague is already running a system trace on one of them. What's the difference between <ALL> and <ENFORCE>, and which one risks clobbering their work?

You're sizing the SQL plan cache for an ABAP-based HANA system. Which rule of thumb applies, and how do you get the input number?

For which SQL statement types is SAP HANA EXPLAIN PLAN allowed?

On HANA 2.0 you want a known-good plan to stick. Why is plan pinning the wrong tool to reach for, and what replaces it?