## 4.2.2.1 Expensive Statements Trace
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 and is inactive by default.
If, in addition to activating the expensive statements trace, you enable per-statement memory tracking, the expensive statements trace will also show the peak memory size used to execute the expensive statements.
## Expensive Statements Trace Information
If you have the TRACE ADMIN privilege, then you can view expensive statements trace information in the following ways:
- In the Expensive Statements app of the SAP HANA cockpit
- In the Statement Library in SAP HANA database explorer by searching for Expensive Statements Analysis .
- In the m_EXPENSIVE_STATEMENTS system view (see Tips for Performance below)
## 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. If you set a value for more than one of the three threshold filters a statement that exceeds any one of the filters is selected as an expensive statement; it is not necessary for all filters you apply to be exceeded.
## Configuration Options
##  Note
The following table shows the configuration parameters which are available; not all of these may be available in the SAP HANA cockpit or the SAP HANA database explorer.
| Option             | Configuration Parameter   | Default Value   | Description                                                                                                                                                                                                                                                                                       |
|--------------------|---------------------------|-----------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Trace status       | enable                    | off             | Specifies the activation status of the trace.                                                                                                                                                                                                                                                     |
| Threshold CPU time | threshold_cpu_time        | -1 (disabled)   | Specifies the threshold CPU time of statement exe- cution in microseconds. When set to 0, all SQL statements are traced.  Note Resource tracking and CPU time tracking must also be enabled. You can do this by configuring the corresponding parameters in the resource_tracking section of the |
| Threshold memory   | threshold_memory          | -1 (disabled)   | Specifies the threshold memory usage of state- ment execution in bytes. When set to 0, all SQL statements are traced.                                                                                                                                                                             |
| Option                                  | Configuration Parameter    | Default Value                         | Description                                                                                                                                                                                                                                                                                                         |
|-----------------------------------------|----------------------------|---------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Threshold dura- tion                    | threshold _duration        | 1000000 ( mi- croseconds = 1 second ) | Specifies the threshold execution time in microsec- onds. When set to 0, all SQL statements are traced. In the SAP HANAdatabase explorer, you can set the threshold duration to be measured in seconds or milliseconds.                                                                                             |
| User, applica- tion, and object filters | user                       | Empty string                          | Specifies filters to restrict traced statements to those of a particular database, application user, application, or tables/views. For user , application_user , and application the use of wildcards is supported (see following sub- section Using Wildcards ).                                                   |
| User, applica- tion, and object filters | application_user           | Empty string                          | Specifies filters to restrict traced statements to those of a particular database, application user, application, or tables/views. For user , application_user , and application the use of wildcards is supported (see following sub- section Using Wildcards ).                                                   |
| User, applica- tion, and object filters | application                | Empty string                          | Specifies filters to restrict traced statements to those of a particular database, application user, application, or tables/views. For user , application_user , and application the use of wildcards is supported (see following sub- section Using Wildcards ).                                                   |
| User, applica- tion, and object filters | object                     | Empty string                          | Specifies filters to restrict traced statements to those of a particular database, application user, application, or tables/views. For user , application_user , and application the use of wildcards is supported (see following sub- section Using Wildcards ).                                                   |
| Passport trace level                    | passport_tracelevel        | Empty string                          | If you are activating the expensive statements trace as part of an end-to-end trace scenario with the Process Monitoring Infrastructure (PMI), you can specify the passport trace level as an additional fil- ter. This means that only requests that are marked with a passport of the specified level are traced. |
| Passport trace level                    | passport_tracelevel        | Empty string                          |  Note Process tracing is possible only for compo- nents in the ABAP and Business Objects stacks.                                                                                                                                                                                                                   |
| Trace parame- ter values                | trace_parameter_values     | true                                  | In SQL statements, field values may be specified as parameters (using a "?" in the syntax). If these parameter values are not required, then you can disable this setting to reduce the amount of data traced.                                                                                                      |
| Trace flush in- terval                  | trace_flush_interval       | 10                                    | Specifies the number of records after which a trace file is flushed.                                                                                                                                                                                                                                                |
| Use in-memory tracing                   | use_in_memory_tracing      | true                                  | If in-memory tracing is active, then information is cached in memory. Otherwise, the data is written directly to file.                                                                                                                                                                                              |
| In-memory tracing records               | in_memory_tracing_reco rds | 30000                                 | Specifies the maximum number of trace records (per service) stored in memory. This setting only takes effect when in memory trac-                                                                                                                                                                                   |
| In-memory tracing records               |                            |                                       | ing is active.                                                                                                                                                                                                                                                                                                      |
## Using Wildcards
If you apply filters for the user , application_user , and application parameters, the use of wildcards and exceptions is also supported. The asterisk wildcard character denotes any number of characters and the exclamation mark denotes an exclusion. For example:
user=SM*,JONES,!GREEN,!BRO*
In this case all users starting with SM will be traced, JONES will be traced, user GREEN will not be traced and all users starting with BRO will not be traced. If terms in the string conflict with each other then the sequence in which the terms occur determines the result. In the following example user SMALL will be traced in spite of the exclusion; the exclusion is ignored because it occurs after the first wildcard.
user=SM*,JONES,!SMALL,!BRO*
## Trace File Rotation
To prevent expensive statement trace information from growing indefinitely, you can limit the size and number of trace files using the following parameters in the expensive_statement section of the global.ini file.
| Parameter   | Default                     | Description                                                                                                                                                                                                                                                                                                                                                    |
|-------------|-----------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| maxfiles    | 10                          | Specifies the maximum number of trace files. When the maximum number of trace files reached, the oldest trace file is deleted and a new one opened. When set to 0, trace file rotation is disabled.                                                                                                                                                            |
| maxfilesize | 10000000 (or 9.5 megabytes) | Specifies the maximum size of an individual trace file in bytes. When the maximum number of files is greater than 1 and the maximum file size is reached, a new trace file is opened. When the maximum number of files is 1, the maximum file size is greater than zero, and the maximum file size is reached, the trace file is deleted and a new one opened. |
## Tips For Performance
The expensive statements trace may incur an overhead on system performance. Note also that accessing the monitoring view m_EXPENSIVE_STATEMENTS implicitly reads the trace files.
You may be able to adjust the settings of the configuration parameters described above to reduce the volume of trace files and optimize the performance. For example:
- You can increase the value of the threshold parameters to reduce the number of expensive statements
- Turn off trace_parameter_values to exclude statements with parameters ('?') in the where clause.
- Make sure that memory caching is in use: use_in_memory_tracing is active by default.
You may change these parameter values permanently or temporarily to handle a specific situation. If necessary you can disable expensive tracing by setting enable to off.
## Related Information
Setting a Memory Limit for SQL Statements [page 79]
Monitoring and Analyzing Expensive Statements (SAP HANA Cockpit) Expensive Statements Monitoring (SAP HANA Studio)