oracle spa性能测试,SPA for 11g 分析性能

11G的新特性SPA(SQL Performance Analyze)现在被广泛的应用到升级和迁移的场景。当然还有一些其他的场景可以考虑使用，比如(参数修改，I/O子系统变更)，但是主要是为了帮助我们检测升级之后性能退化的那些SQL语句，用以防止升级后SQL性能退化导致无法使用的问题。如下图所示：

SPA的主要功能集实施步骤如下：

在生产系统上捕捉SQL负载，并生成SQL Tuning Set；

创建一个中转表，将SQL Tuning Set导入到中转表，导出中转表并传输到测试库；

导入中转表，并解压中转表的数据到SQL Tuning Set；

创建SPA任务，先生成10g的trail，然后在11g中再生成11g的trail；

执行比较任务，再生成SPA报告；

分析性能退化的SQL语句；

在使用SPA的时候，首先我们一定要阅读文档：Using Real Application Testing Functionality in Earlier Releases (文档 ID 560977.1)，主要是阅读Table 3: SQL Performance Analyzer Availability Information。这个表格告诉我们，我们可以确认从那个源端版本到那个目标版本做SPA需要安装那些必要的补丁。

1.在生产系统上捕捉SQL负载，并生成SQL Tuning Set

这个步骤其实不是很复杂，我的一篇文章介绍过关于这个采集的过程。其实采集的方法有很多种，主要是：

cursor cache

awr snapshots

awr baseline

another sql set

10046 trace file(11g+)

我们一般使用的是游标采集和AWR历史资料库采集的方式。游标采集可以最大限度的帮助我们采集到更多的SQL语句。为了保证采集到更多的SQL，我们需要进行一个长期的捕捉，每天捕捉好几次。我们在一个生产环境做的是捕捉4次/天。而AWR历史资料库可以帮我们采集到TOP的SQL语句。我们生产环境的项目里面是采集的是一个月的AWR数据。这两份的合集加在一起基本上是系统中一个比较完整的SQL清单。

【注】采集的过程中可能因为有literal sql，这会导致我们的SQLSET的结果集非常大，因为相关的表涉及到一些CLOB字段，如果结果集过大的话，将导致转换成中间表非常的慢。转换到一半因为UNDO不够大，还还会导致出现ORA-01555错误。为了解决这个问题，我建议在采集的过程中实施过滤。具体参考我写的文档：SPA游标采集之去除重复

--------------新建spa用户及赋权SQL>create user spa identifiedbyspadefaulttablespace spa;Usercreated.SQL>grant connect,resource to spa;Grantsucceeded.SQL>grant ADMINISTER SQL TUNING SET to spa;Grantsucceeded.SQL>grant execute on dbms_sqltune to spa;Grantsucceeded.SQL>grantselectany dictionary to spa;Grantsucceeded.-------------创建sql优化集SQL>execdbms_sqltune.create_sqlset('sql_test');PL/SQL procedure successfully completed.SQL>selectname,OWNER,CREATED,STATEMENT_COUNTfromdba_sqlset;NAME OWNER CREATED STATEMENT_COUNT---------------------------------------------------------------------------------------sql_test SPA18-APR-140--------------执行从游标采集SQL

DECLARE

mycur DBMS_SQLTUNE.SQLSET_CURSOR;BEGINOPEN mycur FOR

SELECT value(P)FROM TABLE(dbms_sqltune.select_cursor_cache('parsing_schema_name in (''ORAADMIN'')',NULL,NULL,NULL,NULL,1,NULL,'ALL'))p;dbms_sqltune.load_sqlset(sqlset_name=>'sql_test',populate_cursor=>mycur,load_option=>'MERGE');CLOSE mycur;END;/

关于采集，可以参考文档：How to Load Queries into a SQL Tuning Set (STS) (文档 ID 1271343.1)

2.创建一个中转表，将SQL Tuning Set导入到中转表，导出中转表并传输到测试库；

这个步骤比较简单，但是需要注意的一点是：如果你的游标数量比较多的话，需要注意在转换过程中容易出现ORA-01555的错误。建议最好把undo retention设置大一些。

-------------不要使用sys用户创建stgtab表DBMS_SQLTUNE.create_stgtab_sqlset(table_name=>'SQLSET_TAB',schema_name=>'SPA',tablespace_name=>'SYSAUX');END;/-------------将优化集打包到stgtab表里面BEGINDBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name=>'spa_test',sqlset_owner=>'SPA',staging_table_name=>'SQLSET_TAB',staging_schema_owner=>'SPA');END;/

转换成中转表之后，我们可以再做一次去除重复的操作。当然，你也可以根据module来删除一些不必要的游标。

deletefromSPA.SQLSET_TAB awhererowid!=(selectmax(rowid)fromSQLSET_TAB bwherea.FORCE_MATCHING_SIGNATURE=b.FORCE_MATCHING_SIGNATUREanda.FORCE_MATCHING_SIGNATURE<>0);deletefromSPA.SQLSET_TABwhereMODULE='PL/SQL Developer';

3.导入中转表，并解压中转表的数据到SQL Tuning Set；

这个步骤我们需要把我们导出的中转表的数据迁移到测试平台，然后导入数据，并再一次转换成11g的SQL Tuning Set里面；

-------------导入数据到测试系统exportNLS_LANG=American_America.zhs16gbk

imp spa/spa fromuser=spa touser=spa file=/home/oracle/spa/SQLSET_TAB.dmp feedback=100-------------创建sqlset

SQL>connect spa/spaConnected.SQL>execDBMS_SQLTUNE.create_sqlset(sqlset_name=>'sql_test');PL/SQL procedure successfully completed.-------------unpack到sqlset

SQL>BEGIN2DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name=>'sql_test',3sqlset_owner=>'SPA',4replace=>TRUE,5staging_table_name=>'SQLSET_TAB',6staging_schema_owner=>'SPA');7END;8/PL/SQL procedure successfully completed.

如果在你源端和目标端SQL SET的name,或者owner不同，需要你使用remap_stgtab_sqlset方法对SQL SET的name和owner进行转换。

execdbms_sqltune.remap_stgtab_sqlset(old_sqlset_name=>'sql_test_aaa',old_sqlset_owner=>'aaa',new_sqlset_name=>'sql_test',new_sqlset_owner=>'SPA',staging_table_name=>'SQLSET_TAB',staging_schema_owner=>'SPA');

导入导出SQLSET，可以参考文档：How to Move a SQL Tuning Set from One Database to Another (文档 ID 751068.1)

4.创建SPA任务，先生成10g的trail，然后在11g中再生成11g的trail；

这个步骤一定要注意一点，先检查测试库上面有没有dblink，如果有的话一定要删除，免得连接到其他库做一些不必要的动作，然后就是在11g中生成11g的trail的时间可能比较慢，最好写成脚本放在后台执行。

-------------新建SPA任务vartname varchar2(30);varsname varchar2(30);exec:sname:='sql_test';exec:tname:='SPA_TEST';exec:tname:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name=>:sname,task_name=>:tname);-------------生成10g的trailbeginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'SPA_TEST',execution_type=>'CONVERT SQLSET',execution_name=>'CONVERT_10G');end;/-------------清空shared pool和buffer cache

alter system flush shared_pool;alter system flush BUFFER_CACHE;-------------生成11g的trailbeginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'SPA_TEST',execution_type=>'TEST EXECUTE',execution_name=>'EXEC_11G');end;/

5.执行比较任务，再生成SPA报告；

我们可以从三个维度来进行对比,包括执行时间、CPU_TIME、Buffer_GET等.

-------------从elapsed_time来进行比较beginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'SPA_TEST',execution_type=>'COMPARE PERFORMANCE',execution_name=>'Compare_elapsed_time',execution_params=>dbms_advisor.arglist('execution_name1','CONVERT_10G','execution_name2','EXEC_11G','comparison_metric','elapsed_time'));end;/-------------从cpu_time来进行比较beginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'SPA_TEST',execution_type=>'COMPARE PERFORMANCE',execution_name=>'Compare_CPU_time',execution_params=>dbms_advisor.arglist('execution_name1','CONVERT_10G','execution_name2','EXEC_11G','comparison_metric','CPU_TIME'));end;/-------------从buffer_gets来进行比较beginDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'SPA_TEST',execution_type=>'COMPARE PERFORMANCE',execution_name=>'Compare_BUFFER_GETS_time',execution_params=>dbms_advisor.arglist('execution_name1','CONVERT_10G','execution_name2','EXEC_11G','comparison_metric','BUFFER_GETS'));end;-------------生成SPA报告settrimspool onsettrim onsetpages0setlong999999999setlinesize1000spool spa_report_elapsed_time.html

SELECT dbms_sqlpa.report_analysis_task('SPA_TEST','HTML','ALL','ALL',top_sql=>300,execution_name=>'Compare_elapsed_time')FROM dual;spool off;spool spa_report_CPU_time.html

SELECT dbms_sqlpa.report_analysis_task('SPA_TEST','HTML','ALL','ALL',top_sql=>300,execution_name=>'Compare_CPU_time')FROM dual;spool off;spool spa_report_buffer_time.html

SELECT dbms_sqlpa.report_analysis_task('SPA_TEST','HTML','ALL','ALL',top_sql=>300,execution_name=>'Compare_BUFFER_GETS_time')FROM dual;spool off;spool spa_report_errors.html

SELECT dbms_sqlpa.report_analysis_task('SPA_TEST','HTML','errors','summary')FROM dual;spool off;spool spa_report_unsupport.html

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

6.分析性能退化的SQL语句；

生成完报告后，一共有5份，都需要我们逐一的去分析。我们从ELAPSED_TIME、CPU_TIME、Buffer_GET这三个报告中，我们可以查看到性能下降的SQL。有的SQL可能是CPU TIME有所升高，有的SQL可能是buffer gets有所升高，有的SQL可能这三方面都有所升高。这都是我们需要检查的。这些SQL的性能的退化，有可能执行计划发生了变化，有可能执行计划未变，要找出执行计划变化的原因，这需要我们对SQL优化和优化器、统计信息等有一个很深入的研究。

还有2份报告是errors和unsupport的语句，这类语句我们还是要看一下，一般情况就是有些是因为数据有差异，会出现invalid ROWID等情况。这些不用过多去关注，因为并不是所有的语句都能够精确分析，还有一些insert语句是unsupport的，我们只要分析大部分语句的问题即可。

参考文档：

How to Load Queries into a SQL Tuning Set (STS) (文档 ID 1271343.1)

How to Move a SQL Tuning Set from One Database to Another (文档 ID 751068.1)

Oracle? Database Real Application Testing User’s Guide 11g Release 2 (11.2)