10GR2迁移升级到11GR2压力性能测试二

最近对一个10GR2的环境进行了迁移升级至ORACLE 11GR2，在业务真正进行切换之前，对升级的环境进行了大量的性能分析和压力测试工作，ORACLE 11G中提供的real application testing确实很方便实用。暂且将部分测试步骤记录在此，后续整理成一个完整的文档
SQL REPLAY 部分10G database 执行14:35:34 SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'RAC38N1',description => 'SPA TEST'); PL/SQL procedure successfully completed Executed in 0.109 seconds 将快照ID 20660,20661 的SQL 放到SQLSET14:46:04 SQL> declare cur sys_refcursor;           2  begin           3  open cur for           4  SELECT VALUE(P) FROM           5  TABLE(DBMS_SQLTUNE.select_workload_repository(20660,20661)) p;           6  dbms_sqltune.load_sqlset(sqlset_name => 'RAC38N1',populate_cursor => cur);           7  close cur;           8  end;           9  / PL/SQL procedure successfully completed Executed in 0.546 seconds--也可以用V$SQL中的符合条件的SQL_TEXT建立SQLSET-- dbms_sqltune.select_cursor_cache('sql_text like ''select * from mytab%''',                                     null,null,null,null,null,null,'ALL')) P;查看SQLSET中的内容 select * from table(dbms_sqltune.select_sqlset('RAC38N1'));......result 将10G环境的SQLSET传输到 11G 环境--transporting 首先在10G中建立存储SQLSET 的表14:46:06 SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => 'SPA_38N1',schema_name => 'PAYADM'); PL/SQL procedure successfully completed Executed in 0.438 seconds将SQLSET的内容PACK到表中 14:51:24 SQL> EXEC DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'RAC38N1',                                                   staging_table_name => 'SPA_38N1',                                                   staging_schema_owner => 'PAYADM'); PL/SQL procedure successfully completed Executed in 1.047 seconds11G database 执行使用DBLINK的方式将存储SQLSET内容的表结构内容传到11G数据库55.52运行下面语句将SQLSET  加载到11G SQLSET15:04:43 SQL> EXEC DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'RAC38N1',                                                     replace => TRUE,                                                     staging_table_name => 'SPA_38N1',                                                     staging_schema_owner => 'PAYADM'); PL/SQL procedure successfully completed Executed in 0.422 seconds创建analysis_task RAC38N1DECLARE V_SPA_NAME VARCHAR2(100);BEGINV_SPA_NAME:=spa_dbms_xhl.create_analysis_task(sqlset_name => 'RAC38N1',task_name => 'spa_38_task');dbms_output.put_line('spa_name='||v_spa_name);end;/如果没有安装real application testing 组件将报下面错误ORA-00438: 未安装 Real Application Testing 选件ORA-06512: 在 "SYS.PRVT_SMGUTIL", line 80ORA-06512: 在 "SYS.DBMS_SQLPA", line 220ORA-06512: 在 line 4shutdown 数据库 运行$ORACLE_HOME/oui/runInstaller 添加此组件9:38:20 SQL> DECLARE V_SPA_NAME VARCHAR2(100);          2  BEGIN          3  V_SPA_NAME:=dbms_sqlpa.create_analysis_task(sqlset_name => 'RAC38N1',task_name => 'spa_38_task');          4  dbms_output.put_line('spa_name='||v_spa_name);          5  end;          6  / PL/SQL procedure successfully completed Executed in 0.578 seconds生成10G 的trail9:42:35 SQL> exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',                                                   execution_type => 'CONVERT SQLSET',                                                   execution_name => 'SPA_3810G'); PL/SQL procedure successfully completed生成11G 的trail9:43:57 SQL> exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',                                                   execution_type => 'TEST EXECUTE',                                                   execution_name => 'SPA_5211G'); PL/SQL procedure successfully completed Executed in 57.484 seconds开始比较9:54:54 SQL> elapsed_time 比较exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',execution_type => 'COMPARE PERFORMANCE',                                                   execution_name => 'compare_elp_time',                                                    execution_params => dbms_advisor.argList('comparison_metric','elapsed_time'));buffer_gets 比较                                                   exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',execution_type => 'COMPARE PERFORMANCE', execution_name => 'compare_buff_gets',  execution_params => dbms_advisor.argList('comparison_metric','buffer_gets'));                                                    PL/SQL procedure successfully completed Executed in 0.532 seconds生成比较报告select dbms_sqlpa.report_analysis_task('spa_38_task','HTML','ALL','ALL') FROM DUAL报告示例：
Workload Impact Threshold: 1%
SQL Impact Threshold: 1%

Report Summary

Projected Workload Change Impact:
Overall Impact:29.59%Improvement Impact:33.06%Regression Impact:-3.46%
SQL Statement Count
SQL CategorySQL CountPlan Change CountOverall13230Improved43Regressed11Unchanged7426with Errors440Unsupported90
Top 79 SQL Sorted by Absolute Value of Change Impact on the Workload
object_idsql_idImpact on  WorkloadExecution  FrequencyMetric  BeforeMetric  AfterImpact  on SQLPlan  Change1500yp9c5psy8h6g19.7%70599247932.29295032517815728.14%y231a3kxbucrmcw2u6.76%8267209556.443933712517197.53%y252d9ub4kj07srsj-3.46%4881094297.715163932867155-162.01%y2026dtnjvsv15haj3.45%7479241947.4821500212650947.71%y2218n54r6zd8hb6h3.14%968318142.282967231572799.67%n