Oracle数据库升级性能回归测试利器-SPA

Oracle · newbie_l · 于 1年前发布 · 1454 次阅读

作者:云吞信息-九条

Oracle Database 11g引入了SQL Performance Analyzer(简称SPA),该工具可评估SQL执行环境的变化对性能的影响,常应用于数据库升级和迁移的场景。使用SPA可提前识别性能退化的SQL语句,预防迁移或升级后SQL性能下降。

SPA实施的主要步骤如下:

1、收集SQL语句及负载数据,生成SQL Tuning Set

2、将SQL Tuning Set迁移至测试库

3、创建SPA任务,生成对比的trail

4、执行比较任务,生成SPA对比报告

5、分析报告

近期某客户Oracle 10g数据库需升级至11g,使用SPA过程如下:

1、收集SQL语句及负载数据的方法有多种,常见的来源有cursor cache/awr,本次以awr作为收集源:

--赋权限

SQL> grant administer sql tuning set to TESTUSER;

SQL> grant select any dictionary to TESTUSER;

SQL> grant advisor to TESTUSER;


--创建sql tuning set

SQL> exec dbms_sqltune.create_sqlset('sqlset_for_upgrade');

--从awr snapshots收集SQL及负载数据

DECLARE

  spacur SYS_REFCURSOR;
  
BEGIN

  OPEN spacur FOR
  
    SELECT VALUE (P)
    
      FROM TABLE(
      
        dbms_sqltune.select_workload_repository(
        
          m,--起始snap_id
          
          n,--终止snap_id
          
          q'[ parsing_schema_name in ('TESTUSER') ]',
          
          
          NULL,NULL,NULL,NULL,1,NULL,'ALL'
        )
        
      ) P;
      
  dbms_sqltune.load_sqlset(
  
    sqlset_name => 'sqlset_for_upgrade',
    
    populate_cursor => spacur,
    
    load_option => 'MERGE'
    
  );
  
  CLOSE spacur;
  
END;

/

2、将SQL Tuning Set打包迁移至测试库


--创建中间表

BEGIN

  dbms_sqltune.create_stgtab_sqlset(
  
    table_name => 'SQLSET_TAB',
    
    schema_name => 'TESTUSER',
    
    tablespace_name => 'SYSAUX'
    
  );
  
END;

/


--打包sql tuning set到中间表

BEGIN

  DBMS_SQLTUNE.pack_stgtab_sqlset(
  
    sqlset_name => 'sqlset_for_upgrade',
    
    sqlset_owner => 'TESTUSER',
    
    staging_table_name => 'SQLSET_TAB',
    
    staging_schema_owner => 'TESTUSER'
    
  );
  
END;

/


--将中间表迁移至测试库

exp/imp

expdp/impdp


--测试库用户赋权

SQL> grant administer sql tuning set to TESTUSER;

SQL> grant select any dictionary to TESTUSER;

SQL> grant advisor to TESTUSER;


--测试库创建sql tunning set

exec DBMS_SQLTUNE.create_sqlset(sqlset_name => 'sqlset_for_upgrade');


--测试库解包sql tunning set

BEGIN

  DBMS_SQLTUNE.unpack_stgtab_sqlset(
  
    sqlset_name => 'sqlset_for_upgrade',
    
    sqlset_owner => 'TESTUSER',
    
    replace => TRUE,
    
    staging_table_name => 'SQLSET_TAB',
    
    staging_schema_owner => 'TESTUSER'
    
  );
  
END;

/

3、创建SPA任务,生成对比trail


--创建分析任务

var v_task varchar2(64);

exec :v_task:= DBMS_SQLPA.create_analysis_task(

  sqlset_name => 'sqlset_for_upgrade',
  
  task_name => 'SPA_COMPARISON');
  

--生成10g trail

begin

  DBMS_SQLPA.execute_analysis_task(
  
    task_name => 'SPA_COMPARISON',
    
    execution_type => 'CONVERT SQLSET',
    
    execution_name => 'TRAIL_10G'
    
  );
  
end;

/


--生成11g trail

begin

  DBMS_SQLPA.execute_analysis_task(
  
    task_name => 'SPA_COMPARISON',
    
    execution_type => 'TEST EXECUTE',
    
    execution_name => 'TRAIL_11G'
    
  );
  
end;

/

4、以elapsed time、cpu time、buffer gets三个维度执行比较任务,生成SPA对比报告


--按elapsed time对比

begin

  DBMS_SQLPA.execute_analysis_task(
  
    task_name => 'SPA_COMPARISON',
    
    execution_type => 'COMPARE PERFORMANCE',
    
    execution_name => 'Compare_elapsed_time',
    
    execution_params => 
    
      dbms_advisor.arglist(
      
        'execution_name1',
        
        'TRAIL_10G',
        
        'execution_name2',
        
        'TRAIL_11G',
        
        'comparison_metric',
        
        'ELAPSED_TIME') 
        
  );
  
end;

/


--按cpu time对比

begin

  DBMS_SQLPA.execute_analysis_task(
  
    task_name => 'SPA_COMPARISON',
    
    execution_type => 'COMPARE PERFORMANCE',
    
    execution_name => 'Compare_CPU_time',
    
    execution_params => 
    
      dbms_advisor.arglist(
      
        'execution_name1', 
        
        'TRAIL_10G', 
        
        'execution_name2', 
        
        'TRAIL_11G', 
        
        'comparison_metric', 
        
        'CPU_TIME') 
        
  );
  
end;

/


--按buffer gets对比

begin

  DBMS_SQLPA.execute_analysis_task(
  
    task_name => 'SPA_COMPARISON',
    
    execution_type => 'COMPARE PERFORMANCE',
    
    execution_name => 'Compare_BUFFER_GETS',
    
    execution_params => 
    
      dbms_advisor.arglist(
      
        'execution_name1', 
        
        'TRAIL_10G', 
        
        'execution_name2', 
        
        'TRAIL_11G', 
        
        'comparison_metric', 
        
        'BUFFER_GETS') 
        
  );
  
end;

/


--生成SPA报告

set trimspool on trim on

set linesize 1024 pages 0 long 999999999 longchunksize 999999999


--按elapsed time对比报告

spool spa_report_elapsed_time.html

SELECT DBMS_SQLPA.report_analysis_task(

  'SPA_COMPARISON',
  
  'HTML', 
  
  'ALL',
  
  'ALL', 
  
  top_sql=>300,
  
  execution_name=>'Compare_elapsed_time',
  
  task_owner=>'TESTUSER') FROM dual;
  
spool off;


--按cpu time对比报告

spool spa_report_CPU_time.html

SELECT DBMS_SQLPA.report_analysis_task(

  'SPA_COMPARISON', 
  
  'HTML', 
  
  'ALL',
  
  'ALL', 
  
  top_sql=>300,
  
  execution_name=>'Compare_CPU_time',
  
  task_owner=>'TESTUSER') FROM dual;
  
spool off;


--按buffer gets对比报告

spool spa_report_buffer_time.html

SELECT DBMS_SQLPA.report_analysis_task(

  'SPA_COMPARISON',
  
  'HTML',
  
  'ALL',
  
  'ALL',
  
  top_sql=>300,
  
  execution_name=>'Compare_BUFFER_GETS',
  
  task_owner=>'TESTUSER') FROM dual;
  
spool off;

5、分析上一步生成的报告,以elapsed time的对比报告为例,大部分的SQL执行效率未改变,15条 SQL执行效率更高,1条 SQL执行效率下降,点击对应的sql_id链接可查看执行计划、统计信息及其它数据,进一步分析执行效率下降的原因。

Tips:

执行DBMS_SQLPA.report_analysis_task生成报告时,可能遇到如下错误

ERROR: 

ORA-27163: out of memory 

ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 8211 

ORA-06512: at "SYS.DBMS_SQLPA", line 515 

ORA-06512: at line 1

可通过设置如下event解决:

SQL> alter session set events '31156 trace name context forever, level 0x400';

本帖已经被管理员设置为: 精华帖 !
共收到 0 条回复
没有找到数据。
回复本帖 (需要登录)