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

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.1
Bug 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=MYDUMP
schemas=SCOTT logfile=scott.log cluster=no

3.3 目标端导入:

impdp \'/ as sysdba\' directory=DATA_PUMP_DIR 
dumpfile=SCOTT.DMP
logfile=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账号:Oracle数据库迁移优化之SPA实战篇

源端AWR中的SQL载入到SQL SET

注:尽可能多的捕捉SQL。


1. 查询snap_id

select max(snap_id), min(snap_id) from dba_hist_snapshot a;Oracle数据库迁移优化之SPA实战篇

2. 将SQL加载至SQL SETOracle数据库迁移优化之SPA实战篇

查看SQL SET信息

SELECT NAME, OWNER, CREATED, STATEMENT_COUNT, LAST_MODIFIED
FROM DBA_SQLSET;

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

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 asselect * from spa.TAB_SQLSET@spa_link;

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

删除重复数据:

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

1. 目标端建立SQL SET

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

2. 进行关联

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

3. 目标端删除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;
/
检查:
select owner, object_name, object_type, status
from dba_objects
where object_type = 'DATABASE LINK';

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

目标端建立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文件

注:均在目标端执行。

1. 源端

begin
DBMS_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掉:

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

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

执行比较

1. 比较执行时间

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

2. 比较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;
/

3. 比较逻辑读

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 on
set trim on
set pages 0
set long 999999999
set linesize 1000

spool spa_report_elapsed_time.html
SELECT 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.html
SELECT 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.html
SELECT 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.html
SELECT dbms_sqlpa.report_analysis_task('TASK_20190918',
'HTML',
'errors',
'summary')
FROM dual;
spool off;

spool spa_report_unsupport.html
SELECT dbms_sqlpa.report_analysis_task('TASK_20190918',
'HTML',
'unsupported',
'all')
FROM dual;
spool off;
/

查看报告

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

试验环境总共执行4条SQL,1条不支持,2条得到性能改善,1条没有变。Oracle数据库迁移优化之SPA实战篇

性能改善了78.09%。Oracle数据库迁移优化之SPA实战篇

得到明显改善的SQL TOP3。

通过errors.html可看到执行失败的语句原因分析。

通过unsupport.html可看到不支持的SQL原因分析。

发表评论

登录后才能评论
联系客服
联系客服
分享本页
返回顶部