Product Information
Analyzing HEX query performance
I have written HEX blogs for both SAP HANA Cloud and SAP HANA Platform introducing the architectural benefits when queries are running with HEX.
- Faster query execution using lesser memory in SAP HANA Cloud
- Faster query execution with lesser memory now also available in SAP HANA Platform SPS07
Prior to these blogs, SQL Analyzer based on VS Code or BAS was introduced for analyzing SQL statements regarding performance
- SQL Analyzer now available in Business Application Studio Part 1
- SQL Analyzer now available in Business Application Studio Part 2
Now, I would like to combine both topics and show how to use the SQL Analyzer for HEX query performance analysis.
|
Non-HEX execution summary |
Shows the execution time (with compilation time), peak memory, and Plan Graph info |
HEX execution summary |
Shows the total CPU time and execution time (with compilation time), peak memory, and Plan Graph info. For HEX, CPU time is an important measure regarding performance as it is executed in multiple pipelines with data chunks, so compared to the older engines, idle time shouldn’t be considered. With multicore processing, CPU time is aggregation of all cores, so it can be higher than the execution time |
Another very important feature in the overview tab is the Why Not HEX audits. While a query is being compiled, the execution plan might not use HEX due to some reasons. You can check the reason using the Why Not HEX audit. The below example shows that a NO_USE_HEX_PLAN hint was used for the non-HEX execution plan.
Why not HEX
Next, we will dive into the Plan Graph to check what features are available for HEX.
Non-HEX Operators |
For non-HEX operators, the operator time line is shown as inclusive time and exclusive time |
HEX Operators |
For HEX operators, it is enclosed within a HEX search operator. The time is show and operator CPU time proportional to total CPU time. To find the bottleneck operator, the operator with the highest total CPU time should be investigated first. |
The details of each HEX operators can be found in the official documentation for HEX Operators.
Lets look into the configuration options for the 2 plan types.
 |
For non-HEX operator time mode, “Inclusive Wall Time” is the default as shown above. The “CPU Time” is disabled as it is reserved only for HEX. “Physical” is the default plan mode. |
 |
For HEX operator time mode, “CPU Time” is shown as default as it’s information is most useful when pinpointing any bottlenecks for HEX. For plan mode, the physical has been split into 2 different modes. “Simple” as default and “Expert” modes. We had received many requests to document the operators and for HEX, we have decided to provide a simple mode so that customer can utilize the information to pinpoint bottlenecks and further improve the performance of HEX execution if needed. All operators in “Simple” mode have been documented for better understanding. The operators in “Expert” mode is for SAP internal usage if issues occur that needs involvement from HEX team and contains detailed information that is generated for such purpose. |
For HEX plans, it may be useful to check the degree of parallelism. To check this information, select Properties tab and check the NumUsedLocalContexts and Calls
As part of HEX optimization, several HEX operators may be fused together during compilation time and would be somewhat difficult to investigate using Plan Graph. For such a case, NO_HEX_FUSE_JIT_CODE hint should be applied during development phase to optimize the SQL statement and remove them for production use.
Additionally, HEX_COMPILE_JIT_CODE should also be used to force full compilation of the query during query development phase.
Hope this information helps investigating HEX query performance and please leave comments regarding what further HEX information would be useful.
Best regards,
Tae-Suk Son
SAP HANA Product Management
** Current blog is based on SQL analyzer tool for SAP HANA v1.3.19230619 **