Earlier this year, I have shared the information of improved query execution as
Faster query execution with lesser memory for SAP HANA Cloud` introducing the latest development of HEX and the improvements achieved by HEX.
With SAP HANA Platform 2.0 SPS07, the same HEX execution engine is also available. So, customers should notice that queries have changed the execution engine from older engines to the latest HEX and running with improved performance while using lesser memory.
One of the biggest enhancements for SPS07 is the unified SQL Optimizer for HEX. With old engines, it each had its own internal optimizer responsible for join and aggregation ordering. So, the SQL optimizer may not have made the most optimal cost based plan with divided optimization responsibilities. With HEX, there is no internal optimizations and relies on the SQL optimization for the optimal execution plan.
For more insights to how HEX provides improved performance, please refer to the earlier
blog.
The major difference between SAP HANA Cloud and SAP HANA Platform regarding HEX is that there are already many customers running SAP applications on top of SAP HANA Platform with mission critical system. So, any regressions would have a big impact in running their daily businesses, so we are being more cautious in the on-premise HANA compared to SAP HANA Cloud. So, there are limits or thresholds to prevent any regressions. If the execution of a statement is expected to exceed the predefined limit or threshold, the plan is compiled with an older plan such as OLAP, JOIN and/or ESX. Most likely this would be detected during query compilation time.
Additionally, some plans may be compiled and executed as HEX but due to parameter value changes that can cause longer running time or select larger datasets, the limit may be exceeded during execution. For such a case, the HEX stop the current execution and flag the SQL compiler to recompile which is known as fallback to old engines.
SAP Note #3326981 describes in detail of why such a fallback occurs and how to detect such cases.
For such a situation, the query was running optimally with HEX but have fallen back to older engines due to defined threshold limits. The execution time for such a case would be similar to when HEX wasn't available but as customers have experienced the benefits of HEX during the earlier executions, customers might consider such a scenario a regression which actually isn't. We have been informed of such cases where customers have noticed the improved performance then fallback.
The issue is that once the plan has fallen back to any older engine based plan, it is kept in cache and doesn't go back to the faster HEX plan until it gets invalidated.
SAP Note #3326981 shows how to detect such cases to get the affected plans and then evict them.
The enclosed SQL statement will list SQL execution statistics that have changed significantly between different engines as a reference script or a HANA sitter can be used to programmatically probe the system periodically.
In addition to the fallback from HEX, a plan may still be compiled to use the old engines during enumeration phase. It may be due to a threshold limit or a feature that is still not supported with HEX (although not many with SPS07). To check why a plan is still using an old engine, set trace level
qo_hex_enum_fail = DEBUG in order to get information which limit was hit and why query wasn’t executed in HEX, The following trace info is an example shown as follows.
[18212]{344628}[32/4103809] 2023-06-07 06:41:55.321169 d qo_hex_enum_fail CapabilityChecker.cc(00083) : [rel: Project, opId:L:0] Hex Enumeration Failure: HEX is disabled because dml is not supported
Also, a similar information is shown in the PlanViz file as Why not HEX info.
I will have another followup blog explain in more detail regarding HEX with SQL Analyzer and links to additional documentation once they become available soon.
We are working on SAP HANA internal plan management to improve the management of such cases and will provide solutions to resolve them in a later revision/version.
Best Regards,
Tae-Suk Son
SAP HANA Product Management
