将 Oracle 工作负载迁移到适用于 Oracle 的亚马逊 RDS 时保持查询计划的稳定性

客户将其应用程序迁移到适用于 Oracle 的新亚马逊关系数据库服务 (Amazon RDS) 环境时面临的一个常见挑战是查询计划更改导致性能意外下降。以下任何迁移因素都可能导致查询计划离题，在这篇文章中，我们概述了一种主动解决数据库迁移项目中这些绊脚石的策略：

数据库对象统计数据的变化
对数据库配置参数的更改
对架构的更改，例如新索引
数据库的主要版本升级

在这篇文章中，我们提出了一个主动的解决方案，应将其添加到您的迁移策略中，以避免性能问题和延迟用户接受。为了成功迁移，我们强烈建议在新环境中对应用程序进行全面的迭代测试，同时考虑各种因素。适用于 Oracle 的 Amazon RDS 使客户能够方便而经济地创建和删除测试数据库实例。此外，从快照重新创建测试环境是一项轻松的任务。

解决方案概述

解决查询计划回归问题涉及评估新索引、收集优化器统计数据和反复进行性能测试的过程。这种方法可能会也可能不会提高性能，也不能保证查询计划的稳定性。为了真正匹配本地性能，建议实施 SQL 计划管理 (SPM) 。SPM 是一种预防机制，它使优化器能够自动管理执行计划，从而确保数据库仅使用已知或经过验证的查询执行计划。SPM 使用一种名为 SQL 计划基线的机制，这是一组公认的 SQL 语句最佳执行计划，优化器在执行期间会使用这些计划。SPM 的主要组件有：

计划捕获：捕获和存储一组 SQL 语句的最佳计划
计划选择：选择适当的 SQL 计划基准来运行 SQL 语句以防止性能下降
计划演变：自动和手动接受或拒绝新计划基准，并将其添加到现有批准的 SQL 计划基准中。

我们建议在迁移到 Amazon RDS for Oracle 时利用 SPM 的功能来改进或保留查询执行计划。SPM 提供了一种在源数据库上捕获最佳查询执行计划并将其传输到目标数据库的机制。新数据库仅使用已转移的计划基准中已接受的计划，并继续生成和存储新计划，这些计划以后可以演变或升级为已接受的计划。因此，SPM 可以确保优化器在迁移到 Amazon RDS for Oracle 后不会使用次优的执行计划，从而帮助消除或最大限度地减少性能下降并稳定性能。

下图说明了我们提出的将稳定计划从源 Oracle 数据库迁移到目标 Oracle RDS 的解决方案：

演练概述

在下一节中，我们将介绍在源数据库中启用 SPM、捕获查询执行计划、打包捕获查询执行计划并将其传输到目标环境以及在 Amazon RDS for Oracle 中启用 SPM 的步骤。

该解决方案包含以下关键步骤：

为源数据库中的 SQL 语句创建 SQL 计划基线（计划）
创建暂存表以将捕获的 SQL 计划基线存储在源数据库中
打包捕获的 SQL 计划基准
使用 Oracle 数据泵导出暂存表
将转储文件复制到目标数据库可以访问的位置
在目标 Amazon RDS for Oracle 环境中导入并验证 SQL 计划
将导入的 SQL 计划基准解压缩到目标数据库中
在目标 Amazon RDS for Oracle 环境中启用 SQL 计划基准

先决条件

在我们开始之前，请确保您满足以下要求。

运行 Oracle 版本 12.2 或更高版本的源数据库和目标数据库。
运行 Oracle 企业版的源数据库和目标数据库。
尽管在 Oracle 数据库标准版2 (SE2) 上也支持 SPM，但存在一些限制，但这篇文章重点介绍 Oracle 数据库企业版。
使用 RDS 主用户执行活动。

为源数据库中的 SQL 语句创建 SQL 计划基线（计划）

SQL 计划是自动或手动捕获的。在这篇文章中，我们演示了 SQL 计划的自动捕获。您可以使用 DBMS_SPM 包手动捕获 S QL 计划。

通过将数据库配置参数 OPT IMIZ ER_CAPTURE_SQL_PLAN_BASELINES 设置为 TRUE，在迁移前至少 7 天开始捕获源数据库中的 SQL 计划基线。

捕获期可能会因您的数据库工作负载模式而异。目的是捕获数据库中运行的大多数查询。

默认情况下，设置 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 参数会开始捕获所有多次执行的查询的计划 。从 Oracle Database 12c 版本 2 起，可以使用以下命令在计划的自动捕获中包含或排除特定的架构或查询文本模式：

包含特定架构 (亚马逊云科技) 的示例：

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

排除特定架构的示例：

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

包括包含文本 “财务” 的 SQL 语句的示例：

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

排除包含文本 “财务” 的 SQL 语句的示例：

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

验证在源预言机数据库中收集的 SQL 计划基准

您可以查询 dba_sql_plan_baselines 以确认正在捕获执行计划 ：

select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

您需要多次运行 SQL 语句才能捕获它们。未捕获数据字典 SQL 语句。

创建暂存表以将捕获的 SQL 计划基线存储在源数据库中

在任何现有架构（例如 DBA）中创建暂存表（例如 SPM_STAGE_TAB ）：

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

打包捕获的 SQL 计划基准

将捕获的基准打包到上一步中创建的暂存表中：

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;
/

使用 DataPump 导出暂存表

运行以下命令导出暂存表数据。

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

dumpfile staging_table.dmp 是在源 系统数据库服务器中由 EXP_DP_DIR 定义的目录 中创建的。它包含在源系统上捕获的所有 SQL 计划。

将备份转储文件复制到目标数据库可以访问的位置

由于无法访问适用于 Oracle 的 Amazon RDS 上的底层文件系统，因此您必须将转储文件 staging_table.dmp 复制到亚马逊简单存储服务 (Amazon S3)，然后将其下载到 Amazon RDS for Oracle 实例服务器并导入，或者使用 DMS _FILE_TRANSFER 复制到 RD S for Oracle。

在目标 Amazon RDS for Oracle 环境中导入并验证 SQL 计划

将迁移后的转储文件导入目标 RDS for Oracle 数据库中的现有架构（即 CLOUD_DBA ）。

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;
/

通过检查文件创建情况并读取导入日志来验证导入是否成功：

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'));

将导入的 SQL 计划基准解压缩到目标数据库中

此步骤是使用 dbms_spm 包 完成的。

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;
/

在目标 Amazon RDS for Oracle 环境中启用 SQL 计划基准

在将数据库配置参数 o ptimiz er_use_sql_baselines 设置为 true 之后，您可以使用导入的 SQL 计划基线。以下步骤列出了如何使用亚马逊云科技命令行接口 (亚马逊云科技 CLI) 在 Amazon RD S f or Oracle 中启用该参数。所有这些步骤也可以通过亚马逊云科技管理控制台执行。

创建自定义参数组

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

修改 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"

将此参数组与您的 RDS for Oracle 实例相关联（需要重启实例）

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

在目标系统上解压缩基准后，任何后续的 SQL 语句执行都将使用这些基线，这样可以确保性能不会下降。

结论

在这篇文章中，我们演示了在将运行在 Oracle 数据库上的工作负载迁移到 Amazon RDS for Oracle 时，如何主动利用 SPM 来避免查询性能下降。我们建议您查看这些分享见解和最佳实践（管理您的 SQL 计划、分析性能管理以及在亚马逊云科技上运行 Oracle 数据库的最佳实践）的相关文章。

如果您有任何意见或问题，请将其留在评论部分。

作者简介

Anita Singh 是一位高级数据库专家解决方案架构师，拥有 20 多年的经验，主要设计数据库技术的解决方案。她为客户提供设计和构建涉及数据库技术的高度可扩展、可用和高性能应用程序的指导。她积极参与客户向亚马逊云科技云或在亚马逊云科技云内部进行数据库迁移和现代化之旅并提供建议。

德文德·辛格 是亚马逊网络服务（亚马逊云科技）的南非经理。他在使用各种数据库和存储技术方面拥有 25 年以上的经验。Devinder 专注于帮助客户踏上亚马逊云科技之旅，帮助他们基于各种关系和 NoSQL 亚马逊云科技数据库服务架构高度可用和可扩展的数据库解决方案。不与客户合作时，你总能发现 Devinder 喜欢长途徒步旅行或骑自行车。

Peter Ticali 是一名首席解决方案架构师，致力于帮助媒体和娱乐客户实现业务转型，并通过新产品和服务进行创新。凭借三十多年的专业经验，他有机会为建筑界做出贡献，该建筑向数百万人直播视频，包括两次超级碗、PPV，甚至一场皇家婚礼。此前，他曾在教育科技、广告和公共关系领域担任董事和首席技术官职务。此外，他还是一位出版摄影记者。