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:
Create a SQL Plan Baselines (Plans) for SQL statements in source database
Create a staging table to store the captured SQL Plan Baselines in source database
Pack the captured SQL Plan Baselines
Export the staging table with Oracle Data Pump
Copy the dump file to a location accessible by target database
Import and verify the SQL Plans in the target Amazon RDS for Oracle environment
Unpack the imported SQL Plan Baselines into the target database
Enable SQL Plan Baselines usage in the target Amazon RDS for Oracle environment
Prerequisites
Before we begin, make sure you meet the following requirements.
Source and target databases running Oracle version 12.2 or above.
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.
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 (AWS):
Example for excluding a particular schema:
Example for including SQL statements containing the text “Financial”:
Example for excluding SQL statements containing the text “Financial”:
Verify SQL Plan Baselines collected in source oracle database
You can query dba_sql_plan_baselines to confirm that execution plans are being captured:
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):
Pack the captured SQL Plan Baselines
Pack the captured baselines into the staging table created in the previous step:
Export the staging table with DataPump
Run the following command to export the staging table data.
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.
Verify the import is successful by checking the file creation and reading the import log:
Unpack the imported SQL Plan Baselines into the target database
This step is done using the dbms_spm package.
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 AWS Command Line Interface (AWS CLI). All of these steps can also be performed from AWS Management Console.
Create a custom parameter group
Modify the two parameters required for SPM
Associate this parameter group with your RDS for Oracle instance (requires a restart of the instance)
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 AWS).
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 AWS cloud.
Devinder Singh is SA Manager with Amazon Web Service (AWS). He has 25+ years of experience working with various database and storage technologies. Devinder focuses on helping customers on their journey to AWS and helping them architect highly available and scalable database solution based on various relational and NoSql AWS 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.
Read MoreAWS Database Blog
