Working with SAP HANA Traces

Objectives

After completing this lesson, you will be able to:

  • Configure SAP HANA traces
  • Check SAP HANA traces

Configuration of Traces

This lesson explains how to activate and configure the different trace tools available in SAP HANA.

The various traces can produce detailed information about the actions of the database system. You can activate and configure most traces in the Database Explorer of SAP HANA cockpit. The figure, Trace Configuration, shows an example of how to open and choose the Trace Configuration tab of the tenant database. Different configuration options are available for each trace.

Note

To configure traces, you must have the system privilege TRACE ADMIN. To configure the kernel profiler, you must have the SAP_INTERNAL_HANA_SUPPORT standard role.

Trace Overview

You can use the following traces:

  • Database trace (default: active)

    Database tracing is always active. This means that information about error situations is always recorded. However, for more detailed analysis of a specific problem or component, you may need to configure a certain trace component to a trace level higher than the default value. For example, the backup trace component records information about backup operations, the authorization component records information about authorization operations, and so on.

    Hint

    The following SAP Note provides information about the trace components and their different use cases:

    SAP Note 2380176 FAQ: SAP HANA Database Trace

    If a trace component is available in all services, the trace level can be configured for all services at once. It is also possible to configure the trace level of a component individually for a specific service. The trace level of a component configured at service level overrides the trace level configured for all services. Some components are only available in a particular service and cannot therefore be changed globally.

    You can configure the trace levels of database trace components in the SAP HANA database explorer or the SAP HANA cockpit. Alternatively, you can modify the parameters in the trace section of the global.ini configuration file (for all services) or service-specific files such as indexserver.ini. The individual parameters correspond to trace components and the parameter value is the trace level.

    The higher the trace level, the more detailed the information recorded by the trace. The following trace levels exist:

    • NONE (0)
    • FATAL (1)
    • ERROR (2)
    • WARNING (3)
    • INFO (4)
    • DEBUG (5)
    Note

    Even if you select trace level NONE, information about error situations is still recorded.

    Tenant databases inherit the database trace level configured in the system database unless you change the trace level in the tenant database.

    The trace level of trace components in a tenant database is inherited from the system database as the default value. If you want to configure a different trace level for a particular component in the tenant database, either globally for all services or for a specific service, you can do so by changing the trace level of the relevant component.

  • User-specific (default: inactive) and End-to-end database (default: inactive) traces

    User-specific and end-to-end traces extend the configured database trace by allowing you to change the trace level of components in the context of a particular user or end-to-end analysis. The trace levels configured for components in these contexts override those configured in the database trace.

    The end-to-end traces are triggered by applications outside of the SAP HANA database. The default trace levels for the SAP HANA database components are normally sufficient and do not need to be changed.

  • SQL trace (default: inactive)

    The SQL trace collects information about all SQL statements executed on the index server (tenant database) or name server (system database) and saves it in a trace file for further analysis.

    Information collected by the SQL trace includes overall execution time of each statement, the number of records affected, potential errors (for example, unique constraint violations) that were reported, the database connection being used, and so on. The SQL trace is a good starting point for understanding executed statements and their potential effect on the overall application and system performance, as well as for identifying potential performance bottlenecks at statement level.

    SQL trace information is saved as an executable python program (by default sqltrace_<...>.py), which can be used to replay the traced database operations. You can also use the SQL Trace Analyzer tool to automate the analysis of the file.

    SAP HANA SQL Trace Analyzer

    SAP HANA SQL Trace Analyzer is a Python tool used to analyze the SAP HANA SQL trace output. The tool gives you an overview of the top SQL statements, the tables accessed, statistical information on different statement types and on transactions executed.

    For more information about the installation and usage of SAP HANA SQL Trace Analyzer, see the following note:

    SAP Note 2412519 FAQ: SAP HANA SQL Trace Analyzer

  • Performance trace (default: inactive)

    The performance trace is a performance tracing tool built into the SAP HANA database. It records performance indicators for individual query processing steps in the database kernel. You may be requested by SAP Support to provide a performance trace.

    Information collected includes the processing time required in a particular step, the data size read and written, network communication, and information specific to the operator or processing-step-specific (for example, number of records used as input and output). The performance trace can be enabled in multiple tenant databases at the same time to analyze cross-database queries.

    Performance Trace Files

    Performance trace results are saved to the trace files with file extension .tpt or .cpt, which you can access with other diagnosis files. To analyze these files, you need a tool capable of reading the output format (.tpt and .cpt). SAP Support has tools for evaluating performance traces.

    Enabling and Configuring the Performance Trace

    You can enable and configure the performance trace using the ALTER SYSTEM * PERFTRACE SQL statements.

    To start the performance trace, execute the following command:

    Code snippet
    ALTER SYSTEM START PERFTRACE
    Expand

  • Expensive statements trace (default: inactive)

    Expensive statements are individual SQL statements whose execution time exceeds a configured threshold. The expensive statements trace records information about these statements for further analysis.

    If, in addition to activating the expensive statements trace, you enable per-statement memory tracking, the expensive statements trace also shows the peak memory size used to execute the expensive statements.

    Expensive Statements Trace Information

    If you have the TRACE ADMIN privilege, you can view expensive statements trace information in the following ways:

    • In the SQL Statements app of the SAP HANA cockpit
    • On the Trace ConfigurationExpensive Statements Trace tab of the SAP HANA database explorer
    • In the M_EXPENSIVE_STATEMENTS system view

    Enabling and Configuring the Expensive Statements Trace

    You can enable and activate the expensive statements trace in the SAP HANA cockpit or the SAP HANA database explorer. Alternatively, you can modify the parameters in the expensive_statement section of the global.ini configuration file.

  • Kernel profiler (default: inactive)

    The kernel profiler is a sampling profiler built into the SAP HANA database. It can be used to analyze performance issues with systems on which third-party software cannot be installed, or parts of the database that are not accessible by the performance trace.

    The kernel profile collects, for example, information about frequent and/or expensive execution paths during query processing.

    Kernel Profiler Traces

    Profiling results are saved to the trace files CPU_<service>_<host>_<port>_<timestamp>.<format> and WAIT_<service>_<host>_<port>_<timestamp>.<format>, where <format> is either dot or kcachegrind. You can access the profiling results with other diagnosis files. To analyze these trace files meaningfully, you need a tool capable of reading the configured output format, that is, KCacheGrind or DOT (default format). Alternatively, send the files to SAP Support.

    Enabling and Configuring the Kernel Profiler

    You enable and configure the kernel profile in the SAP HANA database explorer. It is recommended that you start kernel profiler tracing immediately before you execute the statements you want to analyze and stop it immediately after they have finished. This avoids the unnecessary recording of irrelevant statements. It is also advisable as this kind of tracing can negatively impact performance.

    Note

    To enable the kernel profile, you must have the SAP_INTERNAL_HANA_SUPPORT role. This role is intended only for SAP HANA development support.

  • Plan Trace (default: inactive)

    Plan Trace enables you to collect SQL queries and their execution plans, executed in a given time frame. For each SQL query traced you can visualize the execution plan for performance analysis. The Plan Trace is a trace feature for SQL analyzer.

    Note

    Only SELECT statements are traced with Plan Trace.

    To activate the Plan Trace, search for the Manage SQL Performance card, which is integrated in the Administration or All view of the Database Overview screen. Inside that card, choose the Plan Trace section.

Checking Traces

The SAP HANA database explorer allows you to diagnose and analyze errors in an SAP HANA database by viewing the relevant diagnostic files.

In the database explorer, the diagnostic files for online databases are grouped by tenant, host, and then by service. In a multi-host system, check each host folder to view all diagnostic files associated with a particular service.

Note

To view diagnosis files and delete trace files, you must have the TRACE ADMIN system privilege.

Check the Diagnosis Files

If there are problems with the database, you can check the log and trace files for errors. These diagnosis files are available in the SAP HANA Database Explorer. On the SAP HANA cockpit home screen, you can open the Database Explorer by choosing the Database Explorer link.

If you are in the Database Overview screen of a tenant or the system database, you can open the Database Explorer by selecting the Open SQL Console link.

The diagnosis files are located under in the folder Database Diagnostic FilesDatabase name (SystemDB or tenant)HostService name.

In the service folder, all the diagnostic files related to that specific service can be seen. As soon as you choose a file, it is opened in the file browser. In the text editor, you can search for error or warnings by pressing [CTRL]+F.

Right-clicking on a diagnostic file opens the context menu from which you can open, show, download, and copy trace files. Even creating a shortcut to a trace file is supported.

Trace file rotation prevents trace files from growing indefinitely by limiting the size and number of trace files. You can configure trace file rotation globally for all services in the system and for individual services.

Note

The parameters maxfiles and maxfilesize, which can be found in the global.ini or specific services like indexserver.ini file, control the log rotation.

Creation of a Full System Dump File

To help SAP Support analyze and diagnose problems with your system, you can collect a range of diagnosis information from your system in a ZIP file. You can trigger the collection of diagnosis information from the SAP HANA cockpit and the command line.

In SAP HANA cockpit System Overview screen, search for the Manage full system information dumps link. The Manage Full System Information Dumps application collects all the important diagnosis information in a ZIP file, which you can download to a local PC. You can attach this downloaded ZIP file to an SAP Support Message.

The function Manage Full System Information Dumps provides the following features:

  • Collect important diagnosis information
  • Collect RTE (Runtime Environment) dump files
  • Download and delete collected diagnosis information

Collecting Diagnostics Information by managing Full System Information Dumps using a time range and from existing files does not cover the following items by default, hence they must be selected manually:

  • Performance traces
  • System views
  • Exported system tables and views
Hint

As the user <sid>admin, you can use the alias cdpy to quickly navigate to the python_support directory.

When you start collecting diagnosis information, the system collects the relevant information by executing the Python script fullSystemInfoDump.py. You can execute this script in Manage full system information dumps in SAP HANA cockpit or directly from the command line on the SAP HANA server as the <sid>adm user.

The fullSystemInfoDump.py script is part of the server installation and can be run from the command line. It is located in the directory $DIR_INSTANCE/exe/python_support.

Note

For more information on the Full System Information Dump, see the HA215 course.

Log in to track your progress & complete quizzes