Maintain query plan stability when migrating your Oracle workload to Amazon RDS for Oracle

A common challenge faced by customers migrating their application to a new Amazon Relational Database Service (Amazon RDS) for Oracle environment is unexpected performance degradations caused by query plans changes. Any of the following migration factors can lead to query plan digression and in this post, we outline a strategy to proactively address these stumbling points in your database migration project:

• Change in statistics for database objects
• Changes to database configuration parameters
• Changes to the schema, such as new indexes
• Major version upgrade of the database

In this post, we present a proactive solution that should be added to your migration strategy to avoid performance issues and delayed user acceptance. For a successful migration, we strongly suggest carrying out thorough and iterative tests of the application in the new environment, taking into account various factors. Amazon RDS for Oracle enables customers to conveniently and cost-effectively create and delete test DB instances. Moreover, recreating a test environment from snapshots is an effortless task.

Solution overview

Addressing query plan regression involves a process of evaluating new indexes, gathering optimizer statistics, and repeated performance testing. This approach may or may not improve performance, and it also does not guarantee query plan stability. To truly match on-premises performance SQL Plan Management (SPM) implementation is recommended. SPM is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified query execution plans. SPM uses a mechanism called SQL Plan Baseline, which is a set of accepted optimal execution plans for a SQL statement that the optimizer utilizes during execution. The main components of SPM are:

• Plan Capture: Capturing and storing optimal plans for a set of SQL statements
• Plan Selection: Choose the appropriate SQL Plan Baselines to run SQL statements to prevent performance degradation
• Plan Evolution: Accept or Reject a new plan Baselines automatically and manually, and add it to the existing approved SQL plan baselines.

We recommend utilizing the functionality of SPM to improve or preserve query execution plans when migrating to Amazon RDS for Oracle. SPM provides a mechanism to capture optimal query execution plans on the source database and transfer them to target database. The new database uses only the accepted plans from the transferred plan baselines and continue to generate and store new plans, which can later be evolved or promoted to accepted plans. Thus, SPM can help remove or minimize performance regression and stabilizes performance by ensuring that optimizer does not use suboptimal execution plans post-migration to Amazon RDS for Oracle.

The following diagram illustrates our proposed solution of migrating stable plans from Source Oracle database to target RDS for Oracle:

Walkthrough Overview

In the following section, we walk through the steps to enable SPM in a source database, capture the query execution plans, pack and transfer the capture query execution plans to target environment and enabling SPM in Amazon RDS for Oracle.

The solution features the following key steps:

1. Create a SQL Plan Baselines (Plans) for SQL statements in source database
2. Create a staging table to store the captured SQL Plan Baselines in source database
3. Pack the captured SQL Plan Baselines
4. Export the staging table with Oracle Data Pump
5. Copy the dump file to a location accessible by target database
6. Import and verify the SQL Plans in the target Amazon RDS for Oracle environment
7. Unpack the imported SQL Plan Baselines into the target database
8. Enable SQL Plan Baselines usage in the target Amazon RDS for Oracle environment

Prerequisites

Before we begin, make sure you meet the following requirements.

1. Source and target databases running Oracle version 12.2 or above.
2. Source and target database running Enterprise Edition of Oracle.
   Though SPM is also supported with some limitation on Oracle Database Standard Edition2(SE2), this post focusses on Oracle Database Enterprise Edition.
3. Use the RDS master user to perform the activity.

Create a SQL Plan Baselines (Plans) for SQL statements in source database

SQL Plans are captured automatically or manually. In this post, we demonstrate the automatic capture of SQL Plans. You can manually capture SQL Plans using the DBMS_SPM package.

Initiate capturing of SQL Plan Baselines in Source database at least 7 days prior to migration by setting the database configuration parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE .

The capture period might vary depending on your database workload pattern. The intention is to capture most of the queries run in the database.

By default, setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter starts capturing plans for all queries executed more than once. From Oracle Database 12c Release 2 onwards, a particular schema or query text pattern can be included or excluded from the auto capturing of plans by using the following command:

Example for including a particular schema (Amazon Web Services):

BEGIN
DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'AWS', TRUE);
END;
/

Example for excluding a particular schema:

BEGIN
DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'AWS',FALSE);
END;
/

Example for including SQL statements containing the text “Financial”:

BEGIN
DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', '%Financial%', TRUE);
END;
/

Example for excluding SQL statements containing the text “Financial”:

BEGIN
DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', '%Financial%', FALSE);
END;
/

Verify SQL Plan Baselines collected in source oracle database

You can query dba_sql_plan_baselines to confirm that execution plans are being captured:

select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

You need to run the SQL statements more than once so they are captured. Data dictionary SQL statements are not captured.

Create a staging table to store the captured SQL Plan Baselines in source database

Create the staging table (such as SPM_STAGE_TAB ) in any existing schema (such as DBA ):

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE (table_name => 'SPM_STAGE_TAB',table_owner => 'DBA') ;
END;
/

Pack the captured SQL Plan Baselines

Pack the captured baselines into the staging table created in the previous step:

DECLARE
          v_plan_cnt NUMBER;
BEGIN
         v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'SPM_STAGE_TAB',
table_owner => 'DBA',
enabled    => 'yes');
END;
/

Export the staging table with DataPump

Run the following command to export the staging table data.

expdp system/<password>  dumpfile=staging_table.dmp logfile=exp_stage_tab.log directory=EXP_DP_DIR tables=dba.spm_stage_tab

A dumpfile staging_table.dmp is created in the directory defined by EXP_DP_DIR in the source systems database server. This contains all the SQL plans captured on the source system.

Copy the backup dump file to a location accessible by target database

Since there is no access to the underlying file system on Amazon RDS for Oracle, you must copy the dump file staging_table.dmp to Amazon Simple Storage Service (Amazon S3) and then download it to Amazon RDS for Oracle instance server and import it or use DMS_FILE_TRANSFER to copy to RDS for Oracle.

Import and verify the SQL Plans in the target Amazon RDS for Oracle environment

Import the migrated dump file into an existing schema (i.e., CLOUD_DBA ) in the target RDS for Oracle database.

DECLARE
  v_hdnl NUMBER;
BEGIN
  v_hdnl := DBMS_DATAPUMP.OPEN( 
    operation => 'IMPORT', 
    job_mode  => 'TABLE', 
    job_name  => null);
  DBMS_DATAPUMP.ADD_FILE( 
    handle    => v_hdnl, 
    filename  => 'exp_spm_stage_tab.dmp', 
    directory => 'DATA_PUMP_DIR', 
    filetype  => dbms_datapump.ku$_file_type_dump_file);
  DBMS_DATAPUMP.ADD_FILE( 
    handle    => v_hdnl, 
    filename  => 'imp_spm_stage.log', 
    directory => 'DATA_PUMP_DIR', 
    filetype  => dbms_datapump.ku$_file_type_log_file);
  DBMS_DATAPUMP.METADATA_FILTER(v_hdnl,'SCHEMA_EXPR','IN (''DBA'')');
  DBMS_DATAPUMP.METADATA_REMAP(v_hdnl,'REMAP_SCHEMA','DBA','CLOUD_DBA');
  DBMS_DATAPUMP.METADATA_FILTER(v_hdnl,name => 'NAME_EXPR',value => 'IN (''SPM_STAGE_TAB'')',OBJECT_TYPE => 'TABLE');
  DBMS_DATAPUMP.SET_PARAMETER(v_hdnl,'TABLE_EXISTS_ACTION','SKIP');
  DBMS_DATAPUMP.START_JOB(v_hdnl);
END;
/

Verify the import is successful by checking the file creation and reading the import log:

SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')) ORDER BY MTIME;

SELECT * FROM TABLE    (rdsadmin.rds_file_util.read_text_file(p_directory => 'DATA_PUMP_DIR', p_filename  => 'imp_spm_stage.log'));

Unpack the imported SQL Plan Baselines into the target database

This step is done using the dbms_spm package.

set serveroutput on
declare
pls pls_integer;
begin
pls := dbms_spm.unpack_stgtab_baseline(table_name => 'SPM_STAGE_TAB', table_owner => 'CLOUD_DBA');
dbms_output.put_line('Plans Unpacked:'||pls);
end;
/

Enable SQL Plan Baselines usage in the target Amazon RDS for Oracle environment

You can use the imported SQL plan baselines after setting the database configuration parameter optimizer_use_sql_baselines to true. The following steps list how to enable the parameter in Amazon RDS for Oracle using the Amazon Web Services Command Line Interface (Amazon Web Services CLI). All of these steps can also be performed from Amazon Web Services Management Console .

Create a custom parameter group

aws rds create-db-parameter-group \
 	--db-parameter-group-name mydbparametergroup \
        --db-parameter-group-family oracle-ee-19  
        --description "My new parameter group"

Modify the two parameters required for SPM

aws rds modify-db-parameter-group \
--db-parameter-group-name mydbparametergroup \
--parameters "ParameterName='optimizer_capture_sql_plan_baselines',ParameterValue=TRUE, ApplyMethod=immediate"
                   
aws rds modify-db-parameter-group \
--db-parameter-group-name mydbparametergroup \
--parameters "ParameterName='optimizer_use_sql_plan_baselines' ,ParameterValue=TRUE,ApplyMethod=immediate"

Associate this parameter group with your RDS for Oracle instance (requires a restart of the instance)

aws rds modify-db-instance \
--db-instance-identifier mydbinstance --db-parameter-group-name mydbparametergroup \
--no-apply-immediately

Once the baselines are unpacked on the target system, then any subsequent execution of SQL statements will use these baselines, which assures no performance regression.

Conclusion

In this post, we demonstrated how you can proactively utilize SPM to avoid query performance degradation when migrating workloads running on Oracle databases to Amazon RDS for Oracle. We recommend you review these related posts that share insights and best practices ( Managing your SQL Plan , Analyzing Performance Management , and Best Practices for Running Oracle Database on Amazon Web Services ).

If you have any comments or questions, leave them in the comments section.

About the Authors

Anita Singh is a Senior Database Specialist Solutions Architect with over 20 years of experience, architecting solutions focused on Database technologies.  She provides customers guidance in designing and building highly scalable, available and performant applications involving databases technologies. She actively engages and advices customers on their database migration and modernization journey to or within the Amazon Web Services cloud.

Devinder Singh is SA Manager with Amazon Web Service (Amazon Web Services). He has 25+ years of experience working with various database and storage technologies. Devinder focuses on helping customers on their journey to Amazon Web Services and helping them architect highly available and scalable database solution based on various relational and NoSql Amazon Web Services Database services. When not working with customer you can always find Devinder enjoying long hikes or biking.

Peter Ticali is a Principal Solutions Architect focused on helping Media & Entertainment customers transform their business and innovate with new products and services. With over three decades of professional experience, he’s had the opportunity to contribute to architecture that stream live video to millions, including two Super Bowls, PPVs, and even a Royal Wedding. Previously he held Director, and CTO roles in the EdTech, advertising & public relations space. Additionally, he is a published photo journalist.