ACTS Blog Selection
We use machine learning technology to do auto-translation. Click "English" on top navigation bar to check Chinese version.
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
- 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
- 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 (Amazon Web Services):
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
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
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 (
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.