SQL plan management (SPM) is a mechanism that stabilizes execution plans (or plans for short) and controls their evolution. SPM ensures that new plans are executed only after verification and the performance of the plans is constantly improved.
SPM
SPM is implemented based on the SQL plan baseline, which stores the information (such as Outline Data) of verified plans and serves as their baseline for execution. Each plan corresponds to a plan baseline and can be reproduced based on it.
The SPM mechanism involves the following processes:
Plan capture
If the optimizer generates a plan for an SQL statement and this SQL statement does not have an existing plan in the SQL plan baseline, the system directly adds the plan to this SQL plan baseline. Otherwise, the system verifies whether this plan performs better than the existing plan and replaces the existing plan in the SQL plan baseline with it if it provides better performance.
Plan evolution
For the same SQL statement, if the new plan captured is different from the plan in the SQL plan baseline, the system compares their performance through Canary tests. If the new plan performs better, the system replaces the existing plan in the SQL plan baseline with the new plan. Otherwise, the existing plan is used.
Plan selection
When the optimizer generates a new plan for an SQL statement, it checks the SQL plan baseline for a verified plan of this SQL statement. A verified plan is prioritized and a new plan is accepted only after it is verified to have better performance.
SPM system variables
SPM uses the following system variables and system packages to implement plan management.
| System variable | Value | Description |
|---|---|---|
| optimizer_capture_sql_plan_baselines | true | If the optimizer generates a plan for an SQL statement and this SQL statement does not have an existing plan in the SQL plan baseline, the system directly adds the plan to the SQL plan baseline; if this SQL statement already has a verified plan in the SQL plan baseline and this verified plan is different from the new one, plan evolution is triggered to verify whether to replace the existing plan with the new one. |
| optimizer_capture_sql_plan_baselines | false | The system does not automatically capture new plans or add them to the plan baseline. |
| optimizer_use_sql_plan_baselines | true | If the optimizer generates a plan for an SQL statement and this SQL statement has an existing plan in the plan baseline, the optimizer uses the existing plan in the plan baseline. The new plan is used only when it is verified to have better performance. |
| optimizer_use_sql_plan_baselines | false | The optimizer generates and executes new plans directly without considering plans in the plan baseline. |
The following table describes how the two system variables work in coordination to implement SPM.
| Value of optimizer_capture_sql_plan_baselines | Value of optimizer_use_sql_plan_baselines | Description |
|---|---|---|
| True | True | Both plan capture and plan evolution are enabled, and the optimizer uses the plan in the plan baseline. |
| True | False | When no plan exists in the baseline, the new plan is captured and added to the plan baseline. Plan evolution is disabled. The optimizer uses the new plan without considering plans in the baseline. |
| False | True | The optimizer uses plans in the plan baseline and it does not capture plans or add them to the plan baseline. The optimizer uses a new plan if no plan of the corresponding SQL statement exists in the SQL plan baseline. |
| False | False | Plan capture and plan evolution are disabled. The optimizer uses new plans instead of plans in the plan baseline. |
DBMS_SPM system package
DBMS_SPM is a command package for SPM operations. It enables you to load, modify, and delete the information of a plan baseline.
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE loads the plan baseline information that corresponds to the execution plan from the plan cache to the __all_tenant_plan_baseline table. Syntax:
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
Note
__all_tenant_plan_baselineis an internal table of OceanBase Database.
Parameters:
| Parameter | Description |
|---|---|
| sql_id | The unique identifier of an SQL statement. |
| plan_hash_value | The unique identifier of an execution plan. If the parameter is set to NULL, all plans with the specified sql_id are processed. |
| fixed | Specifies whether to fix the plan after it is added to the SQL plan baseline. If the plan is fixed, the optimizer uses it directly without plan capture or plan evolution. |
| enabled | Specifies whether the optimizer can use this plan baseline. |
Example:
DECLARE
v_load_plans number;
BEGIN
v_load_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '529F6E6454EF579C7CC265D1F6131D70',
plan_hash_value => 13388268709115914355);
END;
/
ALTER_SQL_PLAN_BASELINE
ALTER_SQL_PLAN_BASELINE modifies some attributes of a plan baseline. Syntax:
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
attribute_name IN VARCHAR2,
attribute_value IN VARCHAR2)
RETURN PLS_INTEGER;
Parameters:
| Parameter | Description |
|---|---|
| sql_handle | The unique identifier of an SQL statement. At present, the value of this parameter is the same as that of SQL_ID. |
| plan_name | The unique identifier of an execution plan. At present, the value of this parameter is the same as that of plan_hash_value. |
| attribute_name | The name of the field to be modified. OceanBase Database supports modification of enabled and fixed fields. |
| attribute_value | The modified value. |
The following example fixes a plan in a plan baseline so that the SQL statement uses only this plan:
DECLARE
v_alter_plans number;
BEGIN
v_alter_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
plan_name => '3388268709115914355',
attribute_name => 'fixed',
attribute_value => 'YES' );
END;
/
DROP_SQL_PLAN_BASELINE
DROP_SQL_PLAN_BASELINE is used to drop a specified plan baseline. Syntax:
DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL)
RETURN PLS_INTEGER;
Example:
DECLARE
v_drop_plans number;
BEGIN
v_drop_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
plan_name => '3388268709115914355' );
END;
/
