SQL Plan Management (SPM) is a plan rollback prevention mechanism that ensures that new plans are used only after they are verified. This ensures continuous optimization and updating of plans, thereby continuously improving plan performance.
OceanBase AI Database supports online SPM evolution. When a new plan is not found in the baseline, an evolution task is automatically initiated to evolve the plan without requiring manual intervention.
SPM in OceanBase AI 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 and provides a persistent repository for verified execution plans (including outline data). You can use a plan baseline to reproduce an execution plan.
The evolution of an OceanBase AI Database plan is always triggered by the plan generation process. The execution mechanism of SPM can be summarized as follows:
- The plan generated for an SQL statement the first time is defaulted as the baseline and is
ACCEPTED. - If a new plan exists in the plan baseline and the baseline is
FIXED, or if no otherFIXEDplan baseline exists, the current plan is used directly. - If a reproducible
FIXEDbaseline exists, theFIXEDbaseline plan is always used preferentially, and plan evolution is not performed. - If no reproducible
FIXEDbaseline exists but a reproducibleACCEPTEDbaseline plan exists, the new plan evolves with the baseline plan. - If no baseline plan can be reproduced, the newly generated plan is used directly.
SPM limitations
SPM in OceanBase AI Database has the following limitations:
- Tenants in the backup restore state and standby servers in primary/standby clusters cannot perform plan evolution.
- SQL statements in the sys tenant and inner SQL statements cannot be plan evolved.
- Plan evolution is not performed for an SQL statement that contains the
INSERT INTO VALUESclause. - The evolution results are temporarily stored in the local cache of the OBServer node and are periodically synchronized to the inner table. Therefore, the evolution results of any OBServer node cannot be immediately perceived by other OBServer nodes.
SPM-related views
DBA_SQL_PLAN_BASELINES
The DBA_SQL_PLAN_BASELINES view records the plan baselines for SQL statements in SPM. The meanings of each field in this view are described in the following table.
Field |
Data type |
Nullable |
Description |
|---|---|---|---|
| SIGNATURE | VARCHAR2(128) | NOT NULL | The unique SQL identifier generated by normalized SQL. |
| SQL_HANDLE | VARCHAR2(128) | NOT NULL | The unique text-based representation of an SQL statement. |
| SQL_TEXT | CLOB | NOT NULL | The unnormalized SQL text. |
| PLAN_NAME | VARCHAR2(128) | NOT NULL | The unique text-based identifier of the plan. |
| CREATOR | VARCHAR2(128) | The username of the user who created the plan baseline. | |
| ORIGIN | VARCHAR2(14) | The method used to create the plan baseline. Valid values:
|
|
| PARSING_SCHEMA_NAME | VARCHAR2(128) | Always NULL. | |
| DESCRIPTION | VARCHAR2(500) | The description of the plan baseline. | |
| VERSION | VARCHAR2(64) | The database version when the plan baseline was created. | |
| CREATED | TIMESTAMP(6) | NOT NULL | The time when the plan baseline was created. |
| LAST_MODIFIED | TIMESTAMP(6) | The time when the plan baseline was last modified. | |
| LAST_EXECUTED | TIMESTAMP(6) | The last execution time of the plan baseline. | |
| LAST_VERIFIED | TIMESTAMP(6) | The 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 adopted ('YES') or not adopted ('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 automatically purged ('YES') or not automatically purged ('NO'). | |
| ADAPTIVE | VARCHAR2(3) | Whether the plan baseline automatically captured by SPM is adaptive. | |
| OPTIMIZER_COST | NUMBER | The plan cost estimated by the optimizer when the plan baseline was created. | |
| MODULE | VARCHAR2(64) | Always NULL. | |
| ACTION | VARCHAR2(64) | Always NULL. | |
| EXECUTIONS | NUMBER | The number of plan executions when the plan baseline was created. | |
| ELAPSED_TIME | NUMBER | The total elapsed time of the plan when the plan baseline was created. | |
| CPU_TIME | NUMBER | The 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 in SPM. The meanings of each field in this view are described in the following table.
Field |
Data type |
Nullable |
Description |
|---|---|---|---|
| PARAMETER_NAME | VARCHAR2(128) | NOT NULL | The name of the parameter, for example:
|
| PARAMETER_VALUE | NUMBER | NOT NULL | The value of the parameter. |
| LAST_MODIFIED | TIMESTAMP(6) | The last time when the parameter value was updated. | |
| MODIFIED_BY | VARCHAR2(128) | The user who last updated the parameter value. |
DBMS_SPM system package
The DBMS_SPM system package is used to manage SQL plans and supports loading, modifying, and dropping plan baseline information.
The following table lists the DBMS_SPM subprograms supported by the current version of OceanBase AI Database and provides brief descriptions.
Subprogram |
Description |
|---|---|
| ACCEPT_SQL_PLAN_BASELINE | Accepts a plan as the baseline based on the result of the plan evolution. |
| ALTER_SQL_PLAN_BASELINE | Modifies the attributes of one 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 parameters for SPM. |
| DROP_EVOLVE_TASK | Drops an evolution task. |
| DROP_SQL_PLAN_BASELINE | Drops one or more plan baselines. |
| LOAD_PLANS_FROM_CURSOR_CACHE | 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 result of an evolution plan.
Syntax
The syntax 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. |
| sql_handle | The identifier of the SQL statement, that is, the SQL_ID. |
| plan_name | The name of the plan, which is also the plan hash value. If the value is NULL, all evolving plans for the SQL statement are accepted. |
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 or a group of plans in a baseline. The return value indicates the number of baselines that were modified.
Syntax
The syntax 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. |
| sql_handle | The identifier of the SQL statement, that is, the SQL_ID. |
| plan_name | The name of the plan, which is also the plan hash value. If the value is NULL, all evolving plans for the SQL statement are accepted. |
| 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 value |
Description |
|---|---|---|
| enabled | 'YES' or 'NO' | 'YES' indicates that the plan baseline is valid. Whether the final plan baseline is used depends on whether it is accepted. |
| fixed | 'YES' or 'NO' | 'YES' indicates that the current plan baseline is used preferentially and automatic evolution is not performed. |
| autopurge | 'YES' or 'NO' | 'YES' indicates that a plan baseline is automatically purged if it has not been used for a period of time. 'NO' indicates that a plan baseline is never purged. |
| plan_name | A string of up to 30 characters. | The name of the plan. |
| description | A string of up to 500 bytes. | The description of the plan. |
Examples
After a plan baseline is set to fixed, the SQL statement 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 is used to cancel an ongoing evolution task.
Syntax
The syntax 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. |
| task_name | The name of the task. In the current OceanBase AI Database, the SQL_ID is used as the name of the evolution task. |
Examples
BEGIN
DBMS_SPM.CANCEL_EVOLVE_TASK ('SYS_AUTO_SPM_EVOLVE_TASK');
END;
CONFIGURE
The CONFIGURE procedure is used to set some parameters for SPM.
Syntax
The syntax 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. |
| parameter_name | The name of the parameter to be set. For more information, see SPM parameters. |
| parameter_value | The value of the parameter to be set. For more information, see SPM parameters. |
SPM parameters
SPM parameter |
Description |
Valid value |
Default value |
|---|---|---|---|
| space_budget_percent | Sets the maximum percentage of SYSAUX space for SPM. | 1,2,...,50 | 10 |
| plan_retention_weeks | Plans that remain unused for several weeks are automatically purged. | 5,6,...,523 | 53 |
Examples
Set SPM to automatically purge unused plans after 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 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. |
| task_name | The name of the task. In the current OceanBase AI Database, the SQL_ID is used as the name of the evolution task. |
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 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. |
| sql_handle | The identifier of the SQL statement, that is, the SQL_ID. It identifies the handle of an SQL statement. When you query a specific execution plan for an SQL statement, if multiple similar SQL statements exist, you can use sql_handle to precisely locate the desired statement. |
| plan_name | The name of the execution plan, which is also the plan hash value. If the value is NULL, all evolving plans for the SQL statement are accepted. |
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 plan baselines.
The syntax 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. |
| sql_id | The SQL_ID to be loaded. |
| plan_hash_value | The plan hash value of the plan to be loaded. If the value is NULL, all plans of the specified SQL in the plan cache are loaded as the plan baseline. |
| is_fixed | The is_fixed attribute of the loaded plan baseline. |
| enabled | The enabled attribute of the loaded plan baseline. |
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;
/