11g SPA (sql Performance Analyze) 进行升级测试

转载 2017年11月14日 18:10:00

原文地址:http://ju.outofmemory.cn/entry/77139

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


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

  1. 在生产系统上捕捉SQL负载,并生成SQL Tuning Set;
  2. 创建一个中转表,将SQL Tuning Set导入到中转表,导出中转表并传输到测试库;
  3. 导入中转表,并解压中转表的数据到SQL Tuning Set;
  4. 创建SPA任务,先生成10g的trail,然后在11g中再生成11g的trail;
  5. 执行比较任务,再生成SPA报告;
  6. 分析性能退化的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 identified by spa default tablespace spa;
User created.
SQL> grant connect ,resource to spa;
Grant succeeded.
SQL> grant ADMINISTER SQL TUNING SET to spa;
Grant succeeded.
SQL> grant execute on dbms_sqltune to spa;
Grant succeeded.
SQL> grant select any dictionary to spa;
Grant succeeded.
-------------创建sql优化集
SQL> exec dbms_sqltune.create_sqlset('sql_test');
PL/SQL procedure successfully completed.
SQL> select name,OWNER,CREATED,STATEMENT_COUNT from dba_sqlset;
NAME                           OWNER                          CREATED      STATEMENT_COUNT
------------------------------ ------------------------------ ------------ ---------------
sql_test                       SPA                            18-APR-14                0
--------------执行从游标采集SQL
DECLARE
  mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN mycur FOR
    SELECT value(P)
      FROM TABLE(dbms_sqltune.select_cursor_cache('parsing_schema_name in (''ORAADMIN'')',
                                                  NULL,
                                                  NULL,
                                                  NULL,
                                                  NULL,
,
                                                  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表里面
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'spa_test',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'SPA');
END;
/

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

delete from SPA.SQLSET_TAB a where rowid !=(select max(rowid) from SQLSET_TAB b where a.FORCE_MATCHING_SIGNATURE=b.FORCE_MATCHING_SIGNATURE and a.FORCE_MATCHING_SIGNATURE<>0);

delete from SPA.SQLSET_TAB where MODULE='PL/SQL Developer';

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

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

-------------导入数据到测试系统
export NLS_LANG=American_America.zhs16gbk
imp spa/spa fromuser=spa touser=spa file=/home/oracle/spa/SQLSET_TAB.dmp feedback=100

-------------创建sqlset
SQL> connect spa/spa
Connected.
SQL> exec DBMS_SQLTUNE.create_sqlset(sqlset_name => 'sql_test');
PL/SQL procedure successfully completed.

-------------unpacksqlset
SQL> BEGIN
  DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'sql_test',
  sqlset_owner => 'SPA',
  replace => TRUE,
  staging_table_name => 'SQLSET_TAB',
  staging_schema_owner => 'SPA');
  END;
  /
PL/SQL procedure successfully completed.

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

exec dbms_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任务
var tname varchar2(30);
var sname varchar2(30);
exec :sname := 'sql_test';
exec :tname := 'SPA_TEST';
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);

-------------生成10gtrail
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'CONVERT SQLSET',
execution_name => 'CONVERT_10G');
end;
/

-------------清空shared poolbuffer cache
alter system flush shared_pool;
alter system flush BUFFER_CACHE;

-------------生成11gtrail
begin
DBMS_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来进行比较
begin
DBMS_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来进行比较
begin
DBMS_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来进行比较
begin
DBMS_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报告
set trimspool on
set trim on
set pages 0
set long 999999999
set linesize 1000

spool 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)


相关文章推荐

SQL Performance Analyzer SPA常用脚本汇总

SQL Performance Analyzer SPA常用脚本汇总 ----转载http://www.oracledatabase12g.com/archives/spa-script.htm...

使用SPA(sql performance Analyzer)

SPA比较在数据库更新前后定义在特定sql优化集中的特定sql语句的性能,重大升级,参数改变,索引或是统计信息的改变。 1.建立测试环境 create table t1 as select * ...
  • huhu307
  • huhu307
  • 2016年04月20日 23:06
  • 300

『ORACLE』SPA性能分析器(11g)

SPA的主要作用:主要测试由于系统环境变更包括操作系统变更,数据库升级等对sql性能的影响。尽量减少由于系统环境的变更对业务的影响程度。 分析流程:1、获取sql存取到Tunning set=>...

在ODI11g中创建Microsoft SQL Server服务器测试报错问题

在ODI11.1.1.5.0中创建了Microsoft Sql Server的服务器, JDBC驱动程序名称选择了Microsoft SQL Server 2005 Driver for JDBC ...

asm 10g & 11g 升级降级测试

asm 10g & 11g 升级降级测试

The outline: Oracle Database 11g: Performance Tuning DBA Release 2

上周在OU培训的这课!

11G Concept 第六章翻译 Data Dictionary and Dynamic Performance Views(数据字典和动态性能试图)

DataDictionary and Dynamic Performance Views(数据字典和动态性能试图) 这一章介绍了每个数据库的具有的只读参考表(视图)的最重要部分,统称为数据字典。这张还...

使用window.performance进行浏览器性能测试

互联网产品开发过程中,想了解用户体验一直是一件比较困难的事情。之前很难使用js获得用户访问网站的连接建立时间、dns时间等信息,想得到这些信息一般是建立固定的监测点或者使用专门的测试客户端软件。不过,...

PL/SQL Developer工具登陆一个新创建的用户进行查询时报Dynamic Performance Tables not accessible

今天用PL/SQL Developer工具登陆一个新创建的用户进行查询时报Dynamic Performance Tables not accessible 错误 Automatic s...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:11g SPA (sql Performance Analyze) 进行升级测试
举报原因:
原因补充:

(最多只允许输入30个字)