Oracle数据库迁移优化之SPA实战篇

IT那活儿 2021-11-24

1922

点击上方“IT那活儿”，关注后了解更多精彩内容！！！

Oracle SPA概念

迁移数据库，修改数据库参数或者升级数据库后，使用ORACLE 的SPA（SQL Performance Analyzer）包可确定原库的SQL在目标库执行时没有出现性能衰退。SPA通过比较原库和目标库收集的SQL集合的执行统计数据，最终给我们一个比较结果，通过分析对比结果数据可明确哪些SQL出现性能衰退。查找衰退原因修复问题，确保SQL在迁移后能满足业务需求。

使用该工具可以准确地评估更改对组成工作量的 SQL 语句的影响。SQL 性能分析器可帮助预测潜在的更改对 SQL 查询工作量的性能影响。这种功能可向 DBA 提供有关 SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。这样一来，您就可以执行诸如以下操作的操作：在测试环境中进行更改，以确定数据库升级是否会改进工作量性能，使用情景比如数据库升级、实施优化建议、更改方案、收集统计信息、更改数据库参数、更改操作系统和硬件等等。

SQL性能分析器概要

1. 收集 SQL：在这个阶段中，将收集用于表示生产系统中的 SQL 工作量的 SQL 语句集。可以使用 SQL 优化集或自动工作量资料档案库 (AWR) 来捕获要传送的信息。因为 AWR 本质上是捕获高负载的 SQL，所以应考虑修改默认的 AWR 快照设置和捕获的顶级 SQL，以确保 AWR 捕获最大数量的 SQL 语句。这可以确保捕获更加完整的 SQL 工作量。

2. 传送：在这个阶段中，应将得到的工作量结果传送到测试系统。从生产系统导出 STS，然后将 STS 导入到测试系统。

3. 计算“之前版本”性能：在进行任何更改之前，执行 SQL 语句，收集评估将来的更改对工作量性能的可能影响所需的基线信息。在此阶段收集的信息给出了系统工作量当前状态的一个快照。性能数据包括：

执行计划（如由解释计划生成的计划）
执行统计信息（如由占用时间、缓冲获取次数、磁盘读取次数和已处理的行数组成的信息）

4. 进行更改：获得了之前版本数据后，可以实施计划的更改，然后开始查看对性能的影响。

5. 计算“之后版本”性能：在数据库环境中进行了更改之后才执行此步骤。SQL 工作量的每个语句都在虚拟执行（仅收集统计信息）模式下运行，收集与步骤 3 所捕获的信息相同的信息。

6. 比较和分析 SQL 性能：在获得了两个版本的 SQL 工作量性能数据后，可以通过比较之后版本与之前版本的数据来进行性能分析。比较的根据是执行统计信息，如所用时间、CPU 时间和缓冲区获取次数等。

7. 优化回归的 SQL：在此阶段中，已经准确地确认了哪些 SQL 语句在进行数据库更改时可能导致性能问题。在此阶段中可以使用任何一种数据库工具来优化系统。例如，可以对确认的语句使用 SQL 优化指导或访问指导，然后实施相应的建议。也可以使用在步骤 3 中捕获的计划植入 SQL 计划管理 (SPM) 以确保计划保持不变。在实施了任何优化操作后，应重复该过程来创建新的之后版本，然后分析性能差异以确保新的性能是可接受的。

8. 默认情况下：SPA若涉及到DML语句则只有查询部分Query会被执行，但是貌似是从11.2开始可以执行完全的DML了，需要加入参数EXECUTE_FULLDML，但是该参数目前有一些BUG:

Bug 10428438 : WITH EXECUTE_FULLDML ROWS IS ALWAYS SET TO 0 11.2.0.1Bug 14635522 : SPA SHOULD CAPTURE AND REPLAY TRANSACTIONS 11.2.0.3

实验环境

原库为11.2.0.4，目标库为11.2.0.4，向目标库迁移一个test表空间，在目标库中建立spa-sts，首先通过dblink在原库执行sql trial，然后在目标库执行SQL trial，最后比较两个SQL trial。这样就达到了对比迁移后SQL有无性能衰退情况。

1. 源端

环境准备：创建SPA测试专用用户

采集数据（在生产库转化AWR中SQL为SQL Tuning Set，在生产库从现有SQL Tuning Set提取SQL）

导出数据：打包(pack)转化后的SQL Tuning Set，并导出传输到测试服务器

2. 目标端

环境准备：创建SPA测试专用用户

测试准备：导入SQL Tuning Set表，并解包(unpack)，创建SPA分析任务

前期性能：从SQL Tuning Set中转化得出11g的性能Trail

后期性能：在19c测试数据库中执行SQL Tuning Set中SQL，生成19c性能Trail

对比分析：执行对比分析任务，分别按执行时间，CPU时间和逻辑读三个维度进行

汇总报告：取出对比报告，对每个维度分别取出All，Unsupport，Error 3类报告

总结报告：分析汇总报告，优化其中的性能下降SQL，编写SPA测试报告

3. 准备工作

3.1 源端目标端分别建立SPA用户

CREATE USER SPA IDENTIFIED BY oracle;GRANT DBA TO SPA;

源端业务账号迁移至目标端

3.2 源端导出：

expdp \'/ as sysdba\' dumpfile=SCOTT.dmp directory=MYDUMPschemas=SCOTT logfile=scott.log cluster=no

3.3 目标端导入：

impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=SCOTT.DMPlogfile=SCOTT.log cluster=no

3.4 建立dblink方便传输SQLSET

create database link spa_link connect to spa identified by oracle using 'SOURCEDB';

3.5 源端建立SQLSET，连接到SPA账号：

源端AWR中的SQL载入到SQL SET

注：尽可能多的捕捉SQL。

1. 查询snap_id

select max(snap_id), min(snap_id) from dba_hist_snapshot a;

2. 将SQL加载至SQL SET

查看SQL SET信息

SELECT NAME, OWNER, CREATED, STATEMENT_COUNT, LAST_MODIFIEDFROM DBA_SQLSET;

SQL SET 中的SQL信息加载至中间表

BEGIN    DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('TAB_SQLSET', 'SPA', 'USERS');END;/BEGIN    DBMS_SQLTUNE.PACK_STGTAB_SQLSET(SQLSET_NAME => 'SQLSET_20190918_TEST',                                    SQLSET_OWNER => 'SPA',                                    STAGING_TABLE_NAME => 'TAB_SQLSET',                                    STAGING_SCHEMA_OWNER => 'SPA');END;/

源端的SQL SET 表导入至目标端

1. 目标端进行抽取

create table spa.TAB_TARGET_SQLSET_20190918 as

select * from spa.TAB_SQLSET@spa_link;

2. 对目标端中间表进行处理

删除重复数据：

delete from spa.TAB_TARGET_SQLSET_20190918where rowid not in (select max(rowid)from spa.TAB_TARGET_SQLSET_20190918group by FORCE_MATCHING_SIGNATURE)and FORCE_MATCHING_SIGNATURE <> 0;COMMIT;

中间表数据关联到目标端SQL SET

1. 目标端建立SQL SET

BEGINDBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME  => ' SQLSET_20190918_TEST',DESCRIPTION  => 'SQL Set',SQLSET_OWNER => 'SPA');END;/

2. 进行关联

BEGINDBMS_SQLTUNE.Unpack_Stgtab_Sqlset(sqlset_name          => 'SQLSET_20190918_TEST',sqlset_owner         => 'SPA',replace              => TRUE,staging_table_name   => 'TAB_TARGET_SQLSET_20190918',staging_schema_owner => 'SPA');END;/

3. 目标端删除DBLINK

declarecur number := sys.DBMS_SYS_SQL.open_cursor;rc number;CURSOR cur_sql ISSELECT b.user_id,a.owner,a.object_name,'drop database link ' || object_name exec_sqlfrom dba_objects ainner join dba_users bon (a.owner = b.username)where object_type = 'DATABASE LINK';beginfor i in cur_sql loopSYS.DBMS_SYS_SQL.parse_as_user(c => cur,statement     => i.exec_sql,language_flag => DBMS_SQL.native,userID => i.user_id);rc := SYS.DBMS_SYS_SQL.execute(cur);end loop;SYS.DBMS_SYS_SQL.close_cursor(cur);end;/检查：select owner, object_name, object_type, statusfrom dba_objectswhere object_type = 'DATABASE LINK';

目标端建立TASK

连接到SPA账户。

declarel_a varchar2(30);beginl_a := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name  => 'SQLSET_20190918_TEST',task_name    => 'TASK_20190918',SQLSET_OWNER => 'SPA');end;/

源端目标端生成trail文件

注：均在目标端执行。

1. 源端

beginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name      => 'TASK_20190918',execution_type => 'CONVERT SQLSET',execution_name => 'SOURCE_CONVERT_SQLSET');end;/

2. 目标端

为目标端生成trail文件，该操作会在目标端实际执行SQL，因此比较慢：

首先设置执行超过一分钟（生产环境可以按照实际情况酌情增加超时时间）的sql被kill掉：

为目标端生成trail文件，该操作会在目标端实际执行SQL，因此比较慢：

首先设置执行超过一分钟的sql被kill掉：

beginDBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name => 'TASK_20190918',parameter => 'LOCAL_TIME_LIMIT',value     => 60);end;/beginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'TASK_20190918',execution_type => 'TEST EXECUTE',execution_name => 'TARGET_EXEC');end;/

执行比较

1. 比较执行时间

beginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name        => 'TASK_20190918',execution_type   => 'COMPARE PERFORMANCE',execution_name   => 'Compare_elapsed_time',execution_params => dbms_advisor.arglist('execution_name1','SOURCE_CONVERT_SQLSET','execution_name2','TARGET_EXEC','comparison_metric','elapsed_time'));end;/

2. 比较CPU

beginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name        => 'TASK_20190918',execution_type   => 'COMPARE PERFORMANCE',execution_name   => 'Compare_CPU_time',execution_params => dbms_advisor.arglist('execution_name1','SOURCE_CONVERT_SQLSET','execution_name2','TARGET_EXEC','comparison_metric','CPU_TIME'));end;/

3. 比较逻辑读

beginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name        => 'TASK_20190918',execution_type   => 'COMPARE PERFORMANCE',execution_name   => 'Compare_BUFFER_GETS_time',execution_params => dbms_advisor.arglist('execution_name1','SOURCE_CONVERT_SQLSET','execution_name2','TARGET_EXEC','comparison_metric','BUFFER_GETS'));end;/

目标端获取报告

conn spa/oracleset trimspool onset trim onset pages 0set long 999999999set linesize 1000spool spa_report_elapsed_time.htmlSELECT dbms_sqlpa.report_analysis_task('TASK_20190918','HTML','ALL','ALL',top_sql => 1000,execution_name => 'Compare_elapsed_time')FROM dual;spool off;spool spa_report_CPU_time.htmlSELECT dbms_sqlpa.report_analysis_task('TASK_20190918','HTML','ALL','ALL',top_sql => 1000,execution_name => 'Compare_CPU_time')FROM dual;spool off;spool spa_report_buffer_time.htmlSELECT dbms_sqlpa.report_analysis_task('TASK_20190918','HTML','ALL','ALL',top_sql => 1000,execution_name => 'Compare_BUFFER_GETS_time')FROM dual;spool off;spool spa_report_errors.htmlSELECT dbms_sqlpa.report_analysis_task('TASK_20190918','HTML','errors','summary')FROM dual;spool off;spool spa_report_unsupport.htmlSELECT dbms_sqlpa.report_analysis_task('TASK_20190918','HTML','unsupported','all')FROM dual;spool off;/