Performance Tuning: Expensive Statements, Plan Visualizer & Memory Triage
Master performance triage in SAP HANA: configuring Expensive Statement Tracing, deciphering Plan Visualizer (PlanViz) execution operators, resolving Out-of-Memory (OOM) dumps, and terminating runaway sessions safely.
Advanced (Senior DBA)Performance Tuning~24 min studyHANA 2.0 (All SPS)
Core Learning Objectives
- 1.Enable and configure Expensive Statement Tracing dynamically in global.ini.
- 2.Analyze the critical diagnostic columns in SYS.M_EXPENSIVE_STATEMENTS.
- 3.Interpret Plan Visualizer (PlanViz) operators, dominant execution paths, and engine switching overhead.
- 4.Investigate Out-of-Memory (OOM) trace files, Global Allocation Limits, and heap allocators.
- 5.Execute emergency session cancellation and disconnection.
Prerequisites:hana-fundamentals-overviewhana-internals-delta-merge
Definition & Architectural Rationale
Technical Definition:
An Expensive SQL Statement is any query whose execution duration, CPU cycles, memory allocation, or lock wait time exceeds configured performance thresholds, risking server starvation, user freezes, or Out-of-Memory crashes.
Why this component exists:
In large ERP environments with thousands of concurrent users and custom ABAP reports, poorly indexed queries or missing join conditions can easily request billions of rows or allocate tens of gigabytes of RAM. Identifying and tuning these queries proactively is the primary responsibility of a senior DBA.
Internal Working & Mechanics
1. Expensive Statement Tracing Architecture:
- Configured in global.ini under [expensive_statement].
- When active, the SQL executor records statements exceeding configured threshold_duration (e.g. 2,000,000 microseconds = 2 seconds) or threshold_memory (e.g. 1 GB) into SYS.M_EXPENSIVE_STATEMENTS.
- Dynamic activation: Can be enabled live via SQL without restarting the database.
2. Plan Visualizer (PlanViz) Deep Dive:
When an expensive statement hash is identified, open PlanViz in Cockpit/Studio to inspect:
- Dominant Path (Red/Orange Bars): The chain of operators consuming the highest percentage of execution time.
- Table Scan vs. Index Lookup: A Table Scan on a 200M row column table indicates missing secondary indexes or non-sargable WHERE predicates (e.g. function calls on indexed columns).
- Materialization Overhead: The query creates massive intermediate tables in temporary memory instead of streaming results in a pipeline.
- Engine Switching: Constant jumping back and forth between the Row Store Engine and Column Store Engine, incurring serialization overhead.
3. Out-of-Memory (OOM) Mechanics:
- Global Allocation Limit (GAL): Configured in global.ini [memorymanager] allocationlimit. Defines the ceiling for all database heaps.
- When a query requests a memory block that exceeds GAL, indexserver triggers an OOM event, generates an oom_*.trc dump file, and terminates the offending query (or crashes if GAL is breached by kernel allocation).
- OOM Trace Analysis: Inspect TOP ALLOCATORS (e.g. Pool/RowEngine, Pool/ColumnStore, Pool/PersistenceManager) and identify the offending Statement String and User.
Architecture Flow Diagram
Mermaid DiagramPerformance Tuning: Expensive Statements, Plan Visualizer & Memory Triage
Architectural Flow: Performance troubleshooting moves rapidly from host resource verification to active running query triage, emergency session rollback if memory is threatened, and deep execution plan analysis in PlanViz.
Production SQL Diagnostics
Configure and Enable Expensive Statement Tracing in global.iniModifying (State Change)
Dynamically enables expensive statement tracing with a 2-second threshold and 1GB memory threshold.
Both
Privileges: INIFILE ADMIN
Target Engine: HANA 2.0 (All SPS)
-- Step 1: Enable Expensive Statement Tracing
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM')
SET ('expensive_statement', 'enable') = 'on' WITH RECONFIGURE;
-- Step 2: Set execution runtime threshold to 2 seconds (2,000,000 microseconds)
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM')
SET ('expensive_statement', 'threshold_duration') = '2000000' WITH RECONFIGURE;
-- Step 3: Set memory allocation threshold to 1 GB (1,073,741,824 bytes)
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM')
SET ('expensive_statement', 'threshold_memory') = '1073741824' WITH RECONFIGURE;
DBA Interpretation: Statements exceeding 2 seconds or 1GB RAM will be logged to SYS.M_EXPENSIVE_STATEMENTS automatically.
Top 10 Memory-Heavy Statements (OOM Culprits)Safe (Read-Only)
Isolates statements with the largest peak memory footprints risking Out-of-Memory conditions.
Tenant
Privileges: MONITORING
Target Engine: HANA 2.0 (All SPS)
SELECT TOP 10
STATEMENT_HASH,
USER_NAME,
APPLICATION_NAME,
ROUND(MEMORY_SIZE / 1024 / 1024 / 1024, 2) AS MEM_ALLOC_GB,
ROUND(DURATION_MICROSEC / 1000000, 2) AS RUNTIME_SEC,
RECORDS,
SUBSTR(STATEMENT_STRING, 1, 150) AS SHORT_SQL
FROM SYS.M_EXPENSIVE_STATEMENTS
ORDER BY MEMORY_SIZE DESC;
Key Diagnostic Columns
MEM_ALLOC_GB:Peak memory allocated by this querySTATEMENT_HASH:Unique identifier for query planSHORT_SQL:Preview of query textExpected Diagnostic Output
STATEMENT_HASH | USER_NAME | APPLICATION_NAME | MEM_ALLOC_GB | RUNTIME_SEC | RECORDS | SHORT_SQL ----------------------------------------------------------------------------------------------------------------------------- f8102914bb10294109204910a8f9210c | BATCH_REP | ABAP:Z_FIN_REP | 34.50 | 180.20 | 25000000 | SELECT * FROM BSEG INNER JOIN BKPF...
DBA Interpretation: Z_FIN_REP allocated 34.5 GB of RAM in a single statement. If statement_memory_limit were not configured, this could crash a 64GB tenant!
Troubleshooting & Root Cause Analysis
Production Best Practices & Operational Risks
Mandatory Best Practices
- Configure [memorymanager] statement_memory_limit = 40 (or 25% of GAL) in tenant indexserver.ini to automatically abort individual rogue queries.
- Do not keep expensive_statement tracing enabled with threshold_duration = 0; capturing every query introduces significant logging overhead.