## 4.5.3  SQL Plan Stability
SQL Plan Stability can be used to guarantee the consistent optimal performance of select statements by capturing query execution plans so that exactly the same plan can be reused when the query is executed again.
In SAP HANA, the SQL query processor parses SQL statements and generates SQL query execution plans. As the query processor and the query optimizer continue to be developed (in, for example, the new HANA Execution Engine - HEX) the resultant execution plans for a given query may change from one HANA release to another, or the plan may change because the data being queried has been reorganized; it is therefore possible that the performance of an SQL query statement may degrade.
In order to guarantee the performance of a query the plan stability feature offers the option to preserve a query's execution plan by capturing an abstraction of the plan (ASP) and reusing it if required to regenerate the original plan and restore the original performance.
##  Note
In some cases using statement hints may provide a solution to a loss of performance. Hints are available, for example, to control which optimizing engine is used (Extended SQL Executor (ESX) or HANA Execution Engine (HEX)); refer to Statement Performance Analysis for details.
## Process Overview
Plan Stability is currently only effective for SELECT queries and can be used to capture abstract SQL plans from queries which are either:
- executed at the command line
- already stored in the plan cache.
Restrictions related to Plan Stability are documented in SAP Note 2639193: SAP HANA SQL Plan Stability .
Plan Stability requires the OPTIMIZER_ADMIN privilege. A two-stage process (capture and regenerate from captured plans) is used based on the following SQL statements:
- Start / stop CAPTURE (optionally, capture cached statements)
- Start / stop APPLY, that is, start the process of matching executed queries with captured abstract plans.
Abstract SQL plans are visible in view ABSTRACT_SQL_PLANS and cached plans can be queried in m_SQL_PLAN_CACHE_. The plan cache includes a value COMPILATION_OPTIONS which indicates what type of plan was used (either normal query optimizer or abstract SQL plan) to create the cached statement. By referring to these two views you can follow the stages of the capture and regeneration process and verify the results of each step.
SQL statements are also available to enable, disable or remove abstract SQL plans from the ABSTRACT_SQL_PLANS table.
## Usage Scenarios
There are essentially two use cases for plan stability:
- Upgrade Scenario : Plan Stability can be used at the time of a system upgrade. In this case, plans are captured before the upgrade and applied afterwards.
##  Note
The feature can be used in the case of an upgrade from SAP HANA 1 to SAP HANA 2: it is available in SAP HANA 1.0 SPS 12 for capture purposes and from HANA 2.0 SPS01 for both capturing and regenerating execution plans.
- Always On Scenario : Plan Stability can also be used in daily operations. This may be required due to changes in data over time which may cause the query optimizer to propose different execution plans which may have a negative impact on performance and memory consumption of a query. An additional preparation step can be used to apply filters so that only specific queries are captured. In the background execution statistics are recorded so that the performance of the query can be measured and the best execution plan can be selected.
This section in this document gives an overview of the process with examples of the main SQL commands used. Full details of the SQL commands are available in the SAP HANA SQL and System Views Reference Guide .
## Applying Filters to the Capture Process
You can use filtering to limit the capture process to specific targets. You can filter either by user name or by setting a value for one of the workload class mapping properties listed below. Filtering by user name is done at run time as the capture process is initiated (an example is given below), but to filter by workload class property the filters must be set in advance so that they apply permanently; they can be removed when no longer required.
The supported mapping property-value pairs that an application can set in the client interface are:
- APPLICATION USER NAME
- APPLICATION NAME
- USER NAME
- SCHEMA
- XS APPLICATION USER NAME
See also 'Properties for Workload Classes and Mappings' in the SAP HANA Administration Guide
Filters which have been applied can be seen in the _SYS_PLAN_STABILITY view, they are shown in JSON format in the FILTERS column.
The following examples illustrate the basic syntax and usage of this feature:
## Example
This example sets two filter conditions. The filters are applied when the capture process is run and captures only plans for App2 where the application user name is 'Test':
```
ALTER SYSTEM ADD ABSTRACT SQL PLAN FILTER 'MyFilter01' SET 'application name'='App2', 'application user name'='TEST';
```
The following example shows how a named filter can be removed; the keyword 'ALL' is also available to remove all filters:
```
ALTER SYSTEM REMOVE ABSTRACT SQL PLAN FILTER 'MyFilter01';
```
## Example
Queries are tested against all existing filters to find a match. In the following example two filters have been defined, if the conditions of the first filter match with a query plan it will be captured, if not, the conditions of the next filter will be tested:
```
ALTER SYSTEM ADD ABSTRACT SQL PLAN FILTER 'filter01' SET 'application name' in ['MyStudio', 'MyBiz'], 'application user name'='TEST'; ALTER SYSTEM ADD ABSTRACT SQL PLAN FILTER 'filter02' SET 'User name'='SYSTEM' 'application name'='MyShop' 'application user name'='Employee25', 'Employee26';
```
This means Plan Stability will capture only queries with application properties where:
Application name is "MyStudio" OR "MyBiz" AND application user name is "TEST"
OR
User name is "SYSTEM" AND application name is "MyShop" AND application user name is "Employee25" OR "Employee26".
## Using Plan Stability
## Capture
The capture process must be explicitly started and stopped manually.
SQL filter keywords are available to restrict the capture to individual users; this example starts the capture process for queries submitted by two named users:
```
ALTER SYSTEM START CAPTURE ABSTRACT SQL PLAN FOR USER TESTUSER1, TESTUSER2;
```
The capture process runs in the background while selected queries are executed. During the capture period an abstract plan will be created for any newly submitted queries. The abstracted plan is in JSON format, it is not a complete plan but contains logical plans and essential properties such as join algorithms and table locations. The view m_ABSTRACT_SQL_PLAN_OVERVIEW shows high-level details of activity related to abstract plans for each instance of host and port, this includes current state (ready, capture, apply), the number of plans, memory consumption and timestamps of the last capture period (start/stop time).
Once all required queries are captured, stop the capture process by executing the CAPTURE command again with the keyword STOP. Verify that the abstract plans have been created by referring to system view ABSTRACT_SQL_PLANS. This view shows key details including the following:
Key Values in ABSTRACT_SQL_PLANS
## Column
## Example / Detail
| ABSTRACT_SQL_PLAN_ID   | 10003, 20003 etc.                                                                                                                                  |
|------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------|
| HOST, PORT             | Abstract plans are saved on the basis of locations (host and port). These details can be updated using the SQL statement UPDATE ABSTRACT SQL PLAN. |
| STATEMENT_STRING       | SQL SELECT statement.                                                                                                                              |
| PLAN_KEY               | Text string.                                                                                                                                       |
| ABSTRACT_SQL_PLAN      | JSON formatted string.                                                                                                                             |
| IS_ENABLED             | Abstract plans can be enabled or disabled as required - see below.                                                                                 |
| IS_VALID               | If FALSE the captured plan cannot be used - see validity details below.                                                                            |
The following example uses the JSON_VALUE() function to select abstract plans for a given schema (the variable $.session_user could be used to select plans for a given user):
SELECT * FROM ABSTRACT_SQL_PLANS WHERE JSON_VALUE(PLAN_KEY, '$.schema') = 'mySchema';
## Capture WITH CACHE Option
An additional option is available to capture an abstraction of plans which are already cached. For this, the keywords WITH SQL PLAN CACHE are required with the START statement:
ALTER SYSTEM START CAPTURE ABSTRACT SQL PLAN WITH SQL PLAN CACHE;
This triggers a job (JOB_NAME Plan Stability ) which runs and stops when complete. You can monitor its progress in view m_JOB_PROGRESS. On completion you can verify that abstract plans have been stored in ABSTRACT_SQL_PLANS using the select statement given in the example above.
## Regenerating Plans (Apply)
To apply the abstract SQL plans run the START APPLY statement:
```
ALTER SYSTEM START APPLY ABSTRACT SQL PLAN;
```
Now any newly compiled SELECT queries are matched where possible with captured abstract SQL plans and the stored abstract plans are used to generate an execution plan. You can stop the apply period with the STOP APPLY statement.
Verification : In the plan cache you can see the source of a cached statement by referring to the COMPILATION_OPTIONS value, this will be either empty (compiled by HANA SQL query optimizer) or ' ABSTRACT SQL PLAN' to indicate that the plan was regenerated from an abstract plan.
## Enabling and Disabling
Abstract SQL plans can be enabled, disabled or removed from the ABSTRACT_SQL_PLANS table by referring to the ID value. Y ou can enable plans either for the system as a whole or just for the current session. The session-only option may be helpful to test the performance of a plan before enabling it for the system as a whole (second example).
The following example uses a list of plan IDs, the keyword ALL is also available to enable all plans (this sets IS_ENABLED to true):
```
ALTER SYSTEM ENABLE ABSTRACT SQL PLAN ENTRY FOR MyPlanID01, MyPlanID02;
```
To enable a plan for the current session set the session variable ENABLE_ABSTRACT_SQL_PLAN_ENTRY value to the plan ID:
```
SET 'ENABLE_ABSTRACT_SQL_PLAN_ENTRY' = 'MyPlanID01';
```
In this case, when the query for this plan is executed a comment is appended to the statement in the plan cache identifying which plan was used, for example, the query SELECT * FROM T1 might be shown as follows:
```
SELECT * FROM T1 /* (session-wise plan stability) compiled_using_plan_id: MyPlanID01 */;
```
If a plan is invalid (IS_VALID= FALSE) it will not be used even if it is selected; the query will be compiled using the current version of the optimizer instead of the captured abstract plan. Reasons for invalidation include failed checks on related objects and JSON deserialization failure. The reason is saved in the NOTES column of the ABSTRACT_SQL_PLANS table.
The cache is automatically cleared in the background when certain events take place, that is, when the apply phase is either started or stopped, when one or more plans are enabled or disabled or when one or more plans are removed.
## Changing Locations
If it is necessary to change the location details (host and port) of the server you can use the SQL statement UPDATE ABSTRACT SQL PLAN. One scenario where this might be required is in high availability systems after a failover when the secondary data center performs a takeover and becomes the production system. In this case, it would be necessary to update the locations of all nodes. The following example illustrates a multi-node scaleout scenario with two hosts, and uses the keyword ALL to update the location information of all plans:
```
ALTER SYSTEM UPDATE ABSTRACT SQL PLAN SET LOCATION site1host1:30040 to site2host1:30040, site1host2:30040 to site2host2:30040 FOR ALL;
```
## Migrating Abstract SQL Plan After an Upgrade
When the system is upgraded, there might be changes to the internal structure of how the Abstract SQL Plan is captured and stored. So, a migration is needed to the current structure after the upgrade. The migration can be done using the following SQL statement:
```
ALTER SYSTEM MIGRATE ABSTRACT SQL PLAN;
```
## Tracing
The trace level for this feature is configurable by setting a configuration parameter in the [trace] section of the indexserver.ini file. The default value of the PlanStability parameter is 'error', set this to 'debug' if necessary for detailed trace information.
## Related Information
Statement Performance Analysis [page 180]
m_ABSTRACT_SQL_PLAN_OVERVIEW System View m_ABSTRACT_SQL_PLAN_STATISTICS System View ALTER SYSTEM {START | STOP} APPLY ABSTRACT SQL PLAN (System Management) Properties for Workload Classes and Mappings SAP Note 2639193