SPM plan management

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 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 Database is managed using the DBA_SQL_PLAN_BASELINES and DBA_SQL_MANAGEMENT_CONFIG views and the DBMS_SPM 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 the OceanBase Database plan is always triggered by the plan generation process. The execution mechanism of SPM can be summarized as follows:

1. The first plan generated will be marked as the baseline and be marked as ACCEPT.
2. If a new plan exists in the plan baseline and the baseline is FIXED, or if the baseline is not FIXED but no other plan baseline is FIXED, the current plan is directly applied.
3. When a FIXED baseline exists, it is always used for planning, and plans are not evolved.
4. If no fixable baseline exists but an acceptable baseline plan is available, the new plan evolves with the baseline plan.
5. If no baseline plan can be replayed, use the newly generated plan.

Limitations on the use of SPM

The SPM of OceanBase Database has the following limitations:

• Tenants in the backup restore state and the standby primary/standby standby clusters cannot perform planned evolutions.
• SQL statements in the sys tenant and inner SQL statements cannot be plan evolved.
• The plan evolution is not performed for an SQL statement that contains the INSERT INTO VALUES clause.
• The evolution results are temporarily stored in the local cache of the OBServer node. They 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

The DBA_SQL_PLAN_BASELINES view records the SQL plan baselines in SPM.

• oceanbase.DBA_SQL_PLAN_BASELINES (MySQL-compatible mode)

• DBA_SQL_PLAN_BASELINES (Oracle-compatible mode)

The DBA_SQL_MANAGEMENT_CONFIG view records the configuration parameters of SPM.

• oceanbase.DBA_SQL_MANAGEMENT_CONFIG (MySQL-compatible mode)

• DBA_SQL_MANAGEMENT_CONFIG (Oracle-compatible mode)

SPM system packages

In MySQL-compatible mode:

• ACCEPT_SQL_PLAN_BASELINE (MySQL-compatible mode)
• ALTER_SQL_PLAN_BASELINE (MySQL-compatible mode)
• CANCEL_EVOLVE_TASK (MySQL-compatible mode)
• CONFIGURE (MySQL-compatible mode)
• DROP_EVOLVE_TASK (MySQL-compatible mode)
• DROP_SQL_PLAN_BASELINE (MySQL-compatible mode)
• LOAD_PLANS_FROM_CURSOR_CACH (MySQL-compatible mode)

In Oracle-compatible mode:

• ACCEPT_SQL_PLAN_BASELINE (Oracle-compatible mode)
• ALTER_SQL_PLAN_BASELINE (Oracle-compatible mode)
• CANCEL_EVOLVE_TASK (Oracle-compatible mode)
• CONFIGURE (Oracle-compatible mode)
• DROP_EVOLVE_TASK (Oracle-compatible mode)
• DROP_SQL_PLAN_BASELINE (Oracle-compatible mode)
• LOAD_PLANS_FROM_CURSOR_CACH (Oracle-compatible mode)