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.

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