SQL plan management

SQL plan management (SPM) is a mechanism that prevents execution plans from being rolled back. SPM ensures that new plans are executed only after verification and the performance of the plans is constantly improved.

OceanBase Database supports online SPM evolution. To be specific, when OceanBase Database finds that a new plan is not in the baseline, it automatically starts an evolution task to evolve the plan. In this way, plans automatically evolve without manual intervention.

The SPM mechanism of OceanBase Database uses the DBA_SQL_PLAN_BASELINES and DBA_SQL_MANAGEMENT_CONFIG views and the DBMS_SPM system package to manage execution plans.

Implementation mechanism of 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.

In OceanBase Database, plan evolution is always triggered by the generation of a new plan. The execution mechanism of SPM is as follows:

1. By default, the first plan generated for an SQL statement is used as the baseline and is marked as ACCEPTED.
2. When a new plan exists in the plan baseline, and the baseline is marked as FIXED, or no other plan baselines in the FIXED state exist, the current plan is used directly.
3. When a reproducible baseline plan in the FIXED state exists, the FIXED baseline plan is used first, and the plan is not evolved.
4. When no reproducible baseline plan in the FIXED state is available but a reproducible baseline plan in the ACCEPTED state exists, the new plan and the baseline plan are evolved.
5. If no baseline plan is reproducible, the newly generated plan is used directly.

Limitations of SPM

SPM of OceanBase Database is subject to the following limitations:

• Tenants under recovery and standby clusters in primary/standby deployment cannot implement plan evolution.
• SQL statements of the sys tenant and inner SQL statements are not subject to plan evolution.
• SQL statements containing INSERT INTO VALUES are not subject to plan evolution.
• Evolution results are temporarily stored in the local cache of the OBServer node and are periodically synchronized to the right-side table. Therefore, an OBServer node cannot immediately perceive evolution results of other OBServer nodes.

SPM-related views

DBA_SQL_PLAN_BASELINES

The DBA_SQL_PLAN_BASELINES view records the plan baselines of SQL statements in SPM. The following table describes the fields in the view.

DBA_SQL_MANAGEMENT_CONFIG

The DBA_SQL_MANAGEMENT_CONFIG view records the SPM parameters. The following table describes the fields in the view.

DBMS_SPM system package

The DBMS_SPM system package allows you to manage SQL plans. It allows you to load, modify, and delete the information of a plan baseline.

The following table describes the DBMS_SPM subprograms supported by the current OceanBase Database version.

ACCEPT_SQL_PLAN_BASELINE

The ACCEPT_SQL_PLAN_BASELINE procedure is used to accept a plan as the baseline based on the results of plan evolution.

Syntax

The syntax in Oracle mode is as follows:

DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE  (
    sql_handle        IN VARCHAR,
    plan_name         IN VARCHAR := NULL);

The syntax in MySQL mode is as follows:

DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE  (
     database_name     VARCHAR(65535),
     sql_handle        VARCHAR(65535),
     plan_name         VARCHAR(65535) DEFAULT NULL);

Parameters

Examples

DECLARE
  v_accept_plans NUMBER;
BEGIN
  v_accept_plans := DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE(
     sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
     plan_name => '3388268709115914355'
     );
END;
/

ALTER_SQL_PLAN_BASELINE

The ALTER_SQL_PLAN_BASELINE function is used to modify the attributes of one plan or a group of plans in a baseline. The return value indicates the number of baselines that were modified.

Syntax

The syntax in Oracle mode is as follows:

DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
  sql_handle        IN VARCHAR,
  plan_name         IN VARCHAR := NULL,
  attribute_name    IN VARCHAR,
  attribute_value   IN VARCHAR
)
RETURN PLS_INTEGER;

The syntax in MySQL mode is as follows:

DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
  database_name     VARCHAR(65535),
  sql_handle        VARCHAR(65535),
  plan_name         VARCHAR(65535) DEFAULT NULL,
  attribute_name    VARCHAR(65535),
  attribute_value   VARCHAR(65535)
)
RETURN DECIMAL;

Parameters

Attributes

Examples

Fix a plan as 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;
/

CANCEL_EVOLVE_TASK

The CANCEL_EVOLVE_TASK procedure is used to cancel an ongoing evolution task.

Syntax

The syntax in Oracle mode is as follows:

DBMS_SPM.CANCEL_EVOLVE_TASK (
 task_name INVARCHAR2);

The syntax in MySQL mode is as follows:

DBMS_SPM.CANCEL_EVOLVE_TASK  (
  database_name     VARCHAR(65535),
  task_name         VARCHAR(65535));

Parameters

Examples

BEGIN
  DBMS_SPM.CANCEL_EVOLVE_TASK ('SYS_AUTO_SPM_EVOLVE_TASK');
END;

CONFIGURE

The CONFIGURE procedure is used to set some SPM parameters.

Syntax

The syntax in Oracle mode is as follows:

DBMS_SPM.CONFIGURE (
    parameter_name    IN VARCHAR2,
    parameter_value   IN NUMBER
  );

The syntax in MySQL mode is as follows:

DBMS_SPM.CONFIGURE (
    database_name     VARCHAR(65535),
    parameter_name    VARCHAR(65535),
    parameter_value   DECIMAL
  );

Parameters

SPM parameters

Examples

Specify that a plan will be automatically evicted after it has not been used for 20 weeks.

BEGIN
  DBMS_SPM.CONFIGURE ('plan_retention_weeks', 20);
END;

DROP_EVOLVE_TASK

The DROP_EVOLVE_TASK procedure is used to drop an evolution task.

Syntax

The syntax in Oracle mode is as follows:

DBMS_SPM.DROP_EVOLVE_TASK  (
  task_name        IN  VARCHAR2);

The syntax in MySQL mode is as follows:

DBMS_SPM.DROP_EVOLVE_TASK  (
  database_name     VARCHAR(65535),
  task_name         VARCHAR(65535));

Parameters

Examples

BEGIN
  DBMS_SPM.DROP_EVOLVE_TASK ('SYS_AUTO_SPM_EVOLVE_TASK');
END;

DROP_SQL_PLAN_BASELINE

The DROP_SQL_PLAN_BASELINE function is used to drop one or more plan baselines.

Syntax

The syntax in Oracle mode is as follows:

DBMS_SPM.DROP_SQL_PLAN_BASELINE (
  sql_handle     IN VARCHAR2,
  plan_name      IN VARCHAR2 := NULL
)
RETURN PLS_INTEGER;

The syntax in MySQL mode is as follows:

DBMS_SPM.DROP_SQL_PLAN_BASELINE (
  database_name     VARCHAR(65535),
  sql_handle        VARCHAR(65535),
  plan_name         VARCHAR(65535) DEFAULT NULL
)
RETURN DECIMAL;

Parameters

Examples

DECLARE
  v_drop_plans number;
BEGIN
  v_drop_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
     sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
     plan_name => '3388268709115914355' );
END;
/

LOAD_PLANS_FROM_CURSOR_CACHE

The LOAD_PLANS_FROM_CURSOR_CACHE function is used to read one or more SQL plans from the plan cache as the plan baseline.

The syntax in Oracle mode is as follows:

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  sql_id              IN VARCHAR,
  plan_hash_value     IN NUMBER   := NULL,
  fixed               IN VARCHAR  := 'NO',
  enabled             IN VARCHAR  := 'YES'
)
RETURN PLS_INTEGER;

The syntax in MySQL mode is as follows:

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  database_name     VARCHAR(65535),
  sql_id            VARCHAR(65535),
  plan_hash_value   DECIMAL DEFAULT NULL,
  is_fixed          VARCHAR(65535) DEFAULT 'NO',
  enabled           VARCHAR(65535) DEFAULT 'YES'
)
RETURN DECIMAL;

Parameters

Examples

DECLARE
  v_load_plans number;
BEGIN
  v_load_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
     sql_id => '529F6E6454EF579C7CC265D1F6131D70',
     plan_hash_value => 13388268709115914355);
END;
/