1.生产端：环境准备
为了进行SPA测试，在生产数据库中创建了SPA测试专用用户，避免与其他用户相互混淆与可能产生的误操作。

CREATE USER SPA IDENTIFIED BY SPA DEFAULT TABLESPACE SYSAUX;

GRANT DBA TO SPA;

GRANT ADVISOR TO SPA;

GRANT SELECT ANY DICTIONARY TO SPA;

GRANT ADMINISTER SQL TUNING SET TO SPA;

2.生产端：采集数据

具体采集步骤如下：
1). 获取AWR快照的边界ID

SET LINES 188 PAGES 1000

COL SNAP_TIME FOR A22

COL MIN_ID NEW_VALUE MINID

COL MAX_ID NEW_VALUE MAXID

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

---------- ----------

178978 179488

2). 创建SQL Set

EXEC DBMS_SQLTUNE.DROP_SQLSET (SQLSET_NAME => 'SQLSET_D',SQLSET_OWNER => 'SPA');

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

select OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME from dba_objects where OWNER in ('SPA');

3). 抓取SQL
从AWR中提取

nohup sqlplus / as sysdba << EOF &

set SQLBL on

set lines 199

set pages 9999

set timing on

spool LOAD_SQLSET.log

DECLARE

SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

OPEN SQLSET_CUR FOR

SELECT VALUE(P) FROM TABLE(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(178978,179488,

'PARSING_SCHEMA_NAME IN (''AAAA'',''BBBBB'')',

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

DBMS_SQLTUNE.LOAD_SQLSET(

SQLSET_NAME => 'SQLSET_D',

SQLSET_OWNER => 'SPA',

POPULATE_CURSOR => SQLSET_CUR,

LOAD_OPTION => 'MERGE',

UPDATE_OPTION => 'ACCUMULATE');

CLOSE SQLSET_CUR;

END;

/

spool off

exit

EOF

4). 打包SQL Set

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ('SQLSET_D_AWR', 'SPA', 'SYSAUX');

nohup sqlplus / as sysdba << EOF &

set SQLBL on

set lines 199

set pages 9999

set timing on

spool PACK_STGTAB_SQLSET.log

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET (SQLSET_NAME=> 'SQLSET_D',SQLSET_OWNER=> 'SPA',STAGING_TABLE_NAME=> 'SQLSET_D_AWR',STAGING_SCHEMA_OWNER => 'SPA');

spool off

exit

EOF

3.生产端：导出数据
将采集到的数据打包后，需要将其中生产库导出，并传输到测试服务器中，用于在测试数据库中进行SPA测试工作。
1). 在操作系统中，导出打包后的SQL Set数据

chown oracle:oinstall dmp

vi expdp.sh

expdp \'/ as sysdba\' DIRECTORY=XTTS DUMPFILE=SPA.dmp TABLES=spa.SQLSET_D_AWR LOGFILE=spa.log

nohup sh expdp.sh &

4.测试端：环境准备
为了进行SPA测试，在测试数据库中创建SPA测试专用用户，避免与其他用户相互混淆与可能产生的误操作。

CREATE USER SPA IDENTIFIED BY SPA DEFAULT TABLESPACE SYSAUX;

GRANT DBA TO SPA;

GRANT ADVISOR TO SPA;

GRANT SELECT ANY DICTIONARY TO SPA;

GRANT ADMINISTER SQL TUNING SET TO SPA;

sys/Oracle123@pdb

vi impdp.sh

impdp \'sys/Oracle123@pdb as sysdba\' DIRECTORY=XTTS DUMPFILE=SPA.dmp LOGFILE=impspa.log

nohup sh impdp.sh

5.spa分析

conn spa/SPA

EXEC DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'SQLSET_D',replace => TRUE,staging_table_name => 'SQLSET_D_AWR',staging_schema_owner => 'SPA');

创建analysis_task

DECLARE V_SPA_NAME VARCHAR2(100);

BEGIN

V_SPA_NAME:=dbms_sqlpa.create_analysis_task(sqlset_name => 'SQLSET_D',task_name => 'SQLSET_D_TASK');

dbms_output.put_line('spa_name='||v_spa_name);

end;

/

select OWNER ,OBJECT_NAME,OBJECT_TYPE,STATUS,CREATED,LAST_DDL_TIME from dba_objects where OBJECT_NAME='SQLSET_D_AWR';

DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(

task_name IN VARCHAR2,

parameter IN VARCHAR2,

value IN VARCHAR2,

test_execute_dop IN NUMBER DEFAULT 0,

compare_resultset IN BOOLEAN DEFAULT TRUE);

exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name=>'SQLSET_D_TASK',parameter=>'TEST_EXECUTE_DOP' ,value=>4);

6.生成trc
生成11G 的trail

nohup sqlplus spa/SPA << EOF &

set SQLBL on

set lines 199

set pages 9999

set timing on

spool execute_analysis_task1.log

exec dbms_sqlpa.execute_analysis_task(task_name => 'SQLSET_D_TASK',execution_type => 'CONVERT SQLSET',execution_name => 'SPA_11G');

spool off

exit

EOF

生成19c 的trail

nohup sqlplus spa/SPA << EOF &

set SQLBL on

set lines 199

set pages 9999

set timing on

spool execute_analysis_task2.log

exec dbms_sqlpa.execute_analysis_task(task_name => 'SQLSET_D_TASK',execution_type => 'TEST EXECUTE',execution_name => 'SPA_19C');

spool off

exit

EOF

elapsed_time比较

nohup sqlplus spa/SPA << EOF &

set SQLBL on

set lines 199

set pages 9999

set timing on

spool execute_analysis_task3.log

exec dbms_sqlpa.execute_analysis_task(task_name => 'SQLSET_D_TASK',execution_type => 'COMPARE PERFORMANCE',execution_name => 'compare_elp_time',execution_params => dbms_advisor.argList('comparison_metric','elapsed_time'));

spool off

exit

EOF

buffer_gets 比较

nohup sqlplus spa/SPA << EOF &

set SQLBL on

set lines 199

set pages 9999

set timing on

spool execute_analysis_task4.log

exec dbms_sqlpa.execute_analysis_task(task_name => 'SQLSET_D_TASK',execution_type => 'COMPARE PERFORMANCE', execution_name => 'compare_buff_gets', execution_params => dbms_advisor.argList('comparison_metric','buffer_gets'));

spool off

exit

EOF

disk_reads 比较

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(

task_name => 'SQLSET_D_TASK',

execution_type => 'COMPARE PERFORMANCE',

execution_name => 'compare_disk_reads',

execution_params => dbms_advisor.arglist('comparison_metric', 'disk_reads') );

end;

/

7.生成比较报告

sqlplus SPA/SPA

set long 999999999 longchunksize 999999999 linesize 200 head off feedback off echo off

spool all.html

select dbms_sqlpa.report_analysis_task('SQLSET_D_TASK','HTML','ALL','ALL') FROM DUAL;

spool off

spool elapsed_time.html

SELECT dbms_sqlpa.report_analysis_task('SQLSET_D_TASK', 'HTML', 'ALL','ALL', execution_name=>'compare_elp_time') FROM dual;

spool off

set long 999999999 longchunksize 999999999 linesize 200 head off feedback off echo off

spool buffer_gets.html

SELECT dbms_sqlpa.report_analysis_task('SQLSET_D_TASK', 'HTML', 'ALL','ALL', execution_name=>'compare_buff_gets') FROM dual;

spool off

set long 999999999 longchunksize 999999999 linesize 200 head off feedback off echo off

spool disk_reads.html

SELECT dbms_sqlpa.report_analysis_task('SQLSET_D_TASK', 'HTML', 'ALL','ALL', execution_name=>'compare_disk_reads') FROM dual;

spool off

set long 999999999 longchunksize 999999999 linesize 200 head off feedback off echo off

spool errors.html

SELECT dbms_sqlpa.report_analysis_task('SQLSET_D_TASK', 'HTML', 'errors','all') FROM dual;

spool off;

spool unsupport.html

SELECT dbms_sqlpa.report_analysis_task('SQLSET_D_TASK', 'HTML', 'unsupported','all') FROM dual;

spool off;

总结：

1.SPA用户只需要dba权限应该就可以了。
2.采集数据可以通过如下方式获取：
   a) 转化AWR中SQL为SQL Tuning Set
   b) 转化Share Pool中SQL为SQL Tuning Set
   c) 采集Cursor中SQL为SQL Tuning Set
   d) 从现有SQL Tuning Set提取SQL

3.19C 可以通过DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER设置并行度，根据cpu的个数进行适当设置。如果并行设置很小，spa会跑很久。并行设置过大，   oracle有可能用不上，对主机负载也会有压力，一般设置为cpu个数的一半。

4.相关包：dbms_sqlpa
https://docs.oracle.com/database/121/ARPLS/d_sqlpa.htm#ARPLS68365

5.相关视图：

a）DBA_ADVISOR_TASKS --查看分析任务
b) DBA_SQLSET --查看set
c) DBA_ADVISOR_DEF_PARAMETERS --查看参数设置
d) DBA_ADVISOR_SQLSTATS --查看sql的执行情况
e) dba_sqlset_statements --查看sql的执行情况

6.在执行期间，awr数据会增长很快，注意SYSAUX表空间使用情况。关于改表空间的清理，见另一篇文章。
7.设置相关参数

a)关于目标端执行时间过长，可以使用设置超时参数
exec dbms_sqlpa.set_analysis_default_parameter('LOCAL_TIME_LIMIT',1800);
select parameter_value from dba_advisor_def_parameters where advisor_name='SQL Performance Analyzer' and parameter_name='LOCAL_TIME_LIMIT';
PARAMETER_VALUE
--------------------------------------------------------------------------------
1800

6）调整负载参数
exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name=>'SPA_19C_0614_TASK',parameter => 'workload_impact_threshold',value => 0);
exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name=>'SPA_19C_0614_TASK',parameter => 'sql_impact_threshold',value => 0);

8.关于并行，当并行开很大(cores),会导致实例重启。即使使用alter system set parallel_force_local=FALSE;让任务在多个节点执行，依旧会导致节点重启。开到特别大的时候，两个节点都会重启。

9.每一步的相关回退操作和相关mos
--exec DBMS_SQLTUNE.CANCEL_TUNING_TASK('SPA_19C_2021_0621_TASK');
--exec DBMS_SQLTUNE.DROP_TUNING_TASK('SPA_19C_2021_0621_TASK');
--exec dbms_sqltune.drop_sqlset('SPA_19C_2021_0621','XTTS');
--drop table XTTS.SPA2021_0621;

ORA-13757: Can't drop SQL Tuning Set (Doc ID 1276524.1)
datapatch failed with "ORA-13647: Setting of parameter ALTERNATE_PLAN_LIMIT is disallowed during task execution " (Doc ID 2710316.1)

Changing a Tuning Task Parameter While it is Running Gets "ORA-13647: Setting of parameter LOCAL_TIME_LIMIT is disallowed during task execution" (Doc ID 1597819.1)

10.官方文档参考：

PL/SQL Packages and Types Reference>dbms_sqlpa

11.spa分析
a）在捕获sql和重放的过程中，由于数据量的变化，会影响sql的性能判断，可以通过设置optimizer_features_enable参数，来进行手工对比。
b）spa分析error中，最常见的问题就是cursor_sharing 为FORCE，捕获的变量不全，导致sql变量缺失执行报错。
c）重点分析sql
strings sql.list | while read sql ; do
echo ${sql}_11g.html
echo "<h1>SQL: ${SQL} </h1>" > ${sql}.html
echo "<h2>Before: prod 11g, After: 19C DB, optimizer_features_enable=19.1.0 </h2>" >> ${sql}.html
grep -A 10000 -B 16 ": $sql" buffer_gets_19c.html | sed -n '1,/SQL Details/p' >> ${sql}.html
echo "<h2>Before: prod 11g, After: 19C DB, optimizer_features_enable=11.2.0.4 </h2>" >> ${sql}.html
grep -A 10000 -B 16 ": $sql" buffer_gets.html | sed -n '1,/SQL Details/p' >> ${sql}.html
done

select sql19.sql_id
,sql19.BUFFER_GETS/greatest(sql11.BUFFER_GETS,1) get_diff1
,sql19.BUFFER_GETS/greatest(org11.BUFFER_GETS,1) get_diff2
,sql11.BUFFER_GETS SQL11_Get,sql19.BUFFER_GETS SQL19_Get,org11.BUFFER_GETS org11_Get
,sql11.ELAPSED_TIME SQL11_els,sql19.ELAPSED_TIME SQL19_els,org11.ELAPSED_TIME SQL19_els
,sql11.PLAN_HASH_VALUE SQL11_phv,sql19.PLAN_HASH_VALUE SQL19_phv
from
DBA_ADVISOR_SQLSTATS sql19,DBA_ADVISOR_SQLSTATS sql11,
(select sql_id,sum(buffer_gets)/greatest(1,sum(executions)) buffer_gets,sum(ELAPSED_TIME)/greatest(1,sum(executions)) ELAPSED_TIME
from dba_sqlset_statements org11
where sqlset_name = 'SPA_19C_0614' group by sql_id) org11
where sql19.TASK_NAME = 'SPA_19C_0614_TASK' and sql11.TASK_NAME='SPA_19C_2021_0621_TASK'
and sql19.sql_id = sql11.sql_id and sql19.sql_id = org11.sql_id
and sql11.BUFFER_GETS is not null and sql19.BUFFER_GETS is not null
and sql19.BUFFER_GETS/greatest(sql11.BUFFER_GETS,1)>1.05
and sql19.BUFFER_GETS/greatest(org11.BUFFER_GETS,1)>1.05
and sql19.PLAN_HASH_VALUE!=sql11.PLAN_HASH_VALUE
order by get_diff1 desc;

12.抓取一个好的时段的spa，将会为分析工作带来便利。生产一般都是在线系统，测试数据如果能和生产保持一致是一种理想状态。所以，我们能做的就是在做spa的时候，让测试环境和生产的差异尽可能的小，即，测试迁移完成后，取迁移前的一段时间做。

最新文章

  1. Javascript的实例化与继承：请停止使用new关键字
  2. 如何查看linux内核的版本号？
  3. Excel2007给表格设置成只读加密属性 让他人无法修改
  4. Labview实现单边带信号调制（SSB）[移相法]
  5. memory model
  6. Discuz!NT中集成Memcached分布式缓存
  7. .net 安装Swagger
  8. DOM对象控制HTML无素——详解1
  9. VMware设置NAT网络
  10. Mysql第一周
  11. IE浏览器兼容的常见问题及解决方案
  12. 使用ConcurrentHashMap一定线程安全？
  13. Redis 安装总结记录 附送redis-desktop-manager工具
  14. SpringMvc通过@Value( ) 给静态变量注入值
  15. [01] AJAX
  16. ASP.NET界面重定向传值
  17. Linux基础命令---cp
  18. win2003 server的域用户加入本地管理员组
  19. springboot-dokcer
  20. 选择合适的Linux版本

热门文章

  1. jQuery中获取属性值：attr()、html()、text()、val()等(一)
  2. redhat9 linux 网卡无法激活排障
  3. vue 打开新窗口进行打印
  4. VMware安装IPFire防火墙镜像
  5. redis跨实例迁移 &amp; redis上云
  6. VUE005. 在data中使用 / 改变data，或在data中调用method函数
  7. 随机生成uuid序号
  8. 文件流转换为url
  9. scrum项目冲刺_day05总结
  10. PHP中操作任意精度大小的GMP扩展学习