1. What purpose does expensive statements trace serve?
To record execution details for database requests that exceed defined thresholds, the expensive statements trace is used. When doing SQL optimization it is one of the information sources that can be used. It is recommended to activate this trace on a permanent basis due to the significant added value and the small overhead (in case of reasonable thresholds).
2. What are the common indications for problems with the expensive statements trace?
For one of the following individual checks SQL: "HANA_Configuration_MiniChecks" returns a potentially critical issue (C = 'X')
| Check ID | Details |
| 620 | Minimum expensive SQL trace threshold (ms) |
| 623 | Traced expensive SQL statements (last day) |
3. To record a database request in the expensive statements trace what kinds of criteria can be used?
The performance indicators are available to mark a SQL statement as expensive:
| Indicator | Validity | Parameter | Unit | Details |
| Runtime | general | global.ini -> [expensive_statement] -> threshold_duration | us | SQL statements exceeding the configured runtime limit are recorded. |
| CPU consumption | Rev. >= 94 | global.ini -> [expensive_statement] -> threshold_cpu_time | us | SQL statements exceeding the configured CPU consumption are recorded. As a prerequisite CPU time measurement must be activated with the following parameter global.ini -> [resource_tracking] -> enable_tracking |
| Memory consumption | Rev. >= 94 | global.ini -> [expensive_statement] -> threshold_memory | byte | SQL statements exceeding the configured memory consumption are recorded. As a prerequisite memory tracking must be activated with the following parameters global.ini -> [resource_tracking] -> enable_tracking |
Read here Differences between SAP HANA and Oracle SQL
4. What are the parameters that control the expensive statements trace?
In the service specific trace files (e.g. indexserver.ini) up to SPS 06 the expensive statements trace was configured per default. The configuration has moved to global.ini starting with SPS 07.
For controlling the expensive statements trace the following main parameters exist:
| Parameter | Default | Unit | Details |
| global.ini -> [expensive_statement] -> application |
|
| Possibility to restrict trace to specific application name |
| global.ini -> [expensive_statement] -> application_user |
|
| Possibility to restrict trace to specific application user name |
| global.ini -> [expensive_statement] -> enable | false |
| Main switch, 'true' activates the expensive statements trace |
| global.ini -> [expensive_statement] -> in_memory_tracing_records | 30000 | records | Maximum number of trace records (per service) stored in memory |
| global.ini -> [expensive_statement] -> maxfiles | 10 | files | Maximum number of trace files |
| global.ini -> [expensive_statement] -> maxfilesize | 1000000 | bytes | Maximum file size in byte (default: 1 MB) |
| global.ini -> [expensive_statement] -> object |
|
| Possibility to restrict trace to specific objects (tables, views, procedures) |
| global.ini -> [expensive_statement] -> passport_tracelevel |
|
| Possibility to restrict trace to specific passport tracelevels (medium, high) |
| global.ini -> [expensive_statement] -> threshold_cpu_time |
| us | Threshold for minimum CPU utilization (Rev. >= 94) |
| global.ini -> [expensive_statement] -> threshold_duration | 1000000 | us | Threshold for minimum SQL statement duration (default: 1 s) |
| global.ini -> [expensive_statement] -> threshold_memory |
| byte | Threshold for minimum SQL memory allocation (Rev. >= 94) |
| global.ini -> [expensive_statement] -> trace_flush_interval | 10 | records | Interval for writing trace data from memory buffer to trace files on disk (Rev. >= 100) |
| global.ini -> [expensive_statement] -> trace_parameter_values | true |
| Possibility to trace bind values |
| global.ini -> [expensive_statement] -> use_in_memory_tracing | true |
| Possibility to use memory caching
|
| global.ini -> [expensive_statement] -> user |
|
| Possibility to restrict trace to specific database user name |
5. How can you activate and deactivate the expensive statements trace?
The following approaches can be used to activate and deactivate the expensive statements trace:
| Tool | Operation | Details |
| Parameter setting | Activation | global.ini -> [expensive_statement] -> enable = true |
|
| Deactivation | global.ini -> [expensive_statement] -> enable = false |
| Activation | Administration | |
|
| Deactivation | Administration |
| SAPGUI | Activation | DBACOCKPIT |
|
| Deactivation | DBACOCKPIT |
Read more SAP HANA LOBs Interview Question and Answer
6. Where does the result of expensive statements trace can be found?
In the following ways, the results of the expensive statements trace can be evaluated:
| Tool | Details |
| M_EXPENSIVE_STATEMENTS | This SAP HANA monitoring view contains the collected expensive statements information and can be queried directly. |
| SAP HANA Studio | Administration |
| SAPGUI | DBACOCKPIT |
Additionally the following analysis SQL statements are provided in SAP 1969700:
| SQL Statement | Details |
| SQL: "HANA_SQL_ExpensiveStatements" | Flexible access (including filters, aggregation and sorting) of expensive statements |
| SQL: "HANA_SQL_ExpensiveStatements_BindValues" | Display of bind values recorded in expensive statements trace |
| SQL: "HANA_Expensive_Statements_SQLText" | Display of SQL text of SQL statements recorded in expensive statements trace |
7. What types of problems can exist in the area of the expensive statements trace?
When using the expensive statements trace; the following issues and limitations can be considered:
| Issue | Details | ||||||||
| Overhead in case of small thresholds | A potentially very high number of database requests are traced if the threshold parameters are set too low. Some overhead and increased resource consumption is introduced. The following are the reasonable lower limits for the parameters:
| ||||||||
| Memory leak in Pool/RowEngine/MonitorView | The heap allocator Pool/RowEngine/MonitorView can grow significantly due to a memory leak if in memory tracing is used because of the following parameter setting global.ini -> [expensive_statement] -> use_in_memory_tracing = true | ||||||||
| Risk of crash during startup if trace object is defined | There is a risk for a crash during startup of SAP HANA with SAP HANA Rev. 80 and below if one or more trace objects are defined with the following parameter global.ini -> [expensive_statement] -> object | ||||||||
| Times may be recorded more than once | Depending on the operation, in several records the execution times in the expensive statements trace may be recorded For example, OPERATION = 'AGGREGATED_EXECUTION' also covers the times for individually recorded entries with OPERATION = 'SELECT'. This is the intended behavior. If you want to make sure that you don't count times twice, you have to restrict for specific, cumulative operations, e.g. 'AGGREGATED_EXECUTION' and 'CALL'. |
8. How much can you retain trace information in the expensive statements trace?
See SAP 2088971 for more details.
Read more about SAP HANA Modeling-Overview
9. Can we permanently activate the expensive statements trace?
The performance overhead of the expensive statements trace is minimal when reasonable threshold values are chosen. The added analysis value is significant at the same time. Therefore on a permanent basis it is possible and has been recommended to activate the expensive statements trace.
10. Which columns are important in expensive statements trace?
M_EXPENSIVE_STATEMENTS is the most important columns in the monitoring view
| Column | Unit | Details | ||||||||||||||
| STATEMENT_HASH |
| For a SQL statement the statement hash is a unique identifier and can be used for the same SQL statement from other monitoring views to retrieve performance data. | ||||||||||||||
| DURATION_MICROSEC | us | Duration of the particular operation | ||||||||||||||
| OBJECT_NAME |
| Names of the accessed tables | ||||||||||||||
| OPERATION |
| Type of recorded operation. The most important operations are:
| ||||||||||||||
| RECORDS |
| Number of processed records | ||||||||||||||
| STATEMENT_STRING |
| SQL text | ||||||||||||||
| PARAMETERS |
| Used bind values | ||||||||||||||
| ERROR_CODE |
| Error details in case of termination of database request | ||||||||||||||
| LOCK_WAIT_DURATION | us | Transactional lock wait time | ||||||||||||||
| MEMORY_SIZE | byte | Size of memory allocated by database request (e.g. for storing intermediate results), only populated if the following parameters are activated global.ini -> [resource_tracking] -> enable_tracking = on This value is the total memory consumption across all hosts in a scale-out environment, so it can be higher than the host specific statement_memory_limit configuration | ||||||||||||||
| CPU_TIME | us | CPU time required for processing the statement, only populated if the following parameter is activated, otherwise -1: global.ini -> [resource_tracking] -> cpu_time_measurement_mode | ||||||||||||||
| APP_USER |
| Application details (user, name and module) | ||||||||||||||
| NETWORK_MESSAGE_ID |
| Identifier for mapping expensive statements trace records to SQL client network I/O trace records (M_SQL_CLIENT_NETWORK_IO.MESSAGE_ID). |
11. How can the top SQL statements in terms of factors like elapsed time, CPU or memory consumption be determined?
You can use SQL to identify the top SQL statements in the expensive statements trace in terms of specific key figures:
Set "HANA_SQL_ExpensiveStatements" in "Modification section" with a specific ORDER_BY setting.
| ORDER_BY | Details | Column |
| CPU | Sorting by CPU consumption | CPU_TIME |
| DURATION | Sorting by total runtime | DURATION_MICROSEC |
| MEMORY | Sorting by memory consumption | MEMORY_SIZE |