代码改变世界

记录一则完整的SPA(10g->11g)测试过程

2018-06-22 12:10  AlfredZhao  阅读(564)  评论(0编辑  收藏

生产端:Windows 2008 + Oracle 10.2.0.5
测试端:RHEL 6.5 + Oracle 11.2.0.4
需求:因为Oracle跨越大版本,优化器、新特性变动较多,需要进行SPA测试比对前后期性能差异。
说明:本文是根据DBA Travel的SPA参考规范文档(在此致谢Travel同学),结合实际某客户需求整理的整个测试过程。为了更真实的反映整个过程,在生产端使用swingbench压力测试软件持续运行了一段时间,模拟真实的业务压力。

1.SPA测试流程

为了尽可能的减小对正式生产库的性能影响,本次SPA测试只是从AWR资料库中的SQL数据转化而来的SQL Tuning Set进行整体的SQL性能测试。

本次SPA测试主要分为以下几个步骤:
在生产库端:

  1. 环境准备:创建SPA测试专用用户
  2. 采集数据:
    a) 在生产库转化AWR中SQL为SQL Tuning Set
    b) 在生产库从现有SQL Tuning Set提取SQL
  3. 导出数据:打包(pack)转化后的SQL Tuning Set,并导出传输到测试服务器

在测试库端:
4. 环境准备:创建SPA测试专用用户
5. 测试准备:导入SQL Tuning Set表,并解包(unpack),创建SPA分析任务
6. 前期性能:从SQL Tuning Set中转化得出10g的性能Trail
7. 后期性能:在11g测试数据库中执行SQL Tuning Set中SQL,生成11g性能Trail
8. 对比分析:执行对比分析任务,分别按执行时间,CPU时间和逻辑读三个维度进行
9. 汇总报告:取出对比报告,对每个维度分别取出All,Unsupport,Error 3类报告

总结报告:
10. 总结报告:分析汇总报告,优化其中的性能下降SQL,编写SPA测试报告

2.SPA操作流程

2.1 本文使用的命名规划

类型			       规划
SQLSET			   ORCL_SQLSET_201806
Analysis Task        SPA_TASK_201806
STGTAB			   ORCL_STSTAB_201806
Dmpfile			   ORCL_STSTAB_201806.dmp

2.2 生产端:环境准备

conn / as sysdba
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.3 生产端:采集数据
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;

2). 创建SQL Set

--连接用户
conn SPA/SPA

--如果之前有这个SQLSET的名字,可以这样删除
EXEC DBMS_SQLTUNE.DROP_SQLSET (SQLSET_NAME  => 'ORCL_SQLSET_201806', SQLSET_OWNER => 'SPA');

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

3). 转化AWR数据中的SQL数据,将其中的SQL载入到SQL Set中

DECLARE
  SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN SQLSET_CUR FOR
    SELECT VALUE(P) FROM TABLE(
           DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( 16, 24,
                        'PARSING_SCHEMA_NAME NOT IN (''SYS'', ''SYSTEM'')',
                        NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
  DBMS_SQLTUNE.LOAD_SQLSET(
               SQLSET_NAME => 'ORCL_SQLSET_201806',
               SQLSET_OWNER => 'SPA',
               POPULATE_CURSOR => SQLSET_CUR,
               LOAD_OPTION => 'MERGE',
               UPDATE_OPTION => 'ACCUMULATE');
  CLOSE SQLSET_CUR;
    END;
/			

4). 打包SQL Set

DROP TABLE SPA.JYZHAO_SQLSETTAB_20180106;
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ('ORCL_STSTAB_201806', 'SPA', 'SYSAUX');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( -
                  SQLSET_NAME          => 'ORCL_SQLSET_201806', -
                  SQLSET_OWNER         => 'SPA', -
                  STAGING_TABLE_NAME   => 'ORCL_STSTAB_201806', -
                  STAGING_SCHEMA_OWNER => 'SPA');

2.4 生产端:导出数据
1). 在操作系统中,导出打包后的SQL Set数据

cat > ./export_sqlset_201806.par <<EOF
USERID='SPA/SPA'
FILE=ORCL_STSTAB_201806.dmp
LOG=exp_spa_sqlset_201806.log
TABLES=ORCL_STSTAB_201806
DIRECT=N
BUFFER=10240000
STATISTICS=NONE
EOF

注意:这里DIRECT=Y参数在遇到问题后尝试改为了DIRECT=N,默认也是N。

set NLS_LANG=AMERICAN_AMERICA.US7ASCII
exp PARFILE=export_sqlset_201806.par

注意:NLS_LANG变量是Oracle的变量,设置字符集和数据库字符集一致,避免发生错误转换。

2). 将导出后的Dump文件传输到测试服务器
将 ORCL_STSTAB_201806.dmp 传输到 目标服务器 /orabak/spa下。

2.5 测试端:环境准备

conn / as sysdba
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.6 测试端:测试准备
在进行SPA测试前需要准备测试环境,包括导入生产库中的SQL Set,对其进行解包(unpack)操作,并创建SPA分析任务。
1). 在操作系统中,执行导入命令,导入SQL Set表

cat > ./import_sqlset_201806.par <<EOF
USERID='SPA/SPA'
FILE=ORCL_STSTAB_201806.dmp
LOG=imp_spa_sqlset_201806.log
FULL=Y
EOF

export NLS_LANG=AMERICAN_AMERICA.US7ASCII
imp PARFILE=import_sqlset_201806.par

2). 解包(unpack)SQL Set

conn SPA/SPA
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (-
                  SQLSET_NAME          => 'ORCL_SQLSET_201806', -
                  SQLSET_OWNER         => 'SPA', -
                  REPLACE              => TRUE, -
                  STAGING_TABLE_NAME   => 'ORCL_STSTAB_201806', -
                  STAGING_SCHEMA_OWNER => 'SPA');

3). 创建SPA分析任务

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

2.7 测试端:前期性能
在测试服务器中,可以直接从SQL Tuning Set中转化得到所有SQL在10g数据库中的执行效率,得到10g中的SQL Trail。

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

2.8 测试端:后期性能
在测试服务器(运行11g数据库)中,需要在本地数据库(11g)测试运行SQL Tuning Set中的SQL语句,分析所有语句在11g环境中的执行效率,得到11g中的SQL Trail。

vi spa2.sh
 
echo "WARNING: SPA2 Start @`date`"
sqlplus SPA/SPA << EOF!
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_201806', -
                EXECUTION_NAME => 'EXEC_11G_201806', -
                EXECUTION_TYPE => 'TEST EXECUTE', -
                EXECUTION_DESC => 'Execute SQL in 11g for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
exit
EOF!
echo "WARNING:SPA2 OK @`date`"
 
nohup sh spa2.sh &      

**2.9 测试端:性能对比 **
得到两次SQL Trail之后,可以对比两次Trial之间的SQL执行性能,可以从不同的维度对两次Trail中的所有SQL进行对比分析,主要关注的维度有:SQL执行时间,SQL执行的CPU时间,SQL执行的逻辑读。

1). 对比两次Trail中的SQL执行时间

conn SPA/SPA
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_201806', -
                EXECUTION_NAME => 'COMPARE_ET_201806', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'ELAPSED_TIME', -
                                                 'EXECUTE_FULLDML', 'TRUE', -
                                                 'EXECUTION_NAME1','EXEC_10G_201806', -
                                                 'EXECUTION_NAME2','EXEC_11G_201806'), -
                EXECUTION_DESC => 'Compare SQLs between 10g and 11g at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

2). 对比两次Trail中的SQL执行的CPU时间

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_201806', -
                EXECUTION_NAME => 'COMPARE_CT_201806', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'CPU_TIME', -
                                                 'EXECUTION_NAME1','EXEC_10G_201806', -
                                                 'EXECUTION_NAME2','EXEC_11G_201806'), -
                EXECUTION_DESC => 'Compare SQLs between 10g and 11g at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

3). 对比两次Trail中的SQL执行的逻辑读

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_201806', -
                EXECUTION_NAME => 'COMPARE_BG_201806', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'BUFFER_GETS', -
                                                 'EXECUTION_NAME1','EXEC_10G_201806', -
                                                 'EXECUTION_NAME2','EXEC_11G_201806'), -
                EXECUTION_DESC => 'Compare SQLs between 10g and 11g at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

2.10 测试端:汇总报告
执行对比分析任务之后,就可以取出对应的对比分析任务的结果报告,主要关注的报告类型有:汇总SQL报告,错误SQL报告以及不支持SQL报告。

a) 获取执行时间全部报告

conn SPA/SPA
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL elapsed_all.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201806','HTML','ALL','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

b) 获取执行时间下降报告

ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL elapsed_regressed.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201806','HTML','REGRESSED','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

c) 获取逻辑读全部报告

ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL buffer_all.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201806','HTML','ALL','ALL',NULL,1000,'COMPARE_BG_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

d) 获取逻辑读下降报告

ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL buffer_regressed.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201806','HTML','REGRESSED','ALL',NULL,1000,'COMPARE_BG_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

e) 获取错误报告

ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL error.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201806','HTML','ERRORS','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

f) 获取不支持报告

ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL unsupported.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201806','HTML','UNSUPPORTED','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

g) 获取执行计划变化报告

ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL changed_plans.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201806','HTML','CHANGED_PLANS','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

3.SPA环境清理

3.1 查看SQLSET

conn SPA/SPA
select owner,name,STATEMENT_COUNT from dba_sqlset;

3.2 查看分析任务

select owner,task_id,task_name,created,LAST_MODIFIED,STATUS from DBA_ADVISOR_TASKS  where task_name like upper('%&task_name%') order by 2;
SPA_TASK_201806

3.3 删除ANALYSIS_TASK

exec dbms_sqlpa.DROP_ANALYSIS_TASK('SPA_TASK_201806');

3.4 删除sqlset

exec dbms_sqltune.DROP_SQLSET('ORCL_SQLSET_201806');

如果删除时出现异常情况"ORA-13757",提示STS是活动的,可以尝试使用下面SQL修改后再进行删除。

delete from wri$_sqlset_references
    where sqlset_id in (select id
    from wri$_sqlset_definitions
    where name in ('ORCL_SQLSET_201806','ORCL_SQLSET_201806'));
commit;

3.5 删除用户
删除SPA用户(两端)

drop user spa cascade;