本文详细介绍了SQL Performance Analyzer (SPA) 的概念、作用及测试流程。通过实例展示了如何在源端和目标端数据库进行初始化、数据导入导出，以及SPA的执行过程，用于预测和防止数据库性能问题。在SPA实操部分，涵盖了从创建SQL Set到生成对比报告的完整步骤。
1、什么是SPA(SQL Performance Analyzer)？
SPA(
SQL Performance Analyzer) 是Oracle的SQL性能优化分析器。Oracle Database 11g 引入了 SQL 性能分析器；使用该工具可以准确地评估数据库变更对 SQL 语句的影响。SQL 性能分析器可帮助预测潜在的更改对 SQL 的性能影响。这种功能可向 DBA 提供有关 SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。
SPA其实也是Oracle Real Application Testing的一个组件，另外一个组件Database Replay。SPA的原理是通过变更前对收集的STS进行执行，获取基线数据，变更后再次进行执行，然后进行对比，从多个维度比如CPU时间，I/O，buffer get等生成详细的变更对比报告。典型用法是捕捉STS，然后通过SPA生成对比报告，然后对变更后衰减的SQL调用STA进行优化。
2、SPA作用
SQL 性能分析器可用于预测和防止会影响 SQL 执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括（但不限于）以下任何一种更改：
1）数据库升级
2）实施优化建议
3）更改方案
4）收集统计信息
5）更改数据库参数
6）更改操作系统和硬件
3、SPA测试流程
为了尽可能的减小对正式生产库的性能影响，SPA测试可以从AWR资料库中的SQL数据转化而来的SQL Tuning Set进行整体的SQL性能测试。
本次测试主要分为以下几个步骤：
在源端：
1.环境准备：创建SPA测试专用用户
2.采集数据：
a)在生产库转化AWR中SQL为SQL Tuning Set
b)在生产库从现有SQL Tuning Set提取SQL
3.导出数据：打包(pack)转化后的SQL Tuning Set，并导出传输到测试服务器
在目标库端：
1.环境准备：创建SPA测试专用用户
2.测试准备：导入SQL Tuning Set表，并解包(unpack)，创建SPA分析任务
3.前期性能：从SQL Tuning Set中转化得出11g的性能Trail
4.后期性能：在19c测试数据库中执行SQL Tuning Set中SQL，生成19c性能Trail
5.对比分析：执行对比分析任务，分别按执行时间，CPU时间和逻辑读三个维度进行
6.汇总报告：取出对比报告，对每个维度分别取出All，Unsupport，Error 3类报告
总结报告：分析汇总报告，优化其中的性能下降SQL，编写SPA测试报告
4、SPA实操
4.1、初始化数据库（源端和目标端数据库）
在进行SPA操作前，需要为数据库进行检测，目标端的数据库的表空间的大小和名字需要和源端的表空间的大小和名字一致（除去系统表空间）
a、检测源端数据库的表空间
SQL> select tablespace_name,bytes/1024/1024m from dba_data_files;
TABLESPACE_NAME M
------------------------------ ----------
SYSTEM 700
SYSAUX 600
UNDOTBS1 200
USERS 5
TEST 100
b、查看目标端表空间
SQL> select tablespace_name,bytes/1024/1024m from dba_data_files;
TABLESPACE_NAME M
------------------------------ ----------
SYSTEM 900
SYSAUX 600
USERS 5
UNDOTBS1 55
通过对表空间的检测可以看到，目标端没有test表空间，需要在目标端创建一个名为TEST，大小为100 MB的表空间
c、目标端进行表空间创建
查看表空间的位置
SQL> col file_name for a80
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_SYSTEM_GJ3K66GF_.DBF SYSTEM
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_SYSAUX_GJ3K79OK_.DBF SYSAUX
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_USERS_GJ3K7T1V_.DBF USERS
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_UNDOTBS1_GJ3K7RWQ_.DBF UNDOTBS1
创建表空间
SQL> create tablespace test datafile 'C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\TEST.DBF' size 100m;
表空间已创建。
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_SYSTEM_GJ3K66GF_.DBF SYSTEM
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_SYSAUX_GJ3K79OK_.DBF SYSAUX
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\TEST.DBF TEST
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_USERS_GJ3K7T1V_.DBF USERS
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_UNDOTBS1_GJ3K7RWQ_.DBF UNDOTBS1
4.2、导入导出数据
把源端的tns拷贝到目标端（测试不需要，如果是生产库则需要进行tns的拷贝）
同时需要注意DB link
a、源端导出数据
查看directory
SQL> col DIRECTORY_PATH for a80
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/source/state
SYS DATA_PUMP_DIR /oracle/app/oracle/admin/source/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/state
创建directory
SQL> create directory dump_dir as '/oracle/app/dump';
Directory created.
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS DUMP_DIR /oracle/app/dump
SYS ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/source/state
SYS DATA_PUMP_DIR /oracle/app/oracle/admin/source/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/state
源端导出数据
[oracle@source dump]$ cat /oracle/app/dump/full.sh PATH=$PATH:$HOME/bin NLS_LANG=AMERICAN_AMERICA.ZHS16GBK; export NLS_LANG export PATHORACLE_SID=source; export ORACLE_SID ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH export LANG=C export 022 /oracle/app/oracle/product/11.2.0/dbhome_1/bin/expdp system/oracle DIRECTORY=dump_dir parallel=1 dumpfile=source_full_` date +%y%m%d`_%U.dmp logfile=source_full_` date +%y%m%d`.log compression=all cluster=N full=y
目标端导入数据
C:\Users\li>impdp ' / as sysdba' directory=dump parallel=1 dumpfile=source_full_190617_01.dmp logfile=impdp.source.full.log cluster=no full=y
Import: Release 19.0.0.0.0 - Production on 星期二 6月 18 14:40:49 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
口令:oracle
;;;
Import: Release 19.0.0.0.0 - Production on 星期二 6月 18 14:40:49 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
;;;
连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_FULL_01"
启动 "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=dump parallel=1 dumpfile=source_full_190617_01.dmp logfile=impdp.source.full.log cluster=no full=y
处理对象类型 DATABASE_EXPORT/TABLESPACE
导入报错内容
（1）表空间、角色、用户、序列等已存在，忽略
ORA-31684: 对象类型 TABLESPACE:"UNDOTBS1" 已存在
ORA-31684: 对象类型 TABLESPACE:"TEMP" 已存在
ORA-31684: 对象类型 TABLESPACE:"USERS" 已存在
ORA-31684: 对象类型 TABLESPACE:"TEST" 已存在
ORA-31685: 由于权限不足, 对象类型 USER:"SYS" 失败。失败的 sql 为:
ALTER USER "SYS" IDENTIFIED BY VALUES 'S:6BF11CCC7A4D3D308B5CF151AE6BE8E8981F1612723B5B95DDDD17182B38;8A8F025737A9097A' TEMPORARY TABLESPACE "TEMP"
ORA-31684: 对象类型 USER:"OUTLN" 已存在
ORA-31684: 对象类型 USER:"ORACLE" 已存在
ORA-31684: 对象类型 ROLE:"SELECT_CATALOG_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"EXECUTE_CATALOG_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"DBFS_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"AQ_ADMINISTRATOR_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"AQ_USER_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"ADM_PARALLEL_EXECUTE_TASK" 已存在
ORA-31684: 对象类型 ROLE:"GATHER_SYSTEM_STATISTICS" 已存在
ORA-31684: 对象类型 ROLE:"RECOVERY_CATALOG_OWNER" 已存在
ORA-31684: 对象类型 ROLE:"SCHEDULER_ADMIN" 已存在
ORA-31684: 对象类型 ROLE:"HS_ADMIN_SELECT_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"HS_ADMIN_EXECUTE_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"HS_ADMIN_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"GLOBAL_AQ_USER_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"OEM_ADVISOR" 已存在
ORA-31684: 对象类型 ROLE:"OEM_MONITOR" 已存在
ORA-31684: 对象类型 ROLE:"WM_ADMIN_ROLE" 已存在
ORA-31684: 对象类型 DIRECTORY:"DATA_PUMP_DIR" 已存在
ORA-31684: 对象类型 CONTEXT:"GLOBAL_AQCLNTDB_CTX" 已存在
ORA-31684: 对象类型 CONTEXT:"DBFS_CONTEXT" 已存在
ORA-31684: 对象类型 CONTEXT:"REGISTRY$CTX" 已存在
ORA-31684: 对象类型 CONTEXT:"LT_CTX" 已存在
ORA-39083: 对象类型 PROCACT_SYSTEM 创建失败, 出现错误:
ORA-20000: Incompatible version of Workspace Manager Installed
失败的 sql 为:
BEGIN
declare
最低0.47元/天 解锁文章
扫一扫
45
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
