使用SPA对数据库迁移进行性能评估

梁铭图 2022-01-10 17:40:00
作者介绍

梁铭图，新炬网络首席架构师。具有十余年数据库运维、数据库设计、数据治理以及系统规划建设经验，拥有Oracle OCM和ACE Director、Togaf企业架构师（鉴定级）、IBM CATE等认证，曾获dbaplus年度MVP、华为云MVP等荣誉，并参与数据资产管理国家标准的编写工作。在数据库运维管理和架构设计、运维体系规划、数据资产管理方面有深入研究。

 

背景

 

迁移数据库或者升级数据库前后，目标数据库可能因为版本、补丁或者其他不可预知的原因造成数据库性能急剧衰退。为避免应用启动上线后，我们才手忙脚乱地做出被动的优化后应，我们需要在数据库正式迁移或升级前使用ORACLE 的SPA（SQL Performance Analyzer）综合评估原库的SQL在目标库执行时没有出现性能衰退。SPA通过比较原库和目标库收集的SQL集合的执行统计数据，最终给我们一个比较结果，通过分析对比结果数据可明确哪些SQL出现性能衰退。查找衰退原因修复问题，确保SQL在迁移后能满足业务需求。

 

SQL性能分析器概要

 

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

 

其过程可分为以下几个主要步骤：

 

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

 

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

 

计算“之前版本”性能：在进行任何更改之前，执行 SQL 语句，收集评估将来的更改对工作量性能的可能影响所需的基线信息。在此阶段收集的信息给出了系统工作量当前状态的一个快照。性能数据包括：执行计划（如由解释计划生成的计划）和执行统计信息（如由占用时间、缓冲获取次数、磁盘读取次数和已处理的行数组成的信息）

 

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

 

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

 

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

 

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

 

实施过程

 

1）准备工作

 

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

 

源端：

 

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


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


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

 

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


CREATE USER SPA IDENTIFIED BY oracle;

GRANT DBA TO SPA;


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


源端导出：


expdp \'/ as sysdba\' dumpfile=SCOTT.dmp directory=MYDUMP 

schemas=SCOTT logfile=scott.log cluster=no

 

目标端：

 

环境准备：创建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测试报告

 

目标端导入：

 

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

 

建立dblink方便传输SQLSET

 

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

 

源端建立SQLSET



源端AWR中的SQL载入到SQL SET（尽可能多的捕捉SQL）

查询snap_id



将SQL加载至SQL SET



查看SQL SET信息



将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 表导入至目标端

 

目标端进行抽取

 

  •  
  •  
create table spa.TAB_TARGET_SQLSET_20190918 asselect * from spa.TAB_SQLSET@spa_link;

 

对目标端中间表进行处理，删除重复数据：

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
delete from spa.TAB_TARGET_SQLSET_20190918 where rowid not in (select max(rowid)                       from spa.TAB_TARGET_SQLSET_20190918                      group by FORCE_MATCHING_SIGNATURE)   and FORCE_MATCHING_SIGNATURE <> 0;

COMMIT;

 

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

目标端建立SQL SET

 

  •  
  •  
  •  
  •  
  •  
  •  

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

 

进行关联

 

 
  •  
  •  
  •  
  •  
  •  
  •  
  •  
BEGIN   DBMS_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;/

 

目标端删除DBLINK

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
declare    cur number := sys.DBMS_SYS_SQL.open_cursor;    rc  number;    CURSOR cur_sql IS        SELECT b.user_id,               a.owner,               a.object_name,               'drop database link ' || object_name exec_sql          from dba_objects a         inner join dba_users b            on (a.owner = b.username)         where object_type = 'DATABASE LINK';begin



    for i in cur_sql loop        SYS.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;/

 

检查：



目标端建立TASK

 

连接到SPA账户

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
declare    l_a varchar2(30);begin    l_a := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name  => 'SQLSET_20190918_TEST',                                           task_name    => 'TASK_20190918',                                           SQLSET_OWNER => 'SPA');end;/

 

源端目标端生成trail文件

 

源端

 

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

 

目标端


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

 

  •  
  •  
  •  
  •  
  •  
  •  
begin    DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name => 'TASK_20190918',                                           parameter => 'LOCAL_TIME_LIMIT',                                           value     => 60);end;/

 

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

 

  •  
  •  
  •  
  •  
  •  
  •  
begin    DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name      => 'TASK_20190918',                                     execution_type => 'TEST EXECUTE',                                     execution_name => 'TARGET_EXEC');end;/

 

执行比较

 

比较执行时间

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
begin    DBMS_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;/

 

比较CPU

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
begin    DBMS_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;/

 

比较逻辑读

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
begin    DBMS_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/oracle

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



报告中详细说明总共分析SQL数量，不支持SQL，得到性能改善，没有变化的SQL的具体数量。



以及总体性能改善了百分比

 


以及得到明显改善的SQL

 

我们还可以通过errors.html可看到执行失败的语句原因分析以及通过unsupport.html可看到不支持的SQL原因分析。

 

小结

 

目前，SPA评估是我们实施数据库迁移和升级项目中一个必备的步骤。它可以协助我们及时提前在数据库迁移正式上线前，发现、分析和优化可能会遇到的各种性能问题，为项目的成功实施起到关键作用。

热门文章
最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节，那么长度值可能…

访客 2024年03月04日

只能说作者太用心了，优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年（即30岁左右），能做到年入40w-50w；有…

访客 2023年08月20日

230721

活动预告
Copyright © 广州新炬网络技术有限公司 All rights reserved. 粤ICP备2024180830号-4