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:
- By default, the first plan generated for an SQL statement is used as the baseline and is marked as
ACCEPTED. - When a new plan exists in the plan baseline, and the baseline is marked as
FIXED, or no other plan baselines in theFIXEDstate exist, the current plan is used directly. - When a reproducible baseline plan in the
FIXEDstate exists, theFIXEDbaseline plan is used first, and the plan is not evolved. - When no reproducible baseline plan in the
FIXEDstate is available but a reproducible baseline plan in theACCEPTEDstate exists, the new plan and the baseline plan are evolved. - 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 VALUESare 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.
| Field | Data type | Nullable | Description |
|---|---|---|---|
| SIGNATURE | VARCHAR2(128) | No | The unique SQL identifier generated by the normalized SQL statement. This field is not compatible with Oracle Database. |
| SQL_HANDLE | VARCHAR2(128) | No | The unique SQL expression in the form of text. |
| SQL_TEXT | CLOB | No | The unnormalized SQL text. |
| PLAN_NAME | VARCHAR2(128) | No | The unique plan identifier in the form of text. |
| CREATOR | VARCHAR2(128) | The user who created the plan baseline. | |
| ORIGIN | VARCHAR2(14) | The way that the plan baseline was created. Valid values:
|
|
| PARSING_SCHEMA_NAME | VARCHAR2(128) | The value is fixed to NULL. |
|
| DESCRIPTION | VARCHAR2(500) | The description of the plan baseline. | |
| VERSION | VARCHAR2(64) | The database version at the time when the plan baseline was created. | |
| CREATED | TIMESTAMP(6) | No | The time when the plan baseline was created. |
| LAST_MODIFIED | TIMESTAMP(6) | The time when the plan baseline was modified the last time. | |
| LAST_EXECUTED | TIMESTAMP(6) | The time when the plan baseline was executed the last time. | |
| LAST_VERIFIED | TIMESTAMP(6) | The time when the plan baseline was verified the last time. | |
| ENABLED | VARCHAR2(3) | Specifies whether the plan baseline is enabled ('YES') or disabled ('NO'). | |
| ACCEPTED | VARCHAR2(3) | Specifies whether the plan baseline is accepted ('YES') or not ('NO'). | |
| FIXED | VARCHAR2(3) | Specifies whether the plan baseline is fixed ('YES') or not ('NO'). | |
| REPRODUCED | VARCHAR2(3) | Specifies whether the plan baseline is reproducible ('YES') or not ('NO'). | |
| AUTOPURGE | VARCHAR2(3) | Specifies whether the plan baseline is automatically cleaned up ('YES') or not ('NO'). | |
| ADAPTIVE | VARCHAR2(3) | Specifies whether the plan baseline automatically captured by the SPM has been adapted. | |
| OPTIMIZER_COST | NUMBER | The plan cost estimated by the optimizer when the plan baseline was created. | |
| MODULE | VARCHAR2(64) | The value is fixed to NULL. |
|
| ACTION | VARCHAR2(64) | The value is fixed to NULL. |
|
| EXECUTIONS | NUMBER | The number of times the plan had been executed since the plan baseline was created. | |
| ELAPSED_TIME | NUMBER | The total time consumed for the plan since the plan baseline was created. | |
| CPU_TIME | NUMBER | The total CPU time consumed by the plan since the plan baseline was created. | |
| BUFFER_GETS | NUMBER | The value is fixed to NULL. |
|
| DISK_READS | NUMBER | The value is fixed to NULL. |
|
| DIRECT_WRITES | NUMBER | The value is fixed to NULL. |
|
| ROWS_PROCESSED | NUMBER | The value is fixed to NULL. |
|
| FETCHES | NUMBER | The value is fixed to NULL. |
|
| END_OF_FETCH_COUNT | NUMBER | The value is fixed to NULL. |
DBA_SQL_MANAGEMENT_CONFIG
The DBA_SQL_MANAGEMENT_CONFIG view records the SPM parameters. The following table describes the fields in the view.
| Field | Data type | Nullable | Description |
|---|---|---|---|
| PARAMETER_NAME | VARCHAR2(128) | No | The name of the parameter. Valid values:
|
| PARAMETER_VALUE | NUMBER | No | The value of the parameter. |
| LAST_MODIFIED | TIMESTAMP(6) | The time when the parameter value was updated the last time. | |
| MODIFIED_BY | VARCHAR2(128) | The user that last updated the parameter value. |
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.
| Subprogram | Description |
|---|---|
| ACCEPT_SQL_PLAN_BASELINE | Accepts a plan as a baseline based on the results of plan evolution. |
| ALTER_SQL_PLAN_BASELINE | Modifies the attributes of one plan or a group of plans in a baseline. The return value indicates the number of baselines that were modified. |
| CANCEL_EVOLVE_TASK | Cancels an ongoing evolution task. |
| CONFIGURE | Sets some SPM parameters. |
| DROP_EVOLVE_TASK | Drops an evolution task. |
| DROP_SQL_PLAN_BASELINE | Drops one or more plan baselines. |
| LOAD_PLANS_FROM_CURSOR_CACH | Reads one or more SQL plans from the cursor cache as the plan baseline. |
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
| Parameter | Description |
|---|---|
| database_name | The name of the database. This parameter is required only in MySQL mode. |
| sql_handle | The identifier of the SQL statement, which is the value of SQL_ID. |
| plan_name | The name of the plan, which is the plan hash value. If the value is NULL, all evolving plans are accepted for an SQL statement. |
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
| Parameter | Description |
|---|---|
| database_name | The name of the database. This parameter is required only in MySQL mode. |
| sql_handle | The identifier of the SQL statement, which is the value of SQL_ID. |
| plan_name | The name of the plan, which is the plan hash value. If the value is NULL, all evolving plans are accepted for an SQL statement. |
| attribute_name | The name of the plan attribute. For more information, see Attributes. |
| attribute_value | The value of the plan attribute. For more information, see Attributes. |
Attributes
| Attribute | Valid values | Description |
|---|---|---|
| enabled | YES and NO |
YES indicates that the plan baseline is valid. A plan baseline will be used only when it is accepted. |
| fixed | YES and NO |
YES indicates that the current plan baseline is used preferentially and is not subject to automatic evolution. |
| autopurge | YES and NO |
YES indicates that the plan baseline will be automatically evicted if it is not used for a specified period. NO indicates that the plan baseline will never be evicted. |
| plan_name | A string that can contain up to 30 characters in length | The name of the plan. |
| description | A string that can contain up to 500 bytes in length | The description of the plan. |
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
| Parameter | Description |
|---|---|
| database_name | The name of the database. This parameter is required only in MySQL mode. |
| task_name | The name of the task, which is the name of the evolution task represented by SQL_ID in OceanBase Database. |
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
| Parameter | Description |
|---|---|
| database_name | The name of the database. This parameter is required only in MySQL mode. |
| parameter_name | The name of the parameter to be set. For more information, see SPM parameters. |
| parameter_value | The value of the parameter. For more information, see SPM parameters. |
SPM parameters
| Parameter | Description | Valid values | Default value |
|---|---|---|---|
| space_budget_percent | The maximum percentage of the SYSAUX space for SPM. | 1, 2, ..., 50 | 10 |
| plan_retention_weeks | The number of weeks for which a plan has not been used before it is automatically evicted. | 5, 6, ..., 523 | 53 |
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
| Parameter | Description |
|---|---|
| database_name | The name of the database. This parameter is required only in MySQL mode. |
| task_name | The name of the task, which is the name of the evolution task represented by SQL_ID in OceanBase Database. |
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
| Parameter | Description |
|---|---|
| database_name | The name of the database. This parameter is required only in MySQL mode. |
| sql_handle | The identifier of the SQL statement, which is the value of SQL_ID. |
| plan_name | The name of the plan, which is the plan hash value. If the value is NULL, all evolving plans are accepted for an SQL statement. |
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
| Parameter | Description |
|---|---|
| database_name | The name of the database. This parameter is required only in MySQL mode. |
| sql_id | SQL_ID of the SQL statement to load. |
| plan_hash_value | The plan hash value of the plan to load. If the value is NULL, all plans for the specified SQL in the plan cache are loaded as the plan baseline. |
| is_fixed | The is_fixed attribute of the plan baseline to load. |
| enabled | The enabled attribute of the plan baseline to load. |
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;
/