As SAP HANA Platform evolves with each new release, the SQL optimization team continuously enhances additional optimizations and support for added new features for each release. With the new improvements, the SQL Query Processor generates somewhat different plan from the previous versions and some performance regression may occur. SQL Plan Stability was introduced to avoid such possible performance regressions and controls the execution time to be within the expected boundaries.
To understand how this is achieved, let’s dive into how the SQL Query Processor works behind the scenes. When a new SQL statement is received from a client, it is evaluated by the SQL Optimizer with several steps that needs to be done to compile, pick and cache the best physical execution plan. The following diagram shows simplified steps without SQL Plan Stability.
With SQL Plan Stability, the physical execution plan related information is collected and stored as Abstract SQL Plan (ASP) to the persistent storage for future reference. This information can be used to skip the existing rewriting steps but still able to recreate the same execution plan based on the logical plan and physical properties stored in ASP.
Now let’s look into when the same SQL statement is received again. The statement is first check against the plan cache if it is already cached for faster execution. There will be times where the existing plan is invalidated and need to be recompiled. At this time, when SQL Plan Stability activated, it will check if an Abstract SQL Plan of the SQL statement already exists. Unlike the SQL Plan Cache, the Abstract SQL Plan is not evicted and kept in storage unless manually removed. If ASP already exists, the logical plan with physical properties are loaded back into the SQL Query Optimizer, validated and passed on to the execution plan generation step for final compilation.
We have tested upgrading from HANA 1.0 SPS12 to HANA 2.0 SPS03 with 202 statements captured as ASP with the execution times of each statements in the following test results.
If the conditions are the same, the execution time will be identical and lined up diagonally. Below the line shows improvements and above regression. First, the left side show results without Plan Stability where above and below. To improve the execution time for statements with regression, ASP is applied to enforce the existing execution plan is used. For improved statements, ASP is not applied to keep the execution time improvements. The right side show the results after ASP is activated improving statements with regression.
With the new SQL Plan Stability, two key benefits are obtained. First, the ASP stored will generate the same execution plan based on the existing logical plan and physical properties saved whenever possible. Thus, it will avoid any performance regressions and achieve plan stability. Second, the logical plan enumeration step is skipped reducing the total compilation time which can reduce preparation time for very complex queries.
SQL Plan Stability is a two-step process. First, “Capture” to store the ASP. Second, “Apply” to activate the ASP for execution plan creation. Please find the most updated information regarding restrictions from Note #2639193 – SAP HANA SQL Plan Stability.
To understand how this is achieved, let’s dive into how the SQL Query Processor works behind the scenes. When a new SQL statement is received from a client, it is evaluated by the SQL Optimizer with several steps that needs to be done to compile, pick and cache the best physical execution plan. The following diagram shows simplified steps without SQL Plan Stability.
With SQL Plan Stability, the physical execution plan related information is collected and stored as Abstract SQL Plan (ASP) to the persistent storage for future reference. This information can be used to skip the existing rewriting steps but still able to recreate the same execution plan based on the logical plan and physical properties stored in ASP.
Now let’s look into when the same SQL statement is received again. The statement is first check against the plan cache if it is already cached for faster execution. There will be times where the existing plan is invalidated and need to be recompiled. At this time, when SQL Plan Stability activated, it will check if an Abstract SQL Plan of the SQL statement already exists. Unlike the SQL Plan Cache, the Abstract SQL Plan is not evicted and kept in storage unless manually removed. If ASP already exists, the logical plan with physical properties are loaded back into the SQL Query Optimizer, validated and passed on to the execution plan generation step for final compilation.
We have tested upgrading from HANA 1.0 SPS12 to HANA 2.0 SPS03 with 202 statements captured as ASP with the execution times of each statements in the following test results.
If the conditions are the same, the execution time will be identical and lined up diagonally. Below the line shows improvements and above regression. First, the left side show results without Plan Stability where above and below. To improve the execution time for statements with regression, ASP is applied to enforce the existing execution plan is used. For improved statements, ASP is not applied to keep the execution time improvements. The right side show the results after ASP is activated improving statements with regression.
With the new SQL Plan Stability, two key benefits are obtained. First, the ASP stored will generate the same execution plan based on the existing logical plan and physical properties saved whenever possible. Thus, it will avoid any performance regressions and achieve plan stability. Second, the logical plan enumeration step is skipped reducing the total compilation time which can reduce preparation time for very complex queries.
SQL Plan Stability is a two-step process. First, “Capture” to store the ASP. Second, “Apply” to activate the ASP for execution plan creation. Please find the most updated information regarding restrictions from Note #2639193 – SAP HANA SQL Plan Stability.
- SAP Managed Tags:
