Oracle SQL性能分析SPA实施

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

1.SPA介绍

SPA（SQL Performance Analyzer），是一个SQL性能分析工具，主要是针对数据库的变更进行性能分析，这些变更包括数据库版本升级，参数调整，平台架构变动等操作，提到SPA就得提到另外一个概念STS(SQL Tuning Set)，它是一系列SQL的集合，这些SQL来自于AWR或者Cursor Cache中。
Oracle Database 11g 引入了 SQL 性能分析器；使用该工具可以准确地评估更改对组成工作量的 SQL 语句的影响。SQL 性能分析器可帮助预测潜在的更改对 SQL 查询工作量的性能影响。这种功能可向 DBA 提供有关 SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。这样一来，您就可以执行诸如以下操作的操作：在测试环境中进行更改，以确定数据库升级是否会改进工作量性能。

这些更改可以包括（但不限于）以下任何一种更改：
1.数据库升级
2.实施优化建议
3.更改方案
4.收集统计信息
5.更改数据库参数
6.更改操作系统和硬件

2.SPA步骤

本次给大家介绍Oracle数据库从11g迁移到新服务器的SPA步骤：

在生产库端：
1.环境准备：创建SPA测试专用用户(可省略，使用sys用户)
2.采集数据：在生产库转化AWR中的SQL Tuning Set
3.导出数据：打包(pack)转化后的SQL Tuning Set，并导出传输到目标服务器

在测试库端：
1.环境准备：创建SPA测试专用用户(可省略，使用sys用户)
2.测试准备：导入SQL Tuning Set表，并解包(unpack)，创建SPA分析任务
3.前期性能：从SQL Tuning Set中转化得出11g的性能Trail
4.后期性能：在19c测试数据库中执行SQL Tuning Set中SQL，生成19c性能Trail
5.对比分析：执行对比分析任务，分别按执行时间，CPU时间和逻辑读三个维度进行
6.汇总报告：取出对比报告，对每个维度分别取出Regressed，Unsupport，Error和全部内容4类报告

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

3.源库SPA实施

3.1 环境准备

为更好的进行SPA测试，创建一个专门的监控用户，避免影响正常用户

##在11G源库和19C目标库做如下操作
SQL> CREATE USER SPA IDENTIFIED BY SPA DEFAULT TABLESPACE SYSAUX;
SQL> GRANT DBA TO SPA;

3.2 获取AWR快照的边界

## 在11G源库做操作
SET LINES 200 PAGES 1000
COL SNAP_TIME FOR A22
COL MIN_ID NEW_VALUE MINID
COL MAX_ID NEW_VALUE MAXID

SQL> SELECT MIN(SNAP_ID) MIN_ID, MAX(SNAP_ID) MAX_ID
FROM DBA_HIST_SNAPSHOT
WHERE END_INTERVAL_TIME > trunc(sysdate)-10
ORDER BY 1;

    MIN_ID     MAX_ID
---------- ----------
         1         23

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iytacHxm-1666131840415)(C:\Users\wangd\AppData\Roaming\Typora\typora-user-images\image-20221015090012323.png)]$

3.3 创建SQL Set

## 在11G源库做操作

1.删除
SQL> EXEC DBMS_SQLTUNE.DROP_SQLSET (SQLSET_NAME  => 'SQLSET_SFIT',SQLSET_OWNER => 'SYS');

2.创建
SQL> EXEC DBMS_SQLTUNE.CREATE_SQLSET (SQLSET_NAME  => 'SQLSET_SFIT',DESCRIPTION  => 'SQL Set Create at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), SQLSET_OWNER => 'SYS');

3.4 SQL数据载入到SQL Set

## 在11G源库做操作

DECLARE

  SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

  OPEN SQLSET_CUR FOR

    SELECT VALUE(P) FROM TABLE(

           DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( &MINID, &MAXID,

                        'PARSING_SCHEMA_NAME NOT IN (''SYS'', ''SYSTEM'')',

                        NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;

  DBMS_SQLTUNE.LOAD_SQLSET(

               SQLSET_NAME => 'SQLSET_SFIT',

               SQLSET_OWNER => 'SYS',

               POPULATE_CURSOR => SQLSET_CUR,

               LOAD_OPTION => 'MERGE',

               UPDATE_OPTION => 'ACCUMULATE');

  CLOSE SQLSET_CUR;

  OPEN SQLSET_CUR FOR

    SELECT VALUE(P) FROM TABLE(

           DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'PARSING_SCHEMA_NAME NOT IN (''SYS'', ''SYSTEM'')',

                        NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;

  DBMS_SQLTUNE.LOAD_SQLSET(

               SQLSET_NAME => 'SQLSET_SFIT',

               SQLSET_OWNER => 'SYS',

               POPULATE_CURSOR => SQLSET_CUR,

               LOAD_OPTION => 'MERGE',

               UPDATE_OPTION => 'ACCUMULATE');

  CLOSE SQLSET_CUR;

END;

/

3.5 打包SQL Set

## 在11G源库做操作
SQL> DROP TABLE SYSTEM.SQLSET_SFIT;

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET (SQLSET_NAME=> 'SQLSET_SFIT',SQLSET_OWNER=> 'SYS',STAGING_TABLE_NAME=> 'SQLSET_SFIT',STAGING_SCHEMA_OWNER => 'SYSTEM');

3.6 创建Mapping关系表

## 如果源库和生产库结构一致，无需创建Mapping表，在11G源库做操作

CREATE TABLE SYS.MAPPING_TABLE TABLESPACE SYSAUX AS
SELECT OBJECT_ID ID, OWNER, SUBSTR(OBJECT_NAME, 1, 30) NAME
FROM DBA_OBJECTS
WHERE OBJECT_TYPE NOT 
IN('CONSUMER GROUP', 'EVALUATION CONTEXT', 'FUNCTION', 'INDEXTYPE'
, 'JAVA CLASS', 'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY', 'LOB'
, 'OPERATOR', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'QUEUE'
, 'RESOURCE PLAN', 'SYNONYM', 'TRIGGER', 'TYPE', 'TYPE BODY')
UNION ALL
SELECT USER_ID ID, USERNAME OWNER, NULL NAME
FROM DBA_USERS;

3.7 源库导出表

## 在11G源库做操作
expdp \'/ as sysdba\'  dumpfile=test_stage_table.dmp logfile=sbdb_stage_expdp.log directory=data_pump_dir tables=system.SQLSET_SFIT

Export: Release 11.2.0.4.0 - Production on Sat Oct 15 09:14:36 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=test_stage_table.dmp logfile=sbdb_stage_expdp.log directory=data_pump_dir tables=system.SQLSET_SFIT 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.125 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
. . exported "SYSTEM"."SQLSET_SFIT"                      1.162 MB    2144 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/MES/dpdump/test_stage_table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Oct 15 09:15:12 2022 elapsed 0 00:00:33

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9YpGVYSh-1666131840419)(C:\Users\wangd\AppData\Roaming\Typora\typora-user-images\image-20221015091616843.png)]$

4.目标库SAP实施

4.1 SQL Set表导入

1.源库查询
[oracle@rhel64 ~]$ cd /u01/app/oracle/admin/MES/dpdump/

[oracle@rhel64 dpdump]$ ll
total 1540
-rw-r-----. 1 oracle oinstall     116 Oct  7 00:42 dp.log
-rw-r-----. 1 oracle oinstall   36864 Oct  7 09:23 expdp_mes01.dmp
-rw-r-----. 1 oracle oinstall   69632 Oct  7 09:23 expdp_mes02.dmp
-rw-r--r--. 1 oracle oinstall    1244 Oct 15 09:15 sbdb_stage_expdp.log
-rw-r--r--. 1 oracle oinstall    1939 Oct  7 09:23 test_mes.log
-rw-r-----. 1 oracle oinstall 1458176 Oct 15 09:15 test_stage_table.dmp

2.源库复制到目标库
[oracle@rhel64 ~]$ scp /u01/app/oracle/admin/MES/dpdump/test_stage_table.dmp 192.168.1.58:/u01/app/oracle/admin/MES/dpdump/

3.目标库查询
[root@rhel64 ~]# ll /u01/app/oracle/admin/MES/dpdump/
total 1536
-rw-r-----. 1 oracle oinstall     116 Oct  7 00:42 dp.log
-rw-r-----. 1 oracle oinstall   36864 Oct  7 09:23 expdp_mes01.dmp
-rw-r-----. 1 oracle oinstall   69632 Oct  7 09:23 expdp_mes02.dmp
-rw-r--r--. 1 oracle oinstall    1939 Oct  7 09:23 test_mes.log
-rw-r-----. 1 oracle oinstall 1458176 Oct 15 17:38 test_stage_table.dmp

4.目标库dump导入
[oracle@rhel64 ~]$ impdp \'/ as sysdba\'  dumpfile=test_stage_table.dmp logfile=sbdb_stage_impdp.log \
directory=data_pump_dir tables=system.SQLSET_SFIT

Import: Release 11.2.0.4.0 - Production on Sat Oct 15 17:39:44 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=test_stage_table.dmp logfile=sbdb_stage_impdp.log directory=data_pump_dir tables=system.SQLSET_SFIT 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."SQLSET_SFIT"                      1.162 MB    2144 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Oct 15 17:39:57 2022 elapsed 0 00:00:12

在这里插入图片描述

4.2 解包SQL Set

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(SQLSET_NAME=>'SQLSET_SFIT',SQLSET_OWNER=>'SYS',REPLACE=> TRUE,STAGING_TABLE_NAME=> 'SQLSET_SFIT',STAGING_SCHEMA_OWNER => 'SYSTEM');

PL/SQL procedure successfully completed.

4.3 创建SPA任务

VARIABLE SPA_TASK  VARCHAR2(64);
EXEC :SPA_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(TASK_NAME=>'SPA_TASK_SFIT',DESCRIPTION =>'SPA Analysis task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),SQLSET_NAME  => 'SQLSET_SFIT',SQLSET_OWNER =>'SYS');

PL/SQL procedure successfully completed.

4.4 在目标端转换源库11G的SQL性能

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=> 'SPA_TASK_SFIT',EXECUTION_NAME => 'EXEC_11G_SFIT',EXECUTION_TYPE => 'CONVERT SQLSET', EXECUTION_DESC => 'Convert 11g SQLSET for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

PL/SQL procedure successfully completed.

4.5 在目标端转换目标库11G的SQL性能

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=> 'SPA_TASK_SFIT',EXECUTION_NAME => 'EXEC_11G_TARGET_SFIT',EXECUTION_TYPE => 'TEST EXECUTE',EXECUTION_DESC => 'Execute SQL in 11G TARGET  for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

5.报告分析比对

5.1.对比两次Trail中的SQL执行时间
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=> 'SPA_TASK_SFIT',EXECUTION_NAME =>'COMPARE_ET_SFIT',EXECUTION_TYPE=>'COMPARE PERFORMANCE',EXECUTION_PARAMS=>DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC', 'ELAPSED_TIME','EXECUTE_FULLDML','TRUE','EXECUTION_NAME1','EXEC_11G_SFIT','EXECUTION_NAME2','EXEC_11G_TARGET_SFIT'),EXECUTION_DESC => 'Compare SQLs between 11g and 19g Traget at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

PL/SQL procedure successfully completed.

5.2.对比两次Trail中的SQL执行的CPU时间
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=> 'SPA_TASK_SFIT',EXECUTION_NAME => 'COMPARE_CT_SFIT',EXECUTION_TYPE => 'COMPARE PERFORMANCE',EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC', 'CPU_TIME','EXECUTION_NAME1','EXEC_11G_SFIT', 'EXECUTION_NAME2','EXEC_11G_TARGET_SFIT'),EXECUTION_DESC => 'Compare SQLs between 11g and 19g Traget at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

PL/SQL procedure successfully completed.

5.3.对比两次Trail中的SQL执行的逻辑读
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=> 'SPA_TASK_SFIT',EXECUTION_NAME => 'COMPARE_BG_SFIT',EXECUTION_TYPE => 'COMPARE PERFORMANCE',EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC', 'BUFFER_GETS','EXECUTION_NAME1','EXEC_11G_SFIT', 'EXECUTION_NAME2','EXEC_11G_TARGET_SFIT'),EXECUTION_DESC => 'Compare SQLs between 11g and 19g Traget at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

PL/SQL procedure successfully completed.

5.4.对比两次Trail中的SQL执行的物理读
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=> 'SPA_TASK_SFIT',EXECUTION_NAME => 'COMPARE_DR_SFIT',EXECUTION_TYPE => 'COMPARE PERFORMANCE',EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC', 'DISK_READS','EXECUTION_NAME1','EXEC_11G_SFIT',  'EXECUTION_NAME2','EXEC_11G_TARGET_SFIT'),EXECUTION_DESC => 'Compare SQLs between 11g and 19g Traget at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

PL/SQL procedure successfully completed.

6.汇总报告

6.1.全量报告
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED

SPOOL elapsed_all_d.html
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_SFIT','HTML','ALL','ALL',NULL,500,'COMPARE_ET_SFIT')
FROM DUAL;
SPO OFF

[oracle@rhel64 ~]$ ll
total 2476
drwxr-xr-x. 2 oracle oinstall    4096 Oct  7 22:55 Desktop
drwxr-xr-x. 2 oracle oinstall    4096 Oct  7 22:55 Documents
drwxr-xr-x. 2 oracle oinstall    4096 Oct  7 22:55 Downloads
-rw-r--r--. 1 oracle oinstall 2491027 Oct 15 18:04 elapsed_all_d.html
-rw-r--r--. 1 oracle oinstall    1024 Oct 12 23:37 import.log
drwxr-xr-x. 2 oracle oinstall    4096 Oct  7 22:55 Music
-rw-------. 1 oracle oinstall    3864 Oct  7 09:23 nohup.out
drwxr-xr-x. 2 oracle oinstall    4096 Oct  7 22:55 Pictures
drwxr-xr-x. 2 oracle oinstall    4096 Oct  7 22:55 Public
drwxr-xr-x. 2 oracle oinstall    4096 Oct  7 22:55 Templates
drwxr-xr-x. 2 oracle oinstall    4096 Oct  7 22:55 Videos

6.2 可以生成指定部分的报告
'ERRORS',   --获取不同类型的分析报告，主要关注：REGRESSED, UNSUPPORTED, ERRORS三类即可

6.3 如何阅读SPA报告
根据报告中的SQL耗时和逻辑读消耗对比，查看执行计划分析SQL是否发生变化，再细化分析。