Working with SAP HANA Traces

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

  Copy codeSwitch to dark mode

  1
  ALTER SYSTEM START PERFTRACE

• 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 Configuration → Expensive 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.