Real-time SQL plan management in RDS for Oracle

Starting with Oracle Database 26ai (26.0.0.0), Amazon RDS for Oracle supports real-time SQL plan management. This feature prevents SQL performance regressions by detecting execution plan changes, evaluating them against earlier plans, and selecting the best-performing plan from a SQL plan baseline as needed. This process is transparent and doesn't require manual intervention.

To enable real-time SQL plan management on RDS for Oracle, you use DBMS_SPM.CONFIGURE to turn on the auto evolve task. You then use the rdsadmin.rdsadmin_spm_util package to configure the SYS-owned task parameters.

Overview

A SQL performance regression occurs when the Oracle optimizer chooses a new execution plan that performs worse than a previous plan. Real-time SQL plan management automates the detection and prevention of these regressions.

When real-time SQL plan management is enabled, the database does the following:

Requirements

To use real-time SQL plan management, your DB instance must meet the following requirements:

Enabling real-time SQL plan management

To enable real-time SQL plan management, complete the following two steps.

Step 1: Turn on the auto SPM evolve task

Run the following statement as the master user or as a user with the DBA role:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'AUTO');
END;
/

Step 2: Configure the SYS-owned task parameters

The SYS_AUTO_SPM_EVOLVE_TASK task is owned by SYS. RDS for Oracle provides the rdsadmin.rdsadmin_spm_util package for this purpose.

Set ACCEPT_PLANS to TRUE so that the database accepts evolved plans automatically:

EXEC rdsadmin.rdsadmin_spm_util.set_evolve_task_accept_plans('TRUE');

(Optional) Set the alternate plan source to AUTO:

EXEC rdsadmin.rdsadmin_spm_util.set_evolve_task_alternate_plan_source('AUTO');

Verifying the configuration

To verify that real-time SQL plan management is enabled, run the following query:

SELECT parameter_value
FROM DBA_SQL_MANAGEMENT_CONFIG
WHERE parameter_name = 'AUTO_SPM_EVOLVE_TASK';

The query returns AUTO.

To verify the task parameter settings, run the following query:

SELECT parameter_name, parameter_value
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
  AND parameter_name IN ('ACCEPT_PLANS', 'ALTERNATE_PLAN_SOURCE');

Disabling real-time SQL plan management

To disable real-time SQL plan management, run the following statement:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'OFF');
END;
/

rdsadmin_spm_util procedures

The rdsadmin.rdsadmin_spm_util package provides the following procedures.

Monitoring SQL plan baselines

After you enable real-time SQL plan management, you can monitor the plan baselines that the database manages automatically:

SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES
ORDER BY LAST_MODIFIED DESC;

Considerations

Consider the following when you use real-time SQL plan management:

Related resources