ABAP Developmentadvanced

Open SQL Part 8: Performance Analysis with ST05, SAT and SQL Monitor

Key takeaway

Find slow SQL with evidence: record an SQL trace in ST05, read the statement summary and explain plan, detect identical selects and missing indexes, profile with SAT, and find system-wide hotspots with the SQL Monitor (SQLM).

Explanation

📌 KEY POINTS
✅ ST05 records every database statement of a user: duration, rows, number of executions.
✅ Look for: long single statements, many executions of the same statement (SELECT in loop), identical selects, full table scans.
✅ SAT shows where total runtime goes (ABAP vs DB) and which statements dominate.
💡 SQL Monitor (SQLM) finds expensive statements system-wide in production with low overhead.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–7 gave rules for good SQL. This part is about proving where time is spent — before and after a fix.

━━━━━━━━━━━━━━━━━━━━
▶ 1. ST05 — SQL TRACE

ST05 → activate SQL trace (for your user, or the user of the job).
Run the program (small, representative selection).
Deactivate, display trace.

Useful views:

Statement summary / structure-identical statements: groups by SQL text — shows how often each statement ran and total time.
Identical selects: exactly the same statement with the same values executed several times — pure waste (cache the result).
Explain plan: how the database executes the statement (index used? full scan?).
Also trace buffer access (table buffer) and RFC where relevant.

━━━━━━━━━━━━━━━━━━━━
▶ 2. WHAT TO LOOK FOR

One slow statement with few executions → missing/wrong index, non-selective WHERE, huge result.
Fast statement executed 50,000 times → SELECT inside a loop → bulk read + internal table.
Many rows fetched but few used → missing WHERE conditions or SELECT *.
FOR ALL ENTRIES with many small statements → duplicates in driver, or JOIN alternative.

━━━━━━━━━━━━━━━━━━━━
▶ 3. SAT — RUNTIME ANALYSIS
SAT (formerly SE30) measures the whole program: time per method/statement, split ABAP vs database. Use it when you don't know whether the database is the problem at all. ST12 combines SAT and ST05 in one tool (single transaction analysis).

━━━━━━━━━━━━━━━━━━━━
▶ 4. SQL MONITOR (SQLM)
Activated system-wide (production) for a period, it aggregates executions, time and records per statement and calling code location. Great to find the top 20 expensive statements before a HANA migration or tuning project.

━━━━━━━━━━━━━━━━━━━━
▶ 5. THE METHOD
Measure → identify the top contributor → fix → measure again. Keep before/after numbers in the change documentation.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Trace the "bad" part of the code example with ST05 and look at the statement summary.
Trace the "good" part and compare executions and time.
Run both with SAT and compare the database share.

✅ CHECKPOINT: you tune SQL based on traces, not assumptions.

Code example

ABAP Code

REPORT zosql_part8.* Trace this program with ST05 (SQL trace) and SAT, then compare. DATA: gt_book TYPE STANDARD TABLE OF sbook,      gs_book TYPE sbook,      gv_name TYPE s_custname. TYPES: BEGIN OF ty_cust, id TYPE s_customer, name TYPE s_custname, END OF ty_cust.DATA gt_cust TYPE HASHED TABLE OF ty_cust WITH UNIQUE KEY id.DATA gs_cust TYPE ty_cust.DATA lt_ids  TYPE STANDARD TABLE OF sbook. PARAMETERS p_bad AS CHECKBOX DEFAULT 'X'. START-OF-SELECTION.  SELECT * FROM sbook INTO TABLE gt_book UP TO 5000 ROWS WHERE carrid = 'LH'.   IF p_bad = abap_true.* ===== BAD: SELECT SINGLE in a loop -> 5,000 executions in ST05 =====    LOOP AT gt_book INTO gs_book.      SELECT SINGLE name FROM scustom INTO gv_name WHERE id = gs_book-customid.    ENDLOOP.  ELSE.* ===== GOOD: one bulk read + hashed lookup -> 1 (FAE: few) executions =====    lt_ids = gt_book.    SORT lt_ids BY customid.    DELETE ADJACENT DUPLICATES FROM lt_ids COMPARING customid.    IF lt_ids IS NOT INITIAL.      SELECT id name FROM scustom INTO TABLE gt_cust        FOR ALL ENTRIES IN lt_ids WHERE id = lt_ids-customid.    ENDIF.    LOOP AT gt_book INTO gs_book.      READ TABLE gt_cust INTO gs_cust WITH TABLE KEY id = gs_book-customid.    ENDLOOP.  ENDIF. * ===== What to check in ST05 =====* Statement summary : executions | total time | rows per execution* Identical selects : same statement + same values repeated -> cache* Explain plan      : index used? full table scan?* SAT               : % time ABAP vs DB; top statements / methods* SQLM (production) : top expensive statements system-wide + code location

Real project scenario

A delivery report was "slow since go-live", and the team planned to add database indexes. An ST05 trace showed no slow single statement at all — but 180,000 executions of the same SELECT SINGLE on the customer table inside a loop, plus 40,000 identical selects.
Replacing them with one bulk read into a hashed table reduced database time from 22 minutes to 15 seconds. No index was needed; without the trace, the team would have tuned the wrong thing.

Common mistakes

Tuning without a trace.
Tracing with unrealistic tiny selections.
Only looking at single slow statements, not execution counts.
Adding indexes before checking the access pattern.
Not re-measuring after the fix.

Best practices

Trace with ST05 using realistic data; read the statement summary.
Check identical selects and executions per statement.
Use explain plan for slow single statements.
Use SAT/ST12 when unsure whether DB is the bottleneck.
Use SQLM for production-wide hotspots; document before/after.

Interview angle

Questions: "How do you analyze a slow ABAP report?", "What do you look for in an ST05 trace?", "What is the SQL Monitor?"
Strong answer: start with SAT/ST12 to see ABAP vs DB share, then ST05: statement summary (executions, time, rows), identical selects, explain plan for index usage; typical findings are SELECTs in loops, missing WHERE/field lists, FAE issues, missing indexes; SQLM aggregates expensive statements system-wide with code locations; always measure before and after.

Practice this lesson

Quick check

1. In ST05, what does "identical selects" indicate?

2. An ST05 trace shows one fast statement executed 50,000 times. What is the most likely cause?

3. Which is the recommended replacement for SELECT inside LOOP?

Interview questions

1. You are debugging a SELECT query using ST05 (SQL Trace). Walk through the key metrics you would examine to diagnose performance issues and identify the root cause.

Answer it in your own words first, then compare.

2. A slow report was blamed on ALV rendering. How do you prove where the time goes?

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.