3.7 迁移前后如何保证性能
每次硬件变更或软件升级，涉及最多的词就是“性能”，升级迁移前后如何保证性能的稳定性，甚至稳中有升，是每个DBA被问及最多的话题。在测试环境中，开发人员通常已进行了广泛的测试，以验证更改所带来的影响。尽管在测试环境中已经进行了充分的测试，但是新系统投入生产后还是会经常遇到意想不到的问题。这主要还是因为测试不是基于实际的工作负载执行的，所以开发人员在验证系统变更时，无法模拟真实的工作负载。那么如何在测试环境中模拟真实的生产压力，就是我们亟需解决的问题了，同时也是目前DBA所面临的最大挑战之一。DBA需要使用有效的方式分析更改对数据库整体性能的影响，并及时采取措施。
3.7.1 数据库重放
Oracle 10.2.0.4及以上版本提供了数据库重放（Database Replay）工具，可用于帮助我们捕获生产系统上的工作负载，在测试系统上重放，模拟真实的工作环境，并提供分析报告，借此我们能够全面评估环境变更所带来的影响，从而发现潜在问题。数据库重放工具可以在数据库级别捕获外部客户端的工作负载，以前开发人员使用负载模拟工具，需要花费数月的时间才能完成的模拟测试，现在几天内就可以完成，这大大地降低了测试成本。
图3-9所示为数据库重放的主要工作流程，具体如下。
图3-9 数据库重放工作流程图
1）在生产系统上将工作负载捕获到捕获文件中。
2）将捕获文件复制到测试系统并进行预处理。
3）在测试系统上重放生产系统的工作负载。
4）获取重放分析报告。
数据库重放主要适用于以下场景。
1）数据库或操作系统升级。
2）PDB级别整合或用户层面整合。
3）配置更改，例如，从单机转换为RAC环境。
4）存储、网络更改。
5）硬件环境迁移。
数据库重放的具体实现原理不在本节的讨论范围之内，感兴趣的读者可以查看Oracle 19c官方文档《Testing Guide》，本节将以实际案例为载体讲解晦涩的技术原理，为大家提供参考借鉴。
1.负载捕获
（1）还原测试环境
数据库重放的第一步是还原测试环境，使测试环境尽可能与生产保持一致，具体可以使用以下几种方式，建议使用其中的快照备库（Snapshot standby）方式。
·RMAN DUPLICATE。
·Snapshot standby。
·Data Pump Import and Export（数据泵导入和导出）。
如果生产环境使用了Database Vault，则需要拥有DBMS_WORKLOAD_CAPTURE和DBMS_WORKLOAD_REPLAY的授权。
1）测试环境恢复时间点应与生产捕获开始的时间点尽可能地接近，从而最大程度地降低差异。
2）为了不影响当前生产系统，我们需要对测试环境中的如下对象进行处理，包括Database links（数据库链接）、External tables（外部表）、Directory objects（目录对象）、URLs、E-mails，强烈建议测试环境使用隔离专用网络。
（2）创建捕获目录
在生产端单独创建存放目录，确保目录下无任何文件，保证权限正确，合理评估大小（可以模拟几分钟时间的运行测试捕获，按比例推断出完全捕获所需要的空间大小）。
shell> mkdir -p /home/oracle/capdir shell> create or replace directory capdir as '/home/oracle/capdir';
对于RAC环境，建议使用共享文件系统。也可以在每个实例节点上使用单独的目录，但最终需要合并各个实例下生成的文件，并传输至测试环境。
（3）创建过滤器（可选）
默认情况下，捕获文件中会记录所有的用户会话。我们也可以使用过滤器有选择地记录，过滤器包含Inclusion（包含）和Exclusion（排除）两种。
·Inclusion：包含需要记录的内容。
·Exclusion：排除不需要记录的内容。
可以使用ADD_FILTER存储过程添加过滤器，命令如下：
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => 'filter_user1',
fattribute => 'USER',
fvalue => 'JASON');
END;
/
以上示例代码中，使用ADD_FILTER添加了一个名为filter_user1的过滤器，用于过滤用户名为JASON的所有会话。其参数说明如下。
·fname：添加过滤器名称。
·fattribute：需要过滤的内容，分为PROGRAM、MODULE、ACTION、SERVICE、INSTANCE_NUMBER和USER。
·fvalue：指定需要具体过滤的值。
指定PROGRAM过滤，命令如下：
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => ' filter_prog',
fattribute => 'PROGRAM',
fvalue => '%OSM%');
END;
/
检查创建的过滤信息，命令如下：
SQL> select * from dba_workload_filters; TYPE ID STATUS NAME ATTRIBUTE VALUE ---------- ---- ------ ------------- --------- -------- CAPTURE NEW FILTER_USER1 USER JASON
代码中，dba_workload_filters视图可用于查询过滤器信息，同样，我们也可以使用DELETE_FILTER存储过程删除过滤器，命令如下：
SQL> BEGIN DBMS_WORKLOAD_CAPTURE.DELETE_FILTER (fname => 'filter_user1'); END; /
（4）运行捕获程序
如果在数据库运行的情况下开启捕获程序，那么之前可能存在部分正在执行的尚未提交的事务，这部分事务就是无法完全捕获的，所以在条件允许的情况下，建议重启数据库进行捕获，即使无法重启，我们也可以以业务周期为单位进行捕获。
此外，为了使测试结果更为准确，在测试环境下建议使用物理恢复的方式，尽可能使捕获负载起点与测试还原点接近。
由于工作负载捕获的限制，部分捕获内容无法在测试环境中进行重放，这种情况就可以使用SQL*Loader从外部文件进行数据加载、闪回查询、非SQL对象访问等操作。
1）开启捕获程序，命令如下：
SQL> BEGIN DBMS_WORKLOAD_CAPTURE.START_CAPTURE ( name => 'pri_capture_1', dir => 'CAPDIR', default_action => 'EXCLUDE', duration => NULL); END; /
在此示例中，捕获程序名为pri_capture_1，duration为null表示未指定时长，需要手动执行停止，并将其转储在CAPDIR的数据库目录中。
default_action为EXCLUDE，表示仅捕获过滤器过滤掉的内容，这里是指捕获Jason用户的所有信息。default_action为INCLUDE，表示捕获过滤器包含的内容，也就是除去Jason用户之外的所有用户信息。
代码中的参数说明如下。
·name：标识符名称。
·dir：目录。
·duration：以秒为单位，指定需要执行捕获的时长，如未指定特定值，则需要手动调用FINISH_CAPTURE停止捕获。
·default_action：过滤器默认设置为INCLUDE，如果需要包含过滤器内容，则需要将该值设置为EXCLUDE。
2）查看捕获状态，命令如下：
SQL> select id capture_id,
name,
directory,
status,
user_calls,
transactions,
AWR_BEGIN_SNAP,
AWR_END_SNAP
from dba_workload_captures;
capture_id NAME DIRECTORY STATUS USER_CALLS TRANSACTIONS AWR_BEGIN_SNAP AWR_END_SNAP
---------- ------------- --------- ---------- ---------- ------------ -------------- ------------
16 pri_capture_1 CAPDIR INPROGRESS 0 0 3215
模拟客户端连接执行数据库操作，由于生产环境会产生实时业务数据，因此这里不需要创建测试数据。
3）停止捕获，命令如下：
SQL> BEGIN DBMS_WORKLOAD_CAPTURE.finish_capture; END; /
数据库alert后台告警日志会有开启和关闭的提示，具体如下：
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(): Starting database capture at 07/03/2020
Fri Jul 03 15:55:38 2020
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(): Stopped database capture (not all sessions
could flush their capture buffers) at 07/03/2020 15:55:37
默认情况下，Oracle 10g R2版本中未启用工作负载捕获功能。可以通过初始化参数PRE_11G_ENABLE_CAPTURE来启用或禁用此功能，而从Oracle 11g R1及以上版本开始，此功能默认开启。在Oracle 10g R2中，工作负载捕获功能的开启和关闭方法分别如下。
开启命令如下：
SQL> @$ORACLE_HOME/rdbms/admin/wrrenbl.sql
关闭命令如下：
SQL> @$ORACLE_HOME/rdbms/admin/wrrdsbl.sql
在capdir目录下生成capfiles和cap这两个捕获文件夹，命令如下：
shell> ls -l drwxr-xr-x 3 oracle asmadmin 4096 Jul 2 08:27 capfiles drwxr-xr-x 2 oracle asmadmin 4096 Jul 2 08:47 cap
（5）获取捕获期间的快照点和其他信息
获取捕获期间的快照点和其他信息，命令如下：
SQL> select id capture_id
name,
directory,
status,
user_calls,
transactions,
AWR_BEGIN_SNAP,
AWR_END_SNAP
from dba_workload_captures;
capture_id NAME DIRECTORY STATUS USER_CALLS TRANSACTIONS AWR_BEGIN_SNAP AWR_END_SNAP
---------- ------------ --------- --------- ---------- ------------ -------------- ------------
16 pri_capture_1 CAPDIR COMPLETED 759 10 3215 3216
视图记录了捕获期间的会话信息，并自动创建了快照点。
（6）导出捕获期间AWR报告（可选）
获取运行期间的AWR数据，以对比重放前后AWR的整体性能，这些数据具有非常大的参考价值。导出捕获期间AWR报告的命令如下：
SQL> exec dbms_workload_capture.export_awr (capture_id => 16);
数据库后台日志导出的信息提示如下：
Fri Jul 03 15:58:09 2020 DM00 started with pid=52, OS id=7005, job SYS.SYS_EXPORT_TABLE_01 Fri Jul 03 15:58:17 2020 DW00 started with pid=53, OS id=7180, wid=1, job SYS.SYS_EXPORT_TABLE_01
也可以通过@?/rdbms/admin/awrrpt.sql获取执行期间的AWR快照。
（7）导出捕获期间的详细信息（可选）
导出捕获期间的详细信息，命令如下：
SQL> set pagesize 0 long 30000000 longchunksize 2000 line 1000 SQL> spool pri_capture.html SQL> select dbms_workload_capture.report (CAPTURE_ID=>16, FORMAT=>'HTML') from dual; SQL> spool off
大家也可以将导出的详细信息自定义为文本（TEXT）格式，只需要把HTML改成TEXT即可。
详细信息中记录了会话、TOP SQL、等待事件等信息，如图3-10所示。
图3-10 捕获期间详细信息报告
与数据库记录捕获相关的视图有：DBA_WORKLOAD_CAPTURES和DBA_WORKLOAD_FILTERS。
2.初始化重放
完成了生产端的工作负载捕获及相同版本测试环境的搭建之后，接下来就是将捕获文件传输至目标端，对数据进行预处理操作，以创建必要的元数据，为下一步的重放操作做准备。
1）传输捕获文件。在目标端创建目录，以用于存放捕获文件，命令如下：
shell> mkdir -p /home/ora19c/replay SQL> CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/home/ora19c/replay';
生产端传输文件至目标端，命令如下：
<!--节点1执行--> shell> scp -r /home/oracle/capfile/* oracle@168.68.19.138:/home/oracle/replay/ <!--节点2执行--> shell> scp -r /home/oracle/capfile/inst* oracle@168.68.19.138: /home/oracle/replay/capfile/
在RAC环境、非共享文件系统的情况下，需要将每个实例节点上生成的文件合并传输至测试环境，且要保持目录结构的一致性，节点2仅需要传输对应的实例文件夹即可。
2）创建过滤器（可选）。默认情况下，初始化所有用户会话。我们也可以使用过滤器选择，用法与负载捕获中的用法相同，故在此不做展开。
3）加载捕获日志。使用Oracle自带的DBMS_WORKLOAD_REPLAY包进行加载，指定加载目录为DB_REPLAY_CAPTURE_DIR，命令如下：
SQL> exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE ('DB_REPLAY_CAPTURE_DIR');
加载完成后，生成一个pp19.3.0.0.0的文件夹，用于记录数据库连接信息和执行数据等，命令如下：
shell> ls -tlr total 4 drwxr-xr-x 2 ora19c oinstall 110 Jul 1 20:54 cap drwxr-xr-x 3 ora19c oinstall 19 Jul 1 20:54 capfiles drwxr-xr-x 3 ora19c oinstall 4096 Jul 1 20:54 pp19.3.0.0.0
3.执行重放
强烈建议重放目标环境使用隔离的专用网络，以免DBLINK等类似操作影响当前生产系统。
（1）重放客户端准备阶段
重放客户端主要用于模拟将客户端连接到测试环境并发起业务请求，客户端至少需要安装Oracle client软件，在条件允许的情况下，建议使用单独的客户端，并将目标端重放目录下的所有文件发送至客户端。
（2）评估重放客户端数量
每个客户端均可模拟出多个会话，使用wrc工具，根据捕捉到的工作负载，评估出需要发起重放的客户端模拟会话的数量。示例代码如下：
shell> wrc MODE=calibrate REPLAYDIR=/home/ora19c/replay Workload Replay Client: Release 12.2.0.1.0 - Production on Thu Jul 2 10:01:46 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Report for Workload in: /oracle/replay ----------------------- Recommendation: Consider using at least 5 clients divided among 2 CPU(s) You will need at least 285 MB of memory per client process. If your machine(s) cannot match that number, consider using more clients. Workload Characteristics: - max concurrency: 377 sessions - total number of sessions: 1205 Assumptions: - 1 client process per 100 concurrent sessions - 4 client processes per CPU - 256 KB of memory cache per concurrent session - think time scale = 100 - connect time scale = 100 - synchronization = TRUE
以上示例输出中，建议使用5个重放客户端。
（3）初始化重放数据
使用自带的INITIALIZE_REPLAY包将必要的元数据加载到所需的表中，命令如下：
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => 'replay',
replay_dir => 'DB_REPLAY_CAPTURE_DIR');
END;
/
SQL> select dbid, id, name, CAPTURE_ID, STATUS, AWR_BEGIN_SNAP, AWR_END_SNAP
from dba_workload_replays;
DBID ID NAME CAPTURE_ID STATUS AWR_BEGIN_SNAP AWR_END_SNAP
---------- -- ----- ---------- ------------- ---------------------------
1589686526 1 replay 1 INITIALIZED
INITIALIZE_REPLAY过程用于将预处理负载数据从DB_REPLAY_CAPTURE_DIR目录加载到数据库中。其中所包含的参数说明具体如下。
·replay_name：标识符自定义名称。
·replay_dir：负载文件目录。
（4）重定向连接串和用户
初始化重放数据后，由于捕获文件中记录的客户端连接信息还是指向原生产端，因此需要重定向客户端连接串，以便会话可以连接到目标数据库并执行重放操作。这里使用DBA_WORKLOAD_CONNECTION_MAP视图查询需要重定向的连接信息，命令如下：
SQL> select conn_id, CAPTURE_CONN, REPLAY_CONN from dba_workload_connection_map;
SQL> begin
dbms_workload_replay.remap_connection(connection_id => 1,
replay_connection => '192.168.238.131/ljw');
end;
/
connection_id为DBA_WORKLOAD_CONNECTION_MAP视图中查询到的CONN_ID，replay_connection为目标端连接信息。
同理，如果目标端不存在与生产端相同的重放用户，那么我们就需要对用户进行重定向操作了。DBA_WORKLOAD_USER_MAP视图记录了需要重定向的用户，命令如下：
SQL> select CAPTURE_USER,REPLAY_USER from DBA_WORKLOAD_USER_MAP;
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.SET_USER_MAPPING (capture_user => 'PROD',
replay_user => 'JASON');
END;
/
重定向完成后，将目标端INITIALIZED模式改为PREPARE REPLAY，命令如下：
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => true);
END;
/
SQL> select dbid, id, name, CAPTURE_ID, STATUS, AWR_BEGIN_SNAP, AWR_END_SNAP
from dba_workload_replays;
DBID ID NAME CAPTURE_ID STATUS AWR_BEGIN_SNAP AWR_END_SNAP
---------- -- ------ ---------- ------- -------------- ------------
1589686526 1 replay 1 PREPARE
（5）启动重放客户端
使用wrc工具启动重放客户端，每个重放客户端将会启动与数据库的一个或多个会话，以驱动工作负载重放。
如果客户端为独立的服务器，则需要将目标端重放目录下的所有文件发送至客户端，并根据评估的数量启动相应数量的客户端。
重放客户端的启动命令如下：
shell> scp -r /home/oracle/replay/* oracle@192.168.239.236:/home/oracle/replay shell> wrc jason/oracle@192.168.238.131/ljw mode=replay replaydir=/home/oracle/replay Workload Replay Client: Release 11.2.0.4.0 - Production on Fri Jul 3 17:08:13 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Wait for the replay to start (17:08:13) Replay client 1 started (17:08:33) Replay client 1 finished (17:17:48)
执行完成后，等待开启重放客户端。如出现ORA-15552或ORA-15561报错，则基本上是由于重定向步骤未完成而导致的问题。
（6）执行重放
执行重放的时间与生产库捕获时长有关，具体如下。
开始执行重放，命令如下：
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/
<!--数据库alert后台日志记录了重放开始的时间。-->
SQL> DBMS_WORKLOAD_REPLAY.START_REPLAY(): Starting database replay at 07/03/2020 17:05:15
SQL> select dbid, id replay_id, name, CAPTURE_ID, STATUS, AWR_BEGIN_SNAP, AWR_END_SNAP
from dba_workload_replays;
DBID REPLAY_ID NAME CAPTURE_ID STATUS AWR_BEGIN_SNAP AWR_END_SNAP
---------- --------- ------ ---------- ----------- -------------- ------------
1589686526 1 replay 1 IN PROGRESS 3610
<!--暂停重放-->
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.PAUSE_REPLAY ();
END;
/
<!--继续重放-->
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.RESUME_REPLAY ();
END;
/
<!--停止重放-->
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
END;
/
<!--STATUS三种状态：
PREPARE：初始
IN PROGRESS：执行
COMPLETED：完成-->
DBMS_WORKLOAD_REPLAY: Database replay ran to completion at 07/03/2020 17:12:28
SQL> select dbid, id replay_id, name, CAPTURE_ID, STATUS, AWR_BEGIN_SNAP, AWR_END_SNAP
from dba_workload_replays;
DBID REPLAY_ID NAME CAPTURE_ID STATUS AWR_BEGIN_SNAP AWR_END_SNAP
---------- --------- ------ ---------- --------- -------------- ------------
1589686526 1 replay 1 COMPLETED 3610 3611
4.获取分析报告
1）导出重放期间的详细信息（可选），命令如下：
SQL> set pagesize 0 long 30000000 longchunksize 2000 line 1000 SQL> spool /home/oracle/replay_report.html SQL> select dbms_workload_replay.report(replay_id => 1,format => 'HTML') from dual; SQL> spool off
报告中记录了重放的详细执行情况。
2）导入捕获期间的AWR报告（可选），命令如下：
SQL> select DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => 'DB_REPLAY_CAPTURE_DIR')
capture_id from dual;
CAPTURE_ID
----------
1
SQL> select dbms_workload_capture.import_awr(capture_id => 1,
staging_schema => 'SYSTEM')
from dual;
3）使用DBMS_WORKLOAD_CAPTURE包生成对比报告，命令如下：
SQL> set serveroutput on
SQL> spool /home/oracle/replay/compare_period_report.html
SQL> declare
v_rlt clob;
v_replay_id1 number := 1;
v_replay_id2 number := null;
v_snum number := 1;
v_length number;
v_char varchar2(32767);
begin
dbms_workload_replay.compare_period_report(replay_id1 => v_replay_id1,
replay_id2 => v_replay_id2,
format => 'HTML',
result => v_rlt);
v_length := dbms_lob.GETLENGTH(v_rlt);
while (v_snum < v_length) loop
v_char := dbms_lob.substr(lob_loc => v_rlt,
amount => 32767,
offset => v_snum);
v_snum := v_snum + 32767;
dbms_output.put_line(v_char);
end loop;
end;
/
SQL> spool off
4）生成AWR性能对比报告。
获取捕获端的dbid、begin_snap和end_snap，命令如下：
SQL> select awr_dbid,status, awr_begin_snap, awr_end_snap from dba_workload_captures; AWR_DBID STATUS AWR_BEGIN_SNAP AWR_END_SNAP ---------- ----------------------- -------------- ------------ 19373648 COMPLETED 3215 3216
获取重放端的信息，命令如下：
SQL> select dbid, STATUS, AWR_BEGIN_SNAP, AWR_END_SNAP
from dba_workload_replays;
DBID STATUS AWR_BEGIN_SNAP AWR_END_SNAP
---------- ------------- -------------- ------------
1589686526 COMPLETED 3610 3611
输入以上查询内容生成AWR对比报告，命令如下：
SQL> set pagesize 0 long 30000000 longchunksize 2000 heading off line 1000
SQL> spool /home/oracle/replay/awrdiff_11g_19c.html
SQL> SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(dbid1 => 19373648,
inst_num1 => 1,
bid1 => 3215,
eid1 => 3216,
dbid2 => 1589686526,
inst_num2 => 1,
bid2 => 3610,
eid2 => 3611));
SQL> spool off
5.收尾清理
收尾清理的命令如下：
SQL> SELECT 'exec dbms_workload_capture.delete_capture_info('||id||')' FROM dba_
workload_captures;
SQL> SELECT 'exec DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO('||id||')' FROM dba_
workload_replays;
3.7.2 SQL性能分析
系统环境的变更可能会导致SQL语句的执行计划发生变化，从而影响SQL的整体性能。如何准确地预测系统更改对SQL性能带来的潜在影响，从而使得我们可以在SQL性能变差之前预先进行调整，或者在SQL性能提升后验证和衡量性能的提升量，这些都需要我们通过工具或脚本来完成。而SQL性能分析（SQL Performance Analyzer，SPA）是我们的最佳选择，如果说数据库重放是迁移前后对整体工作负载可行性的评估，那么SQL性能分析就是对SQL整体业务性能的评估。
SQL性能分析通过自动化识别和评估每个业务SQL语句变更前后的性能差异所产生的总体影响，并提供一份SQL整体性能评估报告，该报告显示了由于语句更改所带来的影响。对于会使性能变差的SQL语句，SPA提供了执行计划详细信息及调整建议，以帮助我们提前纠正任何可能的负面结果，从而使变更对数据库SQL的负面影响降到最低。
我们可以通过SQL性能分析工具分析各种类型的数据库更改对SQL性能产生的影响，这里主要包括以下几大类（如图3-11所示）。
1）数据库升级。
2）PDB级别整合或用户层面整合。
3）操作系统或硬件的配置变更。
4）数据库层参数调整。
5）统计信息更新。
6）SQL优化验证。
SQL性能分析的主要工作流程具体如下。
1）目标环境搭建。
2）根据业务周期捕获生产端需要分析的SQL，并将其存储在SQL调优集（SQL Tunning Set，STS）中。
3）将生产捕获到的SQL调优集打包传输到测试环境并导入。
4）在测试环境上创建SQL性能分析任务。
5）执行SQL调优集中的SQL语句，生成变更前的SQL执行信息。
6）执行系统变更（升级、迁移等）。
7）重新执行SQL调优集中的SQL语句，生成变更后的SQL执行信息。
8）比较和分析变更前后对SQL性能的影响，并生成整体的SQL性能评估报告。
9）调优性能下降的SQL语句。
10）重复执行步骤6到8，直到达到预期的SQL性能目标。
图3-11 SQL性能分析流程图
对于系统范围的更改（例如，数据库升级），不建议在生产系统上使用SQL性能分析，而应该在单独的测试系统上运行，以便能够更好地测试系统更改的效果，同时又不会影响生产。SQL性能分析的具体实现原理不在本节讨论范围之内，感兴趣的读者可以查看官方文档《Testing Guide》。
下面就以真实的升级案例向大家展示SQL性能分析的使用方法。
1.创建测试环境
为了提高测试的准确性，我们通过RMAN物理同步的方式创建了一套与生产几乎相同的测试环境，并将数据库升级到Oracle 19c以进行验证。由于篇幅有限，升级部分不做详细说明。
2.采集SQL信息
在运行SQL性能分析之前，需要在生产系统上捕获一组用于分析的SQL语句，并将捕获的SQL语句放入SQL调优集中，SQL调优集中包含了多个SQL语句及执行统计的信息。除了从生产环境中实时捕获SQL语句之外，我们还可以从现有的AWR、SQL跟踪文件和现有的SQL调优集中导入需要的SQL语句。
在理想情况下，应捕获一个业务周期所有的SQL语句。
1）创建SPA用户，命令如下：
SQL> CREATE USER SPAUSER IDENTIFIED BY ORACLE; SQL> GRANT CONNECT,RESOURCE,DBA TO SPAUSER; SQL> GRANT ADVISOR TO SPAUSER; SQL> GRANT SELECT ANY DICTIONARY TO SPAUSER; SQL> GRANT ADMINISTER SQL TUNING SET TO SPAUSER;
2）创建SQL调优集，命令如下：
SQL> begin
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => 'spa11g',
SQLSET_OWNER => 'SPAUSER');
end;
确认SQL调优集信息，命令如下：
SQL> select owner, name, STATEMENT_COUNT from dba_sqlset; OWNER NAME STATEMENT_COUNT -------- ------------------- --------------- SPAUSER spa11g 0
·SQLSET_NAME：自定义SQL调优集的名称。
·SQLSET_OWNER：指定进行SQL性能分析的用户。
3）获取业务周期AWR快照点，命令如下：
SQL> select min(snap_id) min_id, max(snap_id) max_id
from dba_hist_snapshot
where end_interval_time between
to_date('2020-02-21 00', 'yyyy-mm-dd hh24') and
to_date('2020-02-21 14', 'yyyy-mm-dd hh24')
order by 1;
MIN_ID MAX_ID
---------- ----------
81474 81488
4）SQL调优集加载数据。通过AWR报告中的SQL语句导入SQL调优集进行整体的SQL性能测试，命令如下：
SQL> declare
own VARCHAR2(30) := 'spauser';
bid NUMBER := '&begin_snap';
eid NUMBER := '&end_snap';
stsname VARCHAR2(30) := 'spa11g';
stsowner VARCHAR2(30) := 'SPAUSER';
sts_cur dbms_sqltune.sqlset_cursor;
begin
open sts_cur for
select value(P)
from table(dbms_sqltune.select_workload_repository(bid,
eid,
null,
null,
null,
null,
null,
1,
null,
'ALL')) P;
dbms_sqltune.load_sqlset(sqlset_name => stsname,
populate_cursor => sts_cur,
load_option => 'MERGE',
sqlset_owner => stsowner);
end;
/
10 11 12 Enter value for begin_snap: 81474
old 3: bid NUMBER := '&begin_snap';
new 3: bid NUMBER := '81474';
Enter value for end_snap: 81488
old 4: eid NUMBER := '&end_snap';
new 4: eid NUMBER := '81488';
3.获取并分析SQL对比信息
1）新建SQL性能分析任务。指定SQLSET_OWNER和SQL调优集的参数值，创建SQL性能分析任务TASK_NAME，为后续对比升级前后的SQL性能做准备，命令如下：
SQL> VARIABLE t_name VARCHAR2(100);
SQL> EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'spa11g',
task_name => 'SPA_TASK', SQLSET_OWNER => 'SPAUSER');
·sqlset_name：指定之前创建的SQL调优集的名称。
·task_name：自定义SQL性能分析任务的名称。
·SQLSET_OWNER：指定SPA用户。
2）获取升级前SQL的执行信息。指定分析任务TASK_NAME，获取升级前SQL的执行信息，命令如下：
SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'SPA_TASK',
EXECUTION_NAME => 'EXEC_BEFORE_UPGRADE',
EXECUTION_TYPE => 'CONVERT SQLSET');
end;
/
执行Oracle 11g到Oracle 19c的升级操作，具体升级步骤请查看本章升级部分的内容。
3）获取升级后SQL的执行信息。升级变更完成后，指定分析任务TASK_NAME，获取升级后SQL的执行信息，命令如下：
SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'SPA_TASK',
EXECUTION_NAME => 'EXEC_AFTER_UPGRADE',
EXECUTION_TYPE => 'TEST EXECUTE');
end;
4.执行SQL性能分析对比
1）升级前后SQL性能的对比。得到升级前后SQL执行的信息之后，就可以对比升级它们的执行性能了，下面从不同的维度（SQL执行的时间、SQL执行的CPU时间、SQL执行的逻辑读等）进行对比分析。
对比升级前后SQL执行的时间，命令如下：
SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'SPA_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist
('execution_name1',
'EXEC_BEFORE_UPGRADE',
'execution_name2',
'EXEC_AFTER_UPGRADE',
'comparison_metric',
'elapsed_time'));
end;
/
对比升级前后SQL执行的CPU时间，命令如下：
SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'SPA_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_CPU_time',
execution_params => dbms_advisor.arglist
('execution_name1',
'EXEC_BEFORE_UPGRADE',
'execution_name2',
'EXEC_AFTER_UPGRADE',
'comparison_metric',
'CPU_TIME'));
end;
/
对比升级前后SQL执行的逻辑读，命令如下：
SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'SPA_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_BUFFER_GETS_time',
execution_params => dbms_advisor.arglist
('execution_name1',
'EXEC_BEFORE_UPGRADE',
'execution_name2',
'EXEC_AFTER_UPGRADE',
'comparison_metric',
'BUFFER_GETS'));
end;
/
2）生成SQL性能分析报告，结果如图3-12所示。获取全部结果，命令如下：
SQL> ALTER SESSION SET EVENTS '31156 trace name context forever, level 0x400';
SQL> SET LONG 9999999 longchunksize 100000 linesize 200 head off feedback off echo off
SQL> spool 10g_11g_change.html
SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK',
'HTML',
'TYPICAL',
'ALL',
NULL,
100,
NULL,
NULL,
NULL)
FROM DUAL;
SQL> spool off
SQL> set trimspool on
SQL> set trim on
SQL> set pages 0
SQL> set long 999999999
SQL> set linesize 1000
图3-12 SQL性能分析报告
对比SQL的执行时间，生成对比报告，命令如下：
SQL> spool spa_report_elapsed_time.html
SQL> SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'ALL','ALL',
top_sql=>300,execution_name=>'Compare_elapsed_time') FROM dual;
SQL> spool off;
对比SQL执行的CPU时间，生成对比报告，命令如下：
SQL> spool spa_report_CPU_time.html
SQL> SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'ALL','ALL',
top_sql=>300,execution_name=>'Compare_CPU_time') FROM dual;
SQL> spool off;
对比SQL执行的逻辑读时间，生成对比报告，命令如下：
SQL> spool spa_report_buffer_time.html
SQL> SELECT dbms_sqlpa.report_analysis_task('SPA_TASK','HTML','ALL','ALL',
top_sql=>300,execution_name=>'Compare_BUFFER_GETS_time') FROM dual;
SQL> spool off;
获取错误信息，命令如下：
SQL> spool spa_report_errors.html
SQL> SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'errors','summary')
FROM dual;
SQL> spool off;
获取不支持的对象，命令如下：
SQL> spool spa_report_unsupport.html
SQL> SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'unsupported','all')
FROM dual;
SQL> spool off;
/
从本次迁移前后SQL性能的对比报告来看，一共对比了155条SQL语句，执行计划改变的共计45条，性能改善的共计2条，性能下降的共计5条，性能不变的共计135条，带有报错信息的共计8条，不支持的共计5条。根据性能报告，我们可以有针对性地对性能下降的SQL语句进行优化，具体优化方法请参考第4章。