SQL plan management
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 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.
SPM 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
The following variables and system packages are used for plan management through SPM.
| 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 the settings of the variables.
| 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
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: Note
Table _ _all_tenant_plan_baseline is an internal table of OceanBase Database.
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;
See the following table for the description of parameters.
| Parameter | Description |
|---|---|
| sql_id | The unique identifier of an SQL statement. |
| plan_hash_value | The unique identifier of a plan. All plans with the same sql_id are processed if the value is empty. |
| 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;
See the following table for the description of parameters.
| Parameter | Description |
|---|---|
| sql_handle | The unique identifier of an SQL statement. Use sql_id as the SQL handle. |
| plan_name | The unique identifier of a plan. Use plan_hash_value as the plan name. |
| attribute_name | The name of the field to be modified. OceanBase Database supports modification of enabled and fixed fields. |
| attribute_value | The modified value. |
After a plan is fixed in a plan baseline, the SQL statement only uses this plan. Example:
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 deletes a 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;
/