Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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