PUBLIC

SAP [nom] Platform 2.0 SPS 04

Document Version: 1.1 – 2019-10-31

SAP [nom]

Analysis Guide

2019 SAP SE or an SAP affiliate company. All rights reserved.

THE BEST RUN

Content

1 · SAP [nom] Analysis Guide. . . . . . . . . . . . . . . . . . . . . . . . . . 6

2 · Analyzing Generic Symptoms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9

2.1 · [nom] High Resource Utilization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9

2.2 · Common Symptoms [nom] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Slow System-wide [nom] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Slow Individual SQL Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Frequent Out of Memory (OOM). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

3 · Root Causes [nom] Solutions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

3.1 · Memory Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Memory Information in SAP [nom] Cockpit. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Memory Information from Logs [nom] Traces. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Memory Information from SQL Commands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Memory Information from Other Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Root Causes of Memory Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

Transparent Huge Pages on Linux. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

3.2 · CPU Related Root Causes [nom] Solutions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

Indicators of CPU Related Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

Analysis of CPU Related Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

Resolving CPU Related Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Retrospective Analysis of CPU Related Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Controlling Parallel Execution of SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

Controlling CPU Consumption. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

3.3 · Disk Related Root Causes [nom] Solutions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

Reclaiming Disk Space. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Analyze [nom] Resolve Internal Disk-Full Event (Alert 30). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

3.4 · I/O Related Root Causes [nom] Solutions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Analyzing I/O Throughput [nom] Latency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Savepoint [nom] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

3.5 · Configuration Parameter Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Issues with Configuration Parameter log_mode (Alert 32 [nom] 33). . . . . . . . . . . . . . . . . . . . . . . .63

3.6 · Backup [nom] Recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

3.7 · Delta Merge. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Inactive Delta Merge. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Indicator for Large Delta Storage of Column Store Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .70

Failed Delta Merge. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

SAP [nom] Analysis Guide

2 · PUBLIC · Content

Delta Storage Optimization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

3.8 · SAP Web IDE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

Post-Installation Problems with Web IDE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

Web IDE Common Issues with Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

3.9 · [nom] BW on [nom]. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

3.10 · [nom] Multi-Dimensional Services Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

3.11 · [nom] Tips for the Calculation Engine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .94

Native [nom] Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

3.12 · License Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

System Locked Due to Missing, Expired, or Invalid License. . . . . . . . . . . . . . . . . . . . . . . . . . . . .98

License Problem Identification [nom] Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98

Resolution of License Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

3.13 · Security-Related Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

[nom] Authorization Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

[nom] Problems with User Name/Password Authentication. . . . . . . . . . . . . . . . . . . .106

[nom] Problems with User Authentication [nom] SSO. . . . . . . . . . . . . . . . . . . . . . . . . 108

3.14 · Transactional Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111

Blocked Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

[nom] Blocked Transaction Issues that Occurred in the Past. . . . . . . . . . . . . . . . . . . . 116

Multiversion Concurrency Control (MVCC) Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116

Version Garbage Collection Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

3.15 · Statement [nom] Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

SQL Statement Optimization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122

Analysis of Critical SQL Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125

Optimization of Critical SQL Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

3.16 · Application [nom] Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .139

SQL Trace Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

Statement Measurement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140

Data Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141

Source Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142

Technical Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

3.17 · System Hanging Situations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144

Transparent Huge Pages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145

CPU Power Saving. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146

3.18 · Troubleshoot System Replication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147

Replication [nom] Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148

Setup [nom] Initial Configuration Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152

Intermittent Connectivity Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

LogReplay: Managing the Size of the Log File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .158

3.19 · Network [nom] Connectivity Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161

Network [nom] Analysis on Transactional Level. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161

SAP [nom] Analysis Guide

Content · PUBLIC · 3

Stress Test with NIPING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163

Application [nom] Database Connectivity Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164

SAP [nom] System Replication Communication Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . 166

SAP [nom] Inter-Node Communication Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168

3.20 · SAP [nom] Dynamic Tiering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170

Tools [nom] Tracing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170

Query Plan Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170

Data Loading [nom] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172

4 · Tools [nom] Tracing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174

4.1 · System [nom] Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .174

Thread Monitoring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174

Blocked Transaction Monitoring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177

Session Monitoring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178

Job Progress Monitoring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179

Load Monitoring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180

4.2 · SQL Statement Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180

Analyzing SQL Traces. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

Analyzing Expensive Statements Traces. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

Analyzing SQL Execution with the SQL Plan Cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189

4.3 · Query Plan Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190

Analyzing SQL Execution with the Plan Explanation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191

Analyzing SQL Execution with the Plan Visualizer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195

4.4 · Result Cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210

Static Result Cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .211

Dynamic Result Cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216

4.5 · Tracing for Calculation View Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223

4.6 · Advanced Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224

Analyzing Column Searches (qo trace). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .224

Analyzing Table Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226

SQL Plan Stability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227

4.7 · Additional Analysis Tools for Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

[nom] Trace. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

Kernel Profiler. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233

Diagnosis Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234

Analysis Tools in SAP [nom] Web-based Developer Workbench. . . . . . . . . . . . . . . . . . . . . . . . 235

5 · SAP [nom] Database monitoring with Solution Manager. . . . . . . . . . . . . . . . . . . . . . . . . . . . 238

6 · Alerts [nom] the Statistics Service. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242

6.1 · Reference: Alerts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243

6.2 · Alerts Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262

SAP [nom] Analysis Guide

4 · PUBLIC · Content

7 · Important Disclaimer for Features in SAP [nom]. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295

SAP [nom] Analysis Guide

Content · PUBLIC · 5

1 · SAP [nom]

[nom] Analysis Guide

With SAP [nom], you can analyze data at incredible speeds, for example, with scans of 1 billion rows per second per core [nom] join [nom] of 10 million rows per second. However, such results are only possible if the system is monitored [nom] issues are kept to a minimum.

This guide describes the measures you can take to identify [nom] resolve specific [nom] issues [nom] shows you how to enhance the [nom] of your SAP [nom] database in the following areas:

• Host resources (CPU, memory, disk)
• Size [nom] growth of data structures
• Transactional problems
• SQL statement [nom]
• Security, authorization, [nom] licensing
• Configuration.

Prerequisites

• This guide assumes knowledge of the relevant functionality of the SAP [nom] database (knowledge which can be gained from [nom] training courses such as HA100, HA200).
• Access to the administration tool SAP [nom] cockpit (or alternatively SAP [nom] studio) is required.

Overview

Analyzing Generic Symptoms

This section of the [nom] guide helps you to find out about causes of generic problems such as:

• slow system-wide [nom]
• slow individual SQL statements
• frequent out-of-memory (OOM) situations

Furthermore, you are directed to sections of this guide that contain more specific root causes.

SAP Note references lead you to possible solutions.

Analyzing Generic Symptoms [page 9]

Root Causes & Solutions

In this section of the [nom] guide you find concrete root causes for problems together with possible solutions. Some of the areas covered are:

SAP [nom] Analysis Guide

6 · PUBLIC · SAP [nom] Analysis Guide

• Memory Problems
• CPU Related Root Causes [nom] Solutions
• License Issues
• Statement [nom] Analysis

You may find the SAP Notes very useful for solving your issue as they contain detailed explanations [nom] step-by-step instructions, for example.

Root Causes [nom] Solutions [page 22]

Tools [nom] Tracing

This section of the [nom] guide presents monitoring tools which can be used for analyzing [nom]

tracing certain issues.

Tools [nom] Tracing [page 174]

Apart from those more specific monitoring tools discussed here, there are important tools for administrators

[nom] developers in general:

• SAP [nom] cockpit

Tip

For the documentation of the latest SAP [nom] cockpit support package (SP), see https://

[lien]

• SAP [nom] database explorer

SAP [nom] Database Explorer

• SQL analyzer

Analyzing Statement [nom]

• Support Log Assistant

The Support Log Assistant is a tool that allows you to automatically scan [nom] analyze text files such as logs, configuration files or traces. The tool will then suggest solutions to known issues found in the files [nom]

highlight important details that it finds. The tool is integrated into the incident logging procedure [nom] is also available as a standalone tool; the following link is to a Support Portal getting started page which also gives direct access to the Support Log Assistant.

Support Log Assistant

Alerts

Alert checkers run in the background [nom] you are notified in case of potentially critical situations arising in your system. In the SAP [nom] cockpit, you can easily see in which areas you might need to take some action.

Reference: Alerts [page 243]

SAP Notes

SAP Notes are used to give detailed supplementary customer support information in addition to the formal set of published documents. This [nom] guide includes many references to relevant SAP Notes. The Alerts reference section, for example, gives links to corresponding notes for each system alert. Some other

FAQ-format SAP Notes which you may find useful are listed here:

• SAP Note 2000003 · - FAQ: SAP [nom]
• SAP Note 1999997 · - FAQ: SAP [nom] Memory

SAP [nom] Analysis Guide

SAP [nom] Analysis Guide · PUBLIC · 7

• SAP Note 2186744 · - FAQ: SAP [nom] Parameters

Guided Answers

Guided Answers is an interactive online support tool to help users to diagnose [nom] solve problems using decision trees. It covers many SAP products including SAP [nom] offers a set of step-by-step problem-solving online documents each one designed to address a specific topic. Guided Answers is available in the SAP Support portal at the following address:

[lien]

This [nom] guide includes links to specific trees where relevant. The following tree is a general high-level [nom] tree for SAP [nom]:

[lien]

Further Resources

The following SAP [nom] documents are important resources for working with SAP [nom] are often referred to in this guide:

• SAP [nom] Administration Guide
• SAP [nom] SQL [nom] System Views Reference

In particular, the SAP [nom] Administration Guide gives general details on using the administration tools SAP

[nom] cockpit [nom] SAP [nom] studio.

There is a central online portal for a variety of support resources for SAP products which is available from the

SAP ONE Support Launchpad · Software Downloads : [lien]

softwarecenter

In the SAP Community Network (SCN) you can find many support resources online including wikis, blogs, reference materials [nom] so on. This SCN wiki page, for example, provides links to many specialist topics: SAP

[nom] In-Memory [nom] Guide .

Both SAP [nom] Academy [nom] SAP Support offer YouTube channels with a wide range of support materials in video format:

• [lien]
• [lien]

SAP [nom] Analysis Guide

8 · PUBLIC · SAP [nom] Analysis Guide

2 · Analyzing Generic Symptoms

The purpose of this section of the document is to help you to find the probable root cause of some generic problems [nom] refer you to more detailed sections of the SAP [nom] Analysis

Guide to proceed with your analysis.

[nom] issues may be difficult to diagnose; problems may be rooted in a number of seemingly unrelated components. Checking for system alerts is a good starting point if you experience any trouble with your SAP

[nom] system. If the system issues an alert, refer to the Reference: Alerts section to find the part of this guide, an SAP Note or Knowledge Base Article which addresses the problem.

However, alerts are configurable (see Memory Problems for information on configuring alerts) [nom] do not cover all aspects of the system, problems can still occur without triggering an alert. This section therefore describes some generic symptoms which you may observe [nom] helps you to analyze the underlying problem.

Related Information

Memory Problems [page 22]

Reference: Alerts [page 243]

2.1 · [nom] High Resource Utilization

By observing the general symptoms shown by the system such as poor [nom], high memory usage, paging or column store unloads we can start to narrow down the possible causes as a first step in analyzing the issue.

High Memory Consumption

You observe that the amount of memory allocated by the SAP [nom] database is higher than expected. The following alerts indicate issues with high memory usage:

• Memory usage of name server (Alert 12)
• Total memory usage of Column Store tables (Alert 40)
• Memory usage of services (Alert 43)
• Memory usage of main storage of Column Store tables (Alert 45)
• Runtime dump files (Alert 46)

See the section Memory Problems for information on analyzing the root cause.

SAP [nom] Analysis Guide

Analyzing Generic Symptoms · PUBLIC · 9

Out-of-Memory Situations

You observe trace files or error messages indicating an Out-of-Memory (OOM) situation.

See the section Memory Problems for information on analyzing the root cause.

Paging on Operating System Level

You observe that paging is reported on operating system level.

See the section Memory Problems for information on analyzing the root cause.

Column Store Unloads

You observe unloads in the column store. The following alerts indicate issues with high memory usage:

• Column store unloads (Alert 55)

See the section Memory Problems for information on analyzing the root cause.

Permanently Slow System

Issues with overall system [nom] can be caused by a number of very different root causes. Typical reasons for a slow system are resource shortages of CPU, memory, disk I/O [nom], for distributed systems, network [nom]

Check Overview Monitoring [nom] Administration [nom] Monitor for either Memory, CPU or Disk

Usage. If you see a constant high usage of memory or CPU, proceed with the linked sections Memory Problems or CPU Related Root Causes [nom] Solutions respectively. I/O Related Root Causes [nom] Solutions provides ways to check for disk I/O related problems. In case of network [nom] issues, have a look at the Monitor

Network page accessible from the Monitoring group in the SAP [nom] cockpit.

Note that operating system tools can also provide valuable information on disk I/O load. Basic network I/O data is included in the Load graph [nom] in the M_SERVICE_NETWORK_IO system view, but standard network analysis tools can also be helpful to determine whether the network is the main bottleneck. If [nom]

issues only appear sporadically, the problem may be related to other tasks running on the database at the same time.

These include not only maintenance related tasks such as savepoints (disk I/O, see I/O Related Root Causes

[nom] Solutions) or remote replication (network I/O), but also SQL statements dispatched by other users, which can block a lot of resources. In the case of memory, this can lead to unloads of tables, which affects future SQL statements, when a table has to be reloaded into memory. In this case, see Memory Problems as well. Another reason for poor [nom], which in many cases cannot be detected by the SAP [nom] instance itself, are other processes running on the same host that are not related to SAP [nom]. You can use the operating system tools to check for such processes. Note that SAP only supports production systems running on validated hardware.

SAP [nom] Analysis Guide

10 · PUBLIC · Analyzing Generic Symptoms

Slow Individual SQL Statements or with Increasingly Long Runtimes

Issues with the [nom] of a particular statement can be caused by a number of very different root causes.

In principle, a statement can trigger all the resource problems that also lead to an overall slowdown of the system, so most of the previous information also applies to statement [nom] In addition, statement

[nom] can suffer from transactional problems, that is, blocked transactions. Blocked transactions can be checked in the Threads tile or on the Blocked Transactions page accessible from the Monitoring group. For

[nom], proceed with Transaction Problems.

If the runtime of a statement increases steadily over time, there could be an issue with the delta merge operation. Alerts should be issued for most problems occurring with the delta merge, but since they depend on configurable thresholds, this is not always the case. For [nom], proceed with Delta Merge. If you have none of the above problems, but the statement is still too slow, a detailed Statement [nom] Analysis might reveal ways to optimize the statement. However, some queries are inherently complex [nom] require a lot of computational resources [nom] time.

Related Information

Memory Problems [page 22]

CPU Related Root Causes [nom] Solutions [page 39]

Disk Related Root Causes [nom] Solutions [page 48]

I/O Related Root Causes [nom] Solutions [page 55]

M_SERVICE_NETWORK_IO

Transactional Problems [page 111]

Delta Merge [page 68]

Statement [nom] Analysis [page 121]

2.2 · Common Symptoms [nom]

Typical symptoms [nom] the related [nom] information are described in this section.

System-side slow [nom], slow individual statement [nom], [nom] OOM problems are issues that you might experience while using the SAP [nom] database. For each section, this document will cover the known symptoms [nom] the corresponding [nom] steps to follow depending on the causes.

SAP [nom] Analysis Guide

Analyzing Generic Symptoms · PUBLIC · 11

2.2.1 Slow System-wide [nom]

Slow system-wide [nom] issues are problems that could be caused by excessive use of CPU, database resource locks or incorrect configuration of OS parameters.

Generally, when you encounter a [nom] issue, you may see these symptoms in SAP [nom] cockpit:

• Continually high CPU usage according to OS commands or load graph (visible from CPU Usage tile -

[nom] Monitor)

• Many pending or waiting threads in the thread view (details visible from the Threads tile).

To look for the cause at the operating system level refer to the topic System Appears to Hang with High System

CPU Usage.

If the [nom] issue persists or if it recurs sporadically you may need to contact Support to analyze the root cause. In this case please generate at least two runtime dumps at 3 minute intervals while the system

[nom] is slow for further investigation. For details refer to SAP Note 1813020 - How to generate a runtime dump on SAP [nom] or the Guided Answer How to generate a runtime dump.

Related Information

System Appears to Hang with High System CPU Usage [page 13]

SAP Note 1813020

How to generate a runtime dump (Guided Answer)

2.2.1.1 · System Appears to Hang with no new Connections

[nom] no Response from DML

In cases where logs cannot be written, all DML statement will fall into wait status. This can lead to a failure of opening new connections because the system internally executes DML statements during the process.

Typically, a full log volume is the cause for this.

Root cause: Log volume full either caused by disk full or quota setting

Required action:

• Check DISKFULL event in indexserver trace or
• Run "df -h" in OS shell or
• Check quota setting in file system
• Then, follow the procedure described in SAP Note 1679938 - Log Volume is full.

You can find additional information in SAP Note 2083715 - Analyzing log volume full situations.

Root cause: log volume full caused by failure of log backup

Required action:

• Check backup.log (located at /usr/sap/<SID>/HDB<Instance#>/<Host>/trace ) to see whether it includes ERROR in log backup. Check M_BACKUP_CATALOG, M_LOG_SEGMENTS.

SAP [nom] Analysis Guide

12 · PUBLIC · Analyzing Generic Symptoms

• If log backup uses backint, please check backint.log (located at /usr/sap/<SID>/HDB<Instance#>/ <Host>/trace ) to see whether it includes ERROR information, [nom] contact backint vendor support. With certain revision [nom] conditions, the conflict between savepoint lock [nom] DML lock blocks subsequent statements when long running update/insert statements exist. Please contact SAP support if you encounter the next case described below.

Root cause: Savepoint lock conflict with long running update

Required action:

• Collect runtime dump as per SAP Note 1813020 - How to generate a runtime dump on SAP [nom] look for the following call stacks in many threads.

…

DataAccess::SavepointLock::lockShared(…)

DataAccess::SavepointSPI::lockSavepoint(…)

…

[nom] one of the following call stacks that is in the savepoint phase.

…

DataAccess::SavepointLock::lockExclusive()

DataAccess::SavepointImpl::enterCriticalPhase(…)

…

• Check whether the symptoms match the description in SAP Note 2214279 - Blocking situation caused by waiting writer holding consistent change lock. If so, apply the parameter in the Note.
• Create a support ticket attaching a runtime dump for further analysis.

Related Information

SAP Note 1679938

SAP Note 2083715

SAP Note 1813020

SAP Note 2214279

2.2.1.2 · System Appears to Hang with High System CPU

Usage

The SAP [nom] database is an in-memory database [nom] by its nature, it consumes large amounts of memory.

Therefore, some [nom] issues of SAP [nom] can be caused by the OS's memory management.

For this case, you can see high usage of SYSTEM CPU that can be monitored in the [nom] Monitor,

available from · SAP [nom] cockpit · Monitoring [nom] Administration · CPU Usage .

Root cause: Problem caused by the configuration of transparent huge page

Required action:

• Check Transparent Huge Page whether it is set to [never] by running the command "cat /sys/kernel/mm/ transparent_hugepage/enabled".

SAP [nom] Analysis Guide

Analyzing Generic Symptoms · PUBLIC · 13

• Apply KBA 2031375 - SAP [nom]: Transparent HugePages (THP) setting on Linux.

Root cause: Problem caused by the configuration of OS page cache

Required action:

• Check sar file ( /var/log/sa ) whether kbcached usage ( sar -r ) is higher than 10% of physical memory [nom] high page in/out occurred.
• Check [nom] apply SAP Note 1557506 - Linux paging improvements.

Root cause: Problem caused by translation lookaside buffer (TLB) shootdown

Required action:

• Check plan trace. For more information, see SAP Note 2206354 - SAP [nom] DB: High System CPU Consumption Caused by Plan Trace.

Root cause: Due to high context switches (High SYS CPU) by many SqlExecutor threads

Required action:

• Check [nom] Monitor [nom] indexserver.ini -> sql -> sql_executors / max_sql_executors [nom] refer to Controlling Parallelism of SQL Statement Execution.

Root Cause: Due to high context switches (High SYS CPU) by many JobExecutor threads

Required action: Check that the following parameters are set to be bigger than the number of logical CPU cores:

• indexserver.ini -> parallel -> num_cores (<=SPS07) [nom] refer to Controlling Parallelism of SQL Statement Execution
• global.ini/indexserver.ini -> execution -> max_concurrency (>SPS08)

Related Information

Controlling Parallel Execution of SQL Statements [page 42]

SAP Note 2031375

SAP Note 1557506

SAP Note 2206354

System Hanging Situations [page 144]

2.2.1.3 · Slower Response with High User CPU Usage

The [nom] may degrade with increased usage of the CPU [nom] applications.

Root cause: Not many executor threads but high CPU in a few nodes [nom] a few tables accessed

Required Action: In an SAP Business Warehouse system, check for non-even partitioning of huge column tables. For more information, see SAP Note 1819123 - BW on SAP [nom] SP5: landscape redistribution [nom]

Indicator for Large Delta Storage of Column Store Tables.

Root Cause: [nom] degradation by huge MVCC versions

SAP [nom] Analysis Guide

14 · PUBLIC · Analyzing Generic Symptoms

Required Action: Check for KPI Active Version in the [nom] Monitor to find the MVCC garbage blocker, then kill it via the Threads tile on the Overview page. For more information, see [nom] Blocked

Transactions.

Related Information

Indicator for Large Delta Storage of Column Store Tables [page 70]

[nom] Blocked Transactions [page 114]

SAP Note 1819123

2.2.2 Slow Individual SQL Statements

This section looks at the causes of slow individual SQL statements although there is no significant [nom]

issue on system level [nom] the associated [nom] steps.

If the following doesn’t help to resolve the problem, see Getting Support. In this case, to analyze further, collect the explain plan, plan visualizer file, [nom] trace [nom] catalog export. Also refer to SQL Statement

Analysis [nom] Query Plan Analysis for more information. SAP KBA 2000002 - FAQ: SAP [nom] SQL Optimization explains general information about SQL optimization.

Related Information

SQL Statement Analysis [page 180]

Query Plan Analysis [page 190]

SAP Note 2000002

Getting Support

2.2.2.1 · A Statement is Sometimes Slow [nom] Sometimes

Fast

There are a number of things to check when you experience inconsistent query execution time.

Check the following possibilities:

Root Cause: If a related table was unloaded, it takes some time to load tables

Required Action: Check unload trace [nom] execute after table loaded fully. You can refer to LOADED column of M_CS_TABLES.

Root Cause: Query compilation time is long.

Required Action: Check the execution time after adding 'with hint (ignore_plan_cache)' at the end of query.

This hint will always cause the query to be compiled. If a long running compiled query plan has been evicted

SAP [nom] Analysis Guide

Analyzing Generic Symptoms · PUBLIC · 15

frequently from the plan cache, increase the query cache size. For more information, see SQL Plan Cache

Analysis.

Root Cause: Merge status of column table can affect query plan

Required Action: Check MEMORY_SIZE_IN_DELTA, RAW_RECORD_COUNT_IN_DELTA, [nom]

LAST_MERGE_TIME columns of M_CS_TABLES whether there is large amount of data in delta. Check

M_DELTA_MERGE_STATISTICS to see when the last merge occurred. For more information, see Delta Merge.

Related Information

SQL Plan Cache Analysis [page 126]

Delta Merge [page 68]

2.2.2.2 · A Statement is Slower After an Upgrade

After upgrade, the query execution time can be different because of changes in the query execution plan.

Root cause: After an upgrade, a query can have a different plan, which leads to a different execution time.

Required Action: If you have an instance running on an older revision, compare the plan [nom] collect the plan visualizer file. See Getting Support for further help.

Refer also to the SQL Plan Stability section of this guide; this feature offers the option to preserve a query's execution plan by capturing an abstraction of the plan [nom] reusing it after the upgrade to regenerate the original plan [nom] retain the original [nom]

For more information, see Expensive SQL Statements.

Related Information

Expensive SQL Statements [page 32]

Analyzing SQL Execution with the Plan Visualizer [page 195]

SQL Plan Stability [page 227]

Getting Support

2.2.2.3 · A Query on Multiple Nodes can be Slower

In distributed systems, query execution can be routed to other nodes for better [nom] However, there is a chance of having slow [nom] in the case where the network used for transferring intermediate results generated during query execution is slow or where there is an inefficient network configuration.

Root cause: A misconfigured network can lead to slow queries.

SAP [nom] Analysis Guide

16 · PUBLIC · Analyzing Generic Symptoms

Required Action: Check your network configuration [nom] its bandwidth/latency among SAP [nom] servers. For more information see Network View [nom] SAP Note 2183363 - Configuration of SAP [nom] internal network.

Root cause: Statement routing [nom] huge data transfer among distributed nodes can cause differences of query execution times due to the difference of anchor nodes.

Required Action: Check how much data is transferred among distributed nodes [nom] the network [nom]

Consider the locations of joined tables to reduce transferred intermediate result size.

Related Information

Network View [page 203]

[nom] Trace [page 231]

SAP Note 2183363

2.2.2.4 · Slow Select for all Entries (FAE) Query

There are a number of points to check if you have [nom] issues with SELECT FOR ALL ENTRIES (FAE) from an ABAP query.

For FAE on SAP [nom], please generally refer to SAP Note 1662726 - Optimization of select with FOR ALL

ENTRIES on SAP [nom] database.

Root cause: Indexes are missing.

Required Action: Check WHERE clause [nom] check concat indexes for all fields used in WHERE clause.

Root cause: Due to DBSL behavior, slower operator can be chosen.

Required Actions: Apply parameters for ABAP optimization. For more information, see SAP Note 1987132 -

SAP [nom]: Parameter setting for SELECT FOR ALL ENTRIES.

Root cause: When using less than or greater than ('<' or '>') filters in FAE query, it can be slower than having no filter.

Required Actions: Do not use less than or greater than ('<' or '>') filters in an FAE query.

Related Information

SAP Note 1662726

SAP Note 1987132

SAP [nom] Analysis Guide

Analyzing Generic Symptoms · PUBLIC · 17

2.2.2.5 · All Statements Accessing a Specific Table are Slow

In case queries run on specific tables are slow, check if there are too many versions.

Root cause: If there are too many versions of [information personnelle] records, accessing the table can be slow. In this case, the number of system-wide MVCC versions is in acceptable range. To verify further whether there are too many versions for a specific table, check the result of this query:

SELECT * FROM M_RS_TABLE_VERSION_STATISTICS where table_name='mytable';

This can be caused by a cursor unnecessarily being held on a query result or a long-lived transaction without a commit/rollback.

Required Action: Applications should commit as early as possible or close any cursors that are not needed.

Check the application logic to see whether it really needs to update [information personnelle] records frequently.

2.2.3 Frequent Out of Memory (OOM)

If Out Of Memory situations happen frequently, it can also lead to [nom] drop by unloading tables or shrinking memory jobs.

First check Memory Problems [nom] SAP KBA 1999997 - FAQ: SAP [nom] Memory as they provide information on SAP [nom] memory [nom] its problems.

This section introduces common problems [nom] their [nom] steps.

Related Information

Memory Problems [page 22]

SAP Note 1999997

2.2.3.1 · Out of Memory Caused by Sudden Increased

Memory Usage

Check the memory consumption of statements in the event of OOMs caused by suddenly increased memory usage.

Root cause: Huge intermediate results during query processing.

Required Actions: Enable memory tracking by setting the following parameters to on in the global.ini file resource_tracking section.

• enable_tracking = on
• memory_tracking = on

Enable the Expensive Statement Trace by setting the status to Active.

SAP [nom] Analysis Guide

18 · PUBLIC · Analyzing Generic Symptoms

Then, check the memory consumption of statements using M_EXPENSIVE_STATEMENTS.MEMORY_SIZE.

After your analysis you can optimize any problematic queries that were found.

2.2.3.2 · Out of Memory Caused by Continuously Increased

Memory Usage

Check if many statements trigger an Out Of Memory in a system where used memory is continuously increased.

Root cause: Commit/rollback within stored procedure can lead to memory leakages. Do not use exec(“commit”) or exec(“rollback”) within a stored procedure. If this syntax is used, the system cannot reclaim the memory used for query execution because its related transactions are left hanging.

Required Actions: Remove exec(“commit”) or exec(“rollback”). If you would like to use commit/rollback within stored procedure, see SAP [nom] SQLScript Reference for more information.

Root cause: Due to memory leakage caused by a programming error

Required Action: Check the [nom] Monitor in SAP [nom] cockpit Overview Memory Usage to determine whether used memory continuously increases without a significant increase in data size. If you find a suspicious component which keeps allocating memory, create a support ticket attaching a full system dump, mm trace, [nom] the output of _SYS_STATISTICS.HOST_HEAP_ALLOCATORS_BASE.

Related Information

SAP [nom] SQLScript Reference

2.2.3.3 · Out of Memory While High Usage Of Column Store

Components' Allocators

If a system is undersized, Out Of Memory (OOM) can happen frequently.

Root cause: Undersized memory

Required Action:

• Check top allocators in [MEMORY_OOM] section of OOM dump to see whether they are for column store components as described in section 13 of KBA 1999997 - FAQ: SAP [nom] Memory.
• Check the unload trace whether frequent unloading of tables took place.
• Reduce the amount of data in column store or increase physical memory.

SAP [nom] Analysis Guide

Analyzing Generic Symptoms · PUBLIC · 19

Related Information

SAP Note 1999997

2.2.3.4 · Out of Memory Caused by Large Memory Usage of

Statistics Server

This case can happen if the majority of memory used by the statisticsserver is due to many alerts or undersized memory.

Root cause: Due to big STATISTICS_ALERTS_BASE table size, an OOM can occur.

Required Action: Check table size of _SYS_STATISTICS.STATISTICS_ALERTS_BASE [nom] truncate

STATISTICS_ALERTS_BASE from hdbsql based on solution from SAP Note 2170779 - SAP [nom] DB: Big statistics server table leads to [nom] impact on the system.

Root cause: Big Pool/Statistics allocator size

Required Action: Check SAP Note 2147247 - FAQ: SAP [nom] Statistics Server first.

Related Information

SAP Note 2170779

SAP Note 2147247

2.2.3.5 · Out of Memory Occurs due to High Usage of Shared

Memory

Shared memory is space where system information [nom] row store tables are stored.

Check the following if the used amount of shared memory is high.

Root cause: Severely fragmented row store tables

Required action:

• Check whether SHARED_MEMORY in [MEMORY_OOM] -> IPMM short info of OOM dump is unusally high.
• Apply SAP Note 1813245 - SAP [nom] DB: Row store reorganization.

Root cause: Memory shortage is caused by high usage of memory of row store tables in an SAP Business

Warehouse (BW) system

Required action: In an SAP Business Warehouse system the used amount of shared memory is high [nom] SAP

Note 1813245 doesn't recommend row store reorganization, first apply SAP Note 706478 - Preventing Basis tables from increasing considerably, then apply SAP Note 1813245 - SAP [nom] DB: Row store reorganization again.

SAP [nom] Analysis Guide

20 · PUBLIC · Analyzing Generic Symptoms

Root cause: Memory shortage is caused by high usage of memory of row store tables in non-SAP Business

Warehouse (BW) systems

Required Action: Check if you can convert some row store tables into column store tables or archive old data to reduce the memory size, or else increase the system memory.

Related Information

SAP Note 1813245

SAP Note 706478

SAP [nom] Analysis Guide

Analyzing Generic Symptoms · PUBLIC · 21

3 · Root Causes [nom] Solutions

This section provides detailed information on the root causes of problems [nom] their solutions.

System alerts are a good indicator for the underlying problem. The SAP Notes you will be directed to are a source of background information, explanations, alternative options, FAQs or useful SQL statements, for example. Detailed monitoring [nom] administration of the SAP [nom] databases is possible via the SAP [nom]

cockpit [nom] the SAP [nom] database explorer.

3.1 · Memory Problems

This section discusses the analysis steps that are required to identify [nom] resolve memory related issues in the SAP [nom] database.

For more general information on SAP [nom] memory management, see the whitepaper SAP [nom] Memory

Usage Explained which discusses the memory concept in more detail. It also explains the correlation between

Linux indicators (virtual [nom] resident memory) [nom] the key memory usage indicators used by SAP [nom].

Alerts related to memory problems [nom] corresponding SAP Notes are documented in the Alerts reference table

(see category 'Memory'). SAP Note 1840954 – Alerts related to [nom] memory consumption includes information on how to analyze out-of-memory (OOM) dump files.

The SAP [nom] Administration Guide gives details on using the tool SAP [nom] cockpit to analyze memory problems.

In order to understand the current [nom] historic SAP [nom] memory consumption you can use the following tools [nom] approaches:

• Memory information in SAP [nom] cockpit
• Memory information from logs [nom] traces
• Memory information from SQL commands
• Memory information from other tools (see the tools overview in the SAP [nom] Administration Guide).

Related Information

SAP [nom] Memory Usage Explained

SAP Note 1840954

SAP [nom] Administration Guide

SAP [nom] Analysis Guide

22 · PUBLIC · Root Causes [nom] Solutions

3.1.1 Memory Information in SAP [nom] Cockpit

There are a number of sources of information in SAP [nom] cockpit that can assist you in understanding memory utilization.

• Open the Manage Services page from the Overall Database Status tile for high-level information about physical memory, allocation limit, [nom] used memory for each service.
• To get a graphical overview about physical memory, allocation limit, used memory, [nom] resident memory open the [nom] Monitor from the Memory Usage tile.
• Open the Memory Analysis app from the Memory Usage tile for details about memory utilization as well as history information. For example, click the Components tab in the Memory Analysis app to view the used memory grouped by different components like "Statement Execution & Intermediate Results" or "Column Store Tables". When you choose a component, the corresponding historic information of memory usage is displayed by a graph.

3.1.2 Memory Information from Logs [nom] Traces

In case of critical memory issues you can often find more detailed information in logs [nom] trace files.

• Try to identify memory-related errors in the alert trace files in the SAP [nom] database explorer (accessible via the View trace [nom] diagnostic files link). Search for the strings “memory”, “allocat”, or “OOM” (case- insensitive).
• Check if an out-of-memory (OOM) trace file was created.
• Investigate error messages seen on the application side that occurred at times of high memory usage. If the application is an SAP NetWeaver system, good starting points for analysis are System Log (SM21), ABAP Runtime Error (ST22), [nom] Job Selection (SM37).

If help from SAP Customer Support is needed to perform an in-depth analysis, the following information is valuable [nom] should be added to the ticket:

• Diagnosis information (full system info dump). To collect this information, see section Diagnosis Information.
• [nom] trace provides detail information on the system behavior, including statement execution details. To enable this trace, see section [nom] Trace.

The trace output is written to a trace file perftrace.tpt, which must be sent to SAP Customer Support. If specific SAP [nom] system components need deeper investigation, SAP Customer Support can ask you to raise the corresponding trace levels to INFO or DEBUG, rerun the query [nom] then send the indexserver trace files to SAP Customer Support.

To do so choose Trace Configuration in the SAP [nom] database explorer [nom] launch the Database Trace wizard.

The following illustration shows the example of enabling the join engine trace (join_eval). You can enter a search string [nom] then select the component in the indexserver.ini file [nom] change the System Trace Level to the appropriate value. Some trace components (such as debug trace details for join engine) can create many megabytes of trace information [nom] may also require an increase of the values maxfiles [nom]

maxfilesize in the [trace] section of the global.ini file (see also SAP Note 2629103 - SQL Traces Stop

Writing When it Exceeds the Maximum Filesize).

SAP [nom] Analysis Guide

Root Causes [nom] Solutions · PUBLIC · 23

Internal details about SQL statement execution can be collected by enabling the Executor Trace; this provides the highest level of detail [nom] should only be activated for the short time of query execution. After capturing the trace details you can upload the trace file (extrace.py) to SAP Customer Support. This trace is enabled by activating a configuration parameter as described [nom] illustrated here.

Open the Configuration of System Properties view, edit the parameter trace in the [pythontrace] section of the executor.ini file, [nom] change its value to on. When you change the default value, you have the option to set values at each specific layer. The trace parameter is off by default. Click Change Layer to turn the parameter on. You can then change the configuration value for a selected database [nom] for a selected layer:

SAP [nom] Analysis Guide

24 · PUBLIC · Root Causes [nom] Solutions

Related Information

Diagnosis Information [page 234]

[nom] Trace [page 231]

SAP Note 2629103

3.1.3 Memory Information from SQL Commands

There are a number of ways to analyze memory usage based on pre-defined [nom] modifiable SQL queries.

The Statement Library of SAP [nom] database explorer provides a set of tabular views to display the memory consumption of loaded tables based on pre-defined SQL queries:

• The view Component Memory Usage shows the aggregated memory consumption in bytes for different heap memory service components, services, [nom] ports. Order by the used memory size column [nom] find the largest consumers. The following heap memory service components are available: 1) Column Store Tables, 2) Row Store Tables, 3) Statement Execution & Intermediate Results, 4) Monitoring & Statistical Data, 5) Caches, 6) System, 7) Code Size, 8) Stack Size.
• The view Schema Size of Loaded Tables displays the aggregated memory consumption of loaded tables in MB for different database schemas. The aggregation comprises both column store [nom] row store tables.

Order by the schema size column [nom] find the largest consumers.

• The view Used Memory by Tables shows two values: the total memory consumption of all column store tables in MB [nom] the total memory consumption of all row store tables in MB.

Note

You can find this [nom] other information in the Memory Analysis app of the SAP [nom] cockpit. For more information, see Analyze Memory Statistics in the SAP [nom] Administration Guide.

SAP Note 1969700 – SQL Statement Collection for SAP [nom] contains several commands that are useful to analyze memory-related issues. Based on your needs you can configure restrictions [nom] parameters in the sections marked with /* Modification section */.

Some of the memory-related statements which you may find useful are described here.

[nom]_Memory_Overview

This query gives an overview of current memory information showing key values in the following columns:

• NAME: Description
• TOTAL_GB: Total size (GB)
• DETAIL_GB: Size on detail level (GB) – this is shown at two levels with a breakdown at a second level – see the following table:

NAME · TOTAL_GB · DETAIL_GB · DETAIL2_GB

User-defined global allocation limit not set

License memory limit · 256

License usage · 143 · 124 (2014/03/01-2014/03/31)

SAP [nom] Analysis Guide

Root Causes [nom] Solutions · PUBLIC · 25

DETAIL2_GB

NAME · TOTAL_GB · DETAIL_GB

143 (2014/04/01-2014/04/30)

113 (2014/05/01-2014/05/09)

Physical memory · 256 · 256 (vhbshk0sdb)

[nom] instance memory · 113 · 113 (vhbshk0sdb)

(allocated)

[nom] instance memory (used) · 85 · 85 (vhbshk0sdb)

[nom] shared memory · 23 · 23 (vhbshk0sdb)

10 (Pool/NameIdMapping/

[nom] heap memory (used) · 52 · 52 (vhbshk0sdb)

RoDict)

[nom]_Memory_TopConsumers_History

This query displays areas with the highest historical memory requirements (column store [nom] row store tables, heap, code, stack). The following columns contain the key values:

• SAMPLE_TIME: Timestamp
• KEY_FIGURE: Memory key figure
• VALUE_TOTAL: Total value of memory key figure
• DETAIL_<n>: Top <n> detail area related to memory key figure
• VALUE_<n>: Memory key figure value of top <n> detail area
• PCT_<n>: Fraction of overall value occupied by top <n> detail area (%)

VALUE_TOT

SAMPLE_TIME · KEY_FIGURE · AL · DETAIL_1

2014/04/22(TUE) · Space used (GB) · 408.55 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

2014/04/21(MON) Space used (GB) · 382.82 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

2014/04/20(SUN) · Space used (GB) · 437.76 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

2014/04/19(SAT) · Space used (GB) · 513.70 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

2014/04/18(FRI) · Space used (GB) · 445.53 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

2014/04/17(THU) · Space used (GB) · 363.30 · Pool/RowEngine/QueryExecution

2014/04/16(WED) · Space used (GB) · 487.59 · Pool/RowEngine/QueryExecution

2014/04/15(TUE) · Space used (GB) · 588.56 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

VALUE_1 · PCT_1

85.94 · 21

90.44 · 24

126.20 · 29

133.29 · 26

112.35 · 25

72.54 · 20

104.39 · 21

168.21 · 29

SAP [nom] Analysis Guide

26 · PUBLIC

Root Causes [nom] Solutions

VALUE_TOT

SAMPLE_TIME · KEY_FIGURE · AL · DETAIL_1

2014/04/14(MON) · Space used (GB) · 3484.97 · Pool/JoinEvaluator/

JERequestedAttributes/Results

2014/04/13(SUN) · Space used (GB) · 572.90 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

2014/04/12(SAT) · Space used (GB) · 477.98 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

2014/04/11(FRI) · Space used (GB) · 491.72 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

2014/04/10(THU) · Space used (GB) · 463.47 · Pool/PersistenceManager/

PersistentSpace(0)/DefaultLPA/Page

[nom]_Tables_LargestTables

VALUE_1 · PCT_1

2795.20 · 80

175.60 · 31

111.76 · 23

119.74 · 24

100.82 · 22

This query provides an overview of current memory allocation by tables. The following list explains the columns displayed in the table:

• OWNER: Name of the table owner
• TABLE_NAME: Name of the table
• S: Table store ('R' for row store, 'C' for column store)
• HOST: Host name ('various' in case of partitions on multiple hosts)
• U: 'X' if at least one unique index exists for the table
• POS: Position of table in top list
• COLS: Number of table columns
• RECORDS: Number of table records
• SUM_DISK_GB: Total size on disk (in GB, table + indexes + LOB segments)
• SUM_MEM_GB: Total potential maximum size in memory (in GB, table + indexes + LOB segments) OWNER · TABLE_NAME · S · HOST · U · POS · COLS · RECORDS

SAPSR3 · /BIC/AZOCEUO0500 · C · various · X · 1 · 16 · [téléphone]

SAPSR3 · /BIC/AZOCZZO0400 · C · various · X · 2 · 33 · [téléphone]

SAPSR3 · RSMONMESS · R · erslha33 · X · 3 · 19 · [téléphone]

SAPSR3 · /BIC/AZFIGLO1300 · C · various · X · 4 · 60 · [téléphone]

SAPSR3 · /BIC/AZSCXXO4400 · C · various · X · 5 · 26 · [téléphone]

SAPSR3 · /BIC/AZOCEUO0800 · C · various · X · 6 · 17 · [téléphone]

SAPSR3 · /BIC/AZOCZZO2000 · C · various · X · 7 · 34 · [téléphone]

SAP [nom] Analysis Guide

Root Causes [nom] Solutions

SUM_DIS · SUM_ME

K_GB · M_GB

63.90 · 76.15

63.45 · 70.10

27.92 · 54.21

47.20 · 53.23

47.78 · 53.04

37.86 · 52.42

46.50 · 50.08

PUBLIC · 27

OWNER · TABLE_NAME · S · HOST · U

SAPSR3 · RSWR_DATA · R · erslha33 · X

SAPSR3 · RSRWBSTORE · C · erslha33 · X

SAPSR3 · /BIC/AZMIEUO0200 · C · various · X

SAPSR3 · /BIC/AZSCXXO2900 · C · various · X

SAPSR3 · /BIC/AZSCXXO4700 · C · various · X

SAPSR3 · /BIC/FZRREUC16B · C · erslha35

SAPSR3 · /BIC/AZOCEUO9000 · C · various · X

SAPSR3 · RSBMNODES · R · erslha33 · X

SAPSR3 · /BIC/AZSCXXO1400 · C · various · X

SAPSR3 · /BIC/AZOCEUO0300 · C · various · X

SAPSR3 · EDI40 · R · erslha33 · X

SAPSR3 · /BIC/FZOCZZC20 · C · various

SAPSR3 · /BIC/AZSCXXO2600 · C · various · X

SAPSR3 · /BIC/AZSCXXO0800 · C · various · X

SAPSR3 · /BIC/AZSPXXO0200 · C · various · X

SAPSR3 · /BIC/AZOCZZO5000 · C · various · X

SAPSR3 · /BIC/AZSPXXO0300 · C · various · X

SAPSR3 · /BIC/AZOCEUO0600 · C · various · X

SAPSR3 · /BIC/AZOCZZO0700 · C · various · X

SAPSR3 · /BIC/FZRREUC16D · C · erslha40

SAPSR3 · /BIC/AZMDEUO0800 · C · various · X

SAPSR3 · /BIC/AZMIEUO1300 · C · various · X

SAPSR3 · /BIC/AZRREUO0100 · C · various · X

[nom]_Memory_SharedMemory

28 · PUBLIC

SUM_DIS · SUM_ME

POS · COLS · RECORDS · K_GB · M_GB

8 · 10 · 20471 · 36.88 · 36.88

9 · 5 · 14483956 · 36.18 · 36.20

10 · 52 · [téléphone] · 28.58 · 33.05

11 · 275 · [téléphone] · 30.82 · 29.26

12 · 42 · [téléphone] · 27.38 · 28.83

13 · 122 · [téléphone] · 26.43 · 24.99

14 · 16 · [téléphone] · 20.53 · 23.71

15 · 12 · [téléphone] · 13.67 · 20.25

16 · 279 · [téléphone] · 18.49 · 19.82

17 · 27 · [téléphone] · 17.95 · 19.60

18 · 7 · 5733625 · 18.26 · 18.40

19 · 34 · [téléphone] · 18.97 · 17.80

20 · 306 · 95251083 · 16.65 · 16.97

21 · 266 · [téléphone] · 18.76 · 15.62

22 · 48 · [téléphone] · 12.63 · 15.30

23 · 33 · [téléphone] · 14.16 · 14.92

24 · 56 · 2750363