SQL execution plan management (SPM)|V4.4.2| docs|Distributed Database

SQL execution plan management (SPM)

Last Updated：2026-03-06 07:02:42 Updated

SQL Plan Management (SPM) is a plan rollback prevention mechanism that ensures that new plans are used only after they are verified, thereby continuously optimizing and updating plan performance.

Applicability

SPM is currently not supported in OceanBase Database Community Edition.

OceanBase Database supports online SPM evolution. When a newly generated plan is not found in the baseline, an evolution task is automatically initiated to evolve the plan, so plan evolution can be completed automatically without manual intervention.

SPM in OceanBase Database is managed using the DBA_SQL_PLAN_BASELINES and DBA_SQL_MANAGEMENT_CONFIG views and the DBMS_SPM system package.

Execution mechanism of SPM

SPM is implemented based on SQL plan baselines. A SQL plan baseline is a baseline of execution plans that provides a persistent repository for verified execution plan information (such as outline data). Each execution plan can correspond to a plan baseline, and you can use the plan baseline to reproduce an execution plan.

The evolution of OceanBase Database plans is always triggered by plan generation. The execution mechanism of SPM can be summarized as follows:

The first plan generated for an SQL statement is set as the baseline by default and marked as ACCEPT.
When the new plan exists in the plan baseline, and the baseline is FIXED or no other FIXED plan baseline exists, the current plan is used directly.
When a reproducible FIXED baseline exists, it is always used preferentially, and no plan evolution is performed.
When no reproducible FIXED baseline exists but a reproducible ACCEPTED baseline plan does, the new plan evolves with the baseline plan.
If no baseline plan can be reproduced, the newly generated plan is used directly.

Limitations on the use of SPM

OceanBase Database SPM has the following limitations:

Tenants in the backup restore state and standby nodes of primary-standby clusters cannot perform plan evolution.
SQL statements in the sys tenant and inner SQL statements cannot undergo plan evolution.
Plan evolution is not performed for SQL statements that contain INSERT INTO VALUES.
Evolution results are temporarily stored in the local cache of OBServer nodes and are periodically synchronized to the inner table. Therefore, the evolution results of any OBServer node cannot be immediately perceived by other OBServer nodes.

DBA_SQL_PLAN_BASELINES

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

Field name	Data type	Nullable	Description
SIGNATURE	VARCHAR2(128)	NOT NULL	SQL unique identifier generated from the normalized SQL (this field type is not compatible with Oracle).
SQL_HANDLE	VARCHAR2(128)	NOT NULL	Unique text representation of the SQL.
SQL_TEXT	CLOB	NOT NULL	Non-normalized SQL text.
PLAN_NAME	VARCHAR2(128)	NOT NULL	Unique text identifier of the plan.
CREATOR	VARCHAR2(128)		Name of the user who created this plan baseline.
ORIGIN	VARCHAR2(14)		How the plan baseline was created: `MANUAL-LOAD`, `AUTO-CAPTURE`, `MANUAL-SQLTUNE`, or `AUTO-SQLTUNE`.
PARSING_SCHEMA_NAME	VARCHAR2(128)		Always NULL.
DESCRIPTION	VARCHAR2(500)		Description of the plan baseline.
VERSION	VARCHAR2(64)		Database version when the plan baseline was created.
CREATED	TIMESTAMP(6)	NOT NULL	Creation time of the plan baseline.
LAST_MODIFIED	TIMESTAMP(6)		Last modification time of the plan baseline.
LAST_EXECUTED	TIMESTAMP(6)		Last execution time of the plan baseline.
LAST_VERIFIED	TIMESTAMP(6)		Last verification time of the plan baseline.
ENABLED	VARCHAR2(3)		Whether the plan baseline is enabled ('YES') or disabled ('NO').
ACCEPTED	VARCHAR2(3)		Whether the plan baseline is accepted ('YES') or not accepted ('NO').
FIXED	VARCHAR2(3)		Whether the plan baseline is fixed ('YES') or not fixed ('NO').
REPRODUCED	VARCHAR2(3)		Whether the plan baseline is reproducible ('YES') or not reproducible ('NO').
AUTOPURGE	VARCHAR2(3)		Whether the plan baseline is auto-purged ('YES') or not auto-purged ('NO').
ADAPTIVE	VARCHAR2(3)		Whether the plan baseline automatically captured by SPM is already adaptive.
OPTIMIZER_COST	NUMBER		Plan cost estimated by the optimizer when the plan baseline was created.
MODULE	VARCHAR2(64)		Always NULL.
ACTION	VARCHAR2(64)		Always NULL.
EXECUTIONS	NUMBER		Number of plan executions when the plan baseline was created.
ELAPSED_TIME	NUMBER		Total elapsed time of the plan when the plan baseline was created.
CPU_TIME	NUMBER		Total CPU time of the plan when the plan baseline was created.
BUFFER_GETS	NUMBER		Always NULL.
DISK_READS	NUMBER		Always NULL.
DIRECT_WRITES	NUMBER		Always NULL.
ROWS_PROCESSED	NUMBER		Always NULL.
FETCHES	NUMBER		Always NULL.
END_OF_FETCH_COUNT	NUMBER		Always NULL.

DBA_SQL_MANAGEMENT_CONFIG

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

Field name	Data type	Nullable	Description
PARAMETER_NAME	VARCHAR2(128)	NOT NULL	Name of the configuration parameter: `SPACE_BUDGET_PERCENT` or `PLAN_RETENTION_WEEKS`.
PARAMETER_VALUE	NUMBER	NOT NULL	Value of the configuration parameter.
LAST_MODIFIED	TIMESTAMP(6)		Last update time of the parameter value.
MODIFIED_BY	VARCHAR2(128)		User who last updated the parameter value.

DBMS_SPM system package

The DBMS_SPM system package is used to manage SQL plans. It supports loading, modifying, and deleting plan baseline information.

The following table lists the DBMS_SPM subprograms supported in the current version of OceanBase Database and their brief descriptions.

Subprogram	Description
ACCEPT_SQL_PLAN_BASELINE	Accepts a plan as a baseline based on the evolution result.
ALTER_SQL_PLAN_BASELINE	Modifies the attributes of one or more plans in the baseline. Returns the number of baselines modified.
CANCEL_EVOLVE_TASK	Cancels an evolution task in progress.
CONFIGURE	Configures SPM parameters.
DROP_EVOLVE_TASK	Drops an evolution task.
DROP_SQL_PLAN_BASELINE	Drops one or more plan baselines.
LOAD_PLANS_FROM_CURSOR_CACHE	Loads one or more SQL plans from the cursor cache as plan baselines.

ACCEPT_SQL_PLAN_BASELINE

The ACCEPT_SQL_PLAN_BASELINE procedure accepts a plan as a baseline based on the evolution result.

Syntax

Oracle-compatible mode:

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

MySQL-compatible mode:

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

Parameter description

Parameter	Description
database_name	Database name. Required only in MySQL-compatible mode.
sql_handle	SQL identifier, that is, `SQL_ID`.
plan_name	Plan name, that is, Plan Hash Value. If NULL, all plans being evolved for the SQL are accepted.

Example

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 modifies the attributes of one or more plans in the baseline. It returns the number of baselines modified.

Syntax

Oracle-compatible mode:

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;

MySQL-compatible mode:

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;

Parameter description

Parameter	Description
database_name	Database name. Required only in MySQL-compatible mode.
sql_handle	SQL identifier, that is, `SQL_ID`.
plan_name	Plan name, that is, Plan Hash Value. If NULL, the operation applies to all plans for the SQL.
attribute_name	Attribute name of the plan. See Attribute description.
attribute_value	Attribute value of the plan. See Attribute description.

Attribute description

Attribute name	Valid values	Description
enabled	'YES' or 'NO'	'YES' indicates that the plan baseline is enabled. Whether the plan baseline is used depends on whether it is accepted.
fixed	'YES' or 'NO'	'YES' indicates that the plan baseline is used preferentially and automatic evolution is not performed.
autopurge	'YES' or 'NO'	'YES' indicates that the plan baseline is automatically purged after a period of disuse; 'NO' indicates that the plan baseline is never purged.
plan_name	String, up to 30 characters	Name of the plan.
description	String, up to 500 bytes	Description of the plan.

Example

Fix a plan baseline so that the SQL uses only that 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 cancels an evolution task in progress.

Syntax

Oracle-compatible mode:

DBMS_SPM.CANCEL_EVOLVE_TASK (
 task_name INVARCHAR2);

MySQL-compatible mode:

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

Parameter description

Parameter	Description
database_name	Database name. Required only in MySQL-compatible mode.
task_name	Task name, that is, the evolution task name represented by `SQL_ID` in OceanBase Database.

Example

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

CONFIGURE

The CONFIGURE procedure configures SPM parameters.

Syntax

Oracle-compatible mode:

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

MySQL-compatible mode:

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

Parameter description

Parameter	Description
database_name	Database name. Required only in MySQL-compatible mode.
parameter_name	Name of the parameter to set. See SPM parameter description.
parameter_value	Value of the parameter to set. See SPM parameter description.

SPM parameter description

SPM parameter	Description	Valid values	Default
space_budget_percent	Maximum percentage of SYSAUX space used by SPM.	1, 2, ..., 50	10
plan_retention_weeks	Number of weeks after which unused plans are automatically purged.	5, 6, ..., 523	53

Example

Set plans to be automatically purged after 20 weeks of disuse:

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

DROP_EVOLVE_TASK

The DROP_EVOLVE_TASK procedure drops an evolution task.

Syntax

Oracle-compatible mode:

DBMS_SPM.DROP_EVOLVE_TASK  (
  task_name        IN  VARCHAR2);

MySQL-compatible mode:

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

Parameter description

Parameter	Description
database_name	Database name. Required only in MySQL-compatible mode.
task_name	Task name, that is, the evolution task name represented by `SQL_ID` in OceanBase Database.

Example

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

DROP_SQL_PLAN_BASELINE

The DROP_SQL_PLAN_BASELINE function drops one or more plan baselines.

Syntax

Oracle-compatible mode:

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

MySQL-compatible mode:

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

Parameter description

Parameter	Description
database_name	Database name. Required only in MySQL-compatible mode.
sql_handle	SQL identifier, that is, `SQL_ID`. The handle used to identify an SQL statement in the execution plan. When querying a specific execution plan for an SQL statement, you can use sql_handle to precisely specify the target statement when multiple similar SQL statements exist.
plan_name	Plan name, that is, Plan Hash Value. If NULL, all plan baselines for the SQL are dropped.

Example

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 loads one or more SQL plans from the cursor cache as plan baselines.

Oracle-compatible mode:

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;

MySQL-compatible mode:

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;

Parameter description

Parameter	Description
database_name	Database name. Required only in MySQL-compatible mode.
sql_id	`SQL_ID` to load.
plan_hash_value	Plan Hash Value of the plan to load. If NULL, all plans for the specified SQL in the cursor cache are loaded as plan baselines.
is_fixed	`is_fixed` attribute of the loaded plan baseline.
enabled	`enabled` attribute of the loaded 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;
/

SQL execution plan management (SPM)

Applicability

Execution mechanism of SPM

Limitations on the use of SPM

SPM-related views

DBA_SQL_PLAN_BASELINES

DBA_SQL_MANAGEMENT_CONFIG

DBMS_SPM system package

ACCEPT_SQL_PLAN_BASELINE

Syntax

Parameter description

Example

ALTER_SQL_PLAN_BASELINE

Syntax

Parameter description

Attribute description

Example

CANCEL_EVOLVE_TASK

Syntax

Parameter description

Example

CONFIGURE

Syntax

Parameter description

SPM parameter description

Example

DROP_EVOLVE_TASK

Syntax

Parameter description

Example

DROP_SQL_PLAN_BASELINE

Syntax

Parameter description

Example

LOAD_PLANS_FROM_CURSOR_CACHE

Parameter description

Example