All Products
Search
Document Center

SQL plan management

Last Updated: Jun 18, 2021

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:

  1. 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.

  1. 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.

  1. 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.

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.

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;
/