联系：QQ(5163721)
标题：SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set
SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report
1，查看当前STS中的SQL数量：
09:52:42 LUNAR@ lunardb> select count(*) from LUNAR.SQLSET_TAB_LUNAR;
COUNT(*)
----------
496641
Elapsed: 00:00:00.24
09:53:13 LUNAR@ lunardb>
删除一些没用的：
10:04:15 LUNAR@ lunardb> delete from LUNAR.SQLSET_TAB_LUNAR
10:07:33 2 where (PARSING_SCHEMA_NAME in ('LUNAR', 'GGUSR','EXFSYS','SYS') )
10:07:33 3 or ( module in ('PL/SQL Developer','SQL*Plus','sqlplus.exe','plsqldev.exe','DBMS_SCHEDULER') );
701 rows deleted.
Elapsed: 00:00:00.96
10:07:34 LUNAR@ lunardb> commit;
Commit complete.
Elapsed: 00:00:00.00
10:07:38 LUNAR@ lunardb>
2，在新库创建Lunar_11201STS_LUNAR SQLSET集
create user LUNARSPA identified by LUNARSPA;
grant connect,resource,dba to LUNARSPA;
10:24:41 LUNAR@ lunardb>
10:24:41 2 from dba_users
10:24:41 3 where username in ('LUNAR','LUNARSPA')
10:24:41 4 order by 1,2;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
LUNAR USERS TEMP
LUNARSPA USERS TEMP
Elapsed: 00:00:00.03
10:24:42 LUNAR@ lunardb>
—(2)使用LUNAR用户，创建STS：Lunar_11204STS_LUNAR
10:24:42 LUNAR@ lunardb> conn LUNARSPA/LUNARSPA Connected. 10:25:33 LUNARSPA@ lunardb> exec DBMS_SQLTUNE.create_sqlset(sqlset_name => 'Lunar_11204STS_LUNAR'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.05 10:25:40 LUNARSPA@ lunardb>
—(2)使用LUNARSPA用户，将源库的LUNAR.Lunar_11201STS_LUNAR的SQL优化器映射到LUNARSPA.Lunar_11204STS_LUNAR
10:28:24 LUNARSPA@ lunardb> select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET; NAME OWNER CREATED STATEMENT_COUNT ------------------------------ ------------------------------ ------------------- --------------- Lunar_11204STS_LUNAR LUNARSPA 2015-04-19 10:25:40 0 Elapsed: 00:00:00.00 10:40:44 LUNARSPA@ lunardb> exec dbms_sqltune.remap_stgtab_sqlset(old_sqlset_name =>'Lunar_11201STS_LUNAR',old_sqlset_owner => 'LUNAR', new_sqlset_name => 'Lunar_11204STS_LUNAR',new_sqlset_owner => 'LUNARSPA', staging_table_name => 'SQLSET_TAB_LUNAR',staging_schema_owner => 'LUNAR'); PL/SQL procedure successfully completed. Elapsed: 00:00:09.39 10:41:06 LUNARSPA@ lunardb>
使用LUNARSPA用户执行remap：
BEGIN DBMS_SQLTUNE.unpack_stgtab_sqlset( sqlset_name => 'Lunar_11201STS_LUNAR', sqlset_owner => 'SPA', replace => TRUE, staging_table_name => 'SQLSET_TAB', staging_schema_owner => 'SPA'); END; / 11:21:16 LUNARSPA@ lunardb> select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET; NAME OWNER CREATED STATEMENT_COUNT ------------------------------ ------------------------------ ------------------- --------------- Lunar_11204STS_LUNAR LUNARSPA 2015-04-19 11:19:04 6005 Elapsed: 00:00:00.01 11:21:19 LUNARSPA@ lunardb>
至此，SPA在新库的数据已经准备完毕，可以开始生成SPA报告了。
常见报告的就提步骤如下：
1）创建SPA任务
11:33:10 LUNARSPA@ lunardb> exec :sname := 'Lunar_11204STS_GPS'; exec :tname := 'SPA_LUNARTEST1'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 11:33:10 LUNARSPA@ lunardb> PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 11:33:10 LUNARSPA@ lunardb> exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname); PL/SQL procedure successfully completed. Elapsed: 00:00:00.19 11:33:10 LUNARSPA@ lunardb>
2）生成11.2.0.1的SPA Trail,采用STS转化方式
begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPA_LUNARTEST1', execution_type => 'CONVERT SQLSET', execution_name => 'CONVERT_11204G'); end; / 3）在11.2.0.4中测试执行，从性能数据生成SPA trial begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPA_LUNARTEST1', execution_type => 'TEST EXECUTE', execution_name => 'EXEC_11204G'); end; /
5 执行比较任务(一般取Elapsed Time、CPU Time、Buffer Get等指标)
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_LUNARTEST1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_11204G', 'execution_name2', 'EXEC_11204G', 'comparison_metric', 'elapsed_time') );
end;
/
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_LUNARTEST1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_CPU_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_11204G', 'execution_name2', 'EXEC_11204G', 'comparison_metric', 'CPU_TIME') );
end;
/
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_LUNARTEST1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_BUFFER_GETS_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_11204G', 'execution_name2', 'EXEC_11204G', 'comparison_metric', 'BUFFER_GETS') );
end;
/
6 生成SPA报告
spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time',top_sql=>500) FROM dual;
spool off;
spool spa_report_CPU_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'ALL','ALL', execution_name=>'Compare_CPU_time',top_sql=>500) FROM dual;
spool off;
spool spa_report_buffer_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1','HTML','ALL','ALL', execution_name=>'Compare_BUFFER_GETS_time',top_sql=>500) FROM dual;
spool off;
spool spa_report_elapsed_time_regressed.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'REGRESSED','ALL', execution_name=>'Compare_elapsed_time',top_sql=>500) FROM dual;
spool off;
spool spa_report_CPU_time_regressed.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'REGRESSED','ALL', execution_name=>'Compare_CPU_time',top_sql=>500) FROM dual;
spool off;
spool spa_report_buffer_time_regressed.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1','HTML','REGRESSED','ALL', execution_name=>'Compare_BUFFER_GETS_time',top_sql=>500) FROM dual;
spool off;
spool spa_report_errors.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'errors','summary') FROM dual;
spool off;
spool spa_report_unsupport.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'unsupported','all') FROM dual;
spool off;
生成的报告一般如下：
-rwxrwxrwx 1 oracle oracle 1850 Apr 19 21:33 report_spa.sh -rw-rw-r-- 1 oracle oracle 8498134 Apr 19 21:37 spa_report_elapsed_time.html -rw-rw-r-- 1 oracle oracle 8954773 Apr 19 21:41 spa_report_CPU_time.html -rw-rw-r-- 1 oracle oracle 7941640 Apr 19 21:44 spa_report_buffer_time.html -rw-rw-r-- 1 oracle oracle 38933 Apr 19 21:44 spa_report_elapsed_time_regressed.html -rw-rw-r-- 1 oracle oracle 61982 Apr 19 21:44 spa_report_CPU_time_regressed.html -rw-rw-r-- 1 oracle oracle 28886 Apr 19 21:44 spa_report_buffer_time_regressed.html -rw-rw-r-- 1 oracle oracle 15537 Apr 19 21:44 spa_report_errors.html -rw-rw-r-- 1 oracle oracle 58703 Apr 19 21:44 spa_report_unsupport.html -rw-rw-r-- 1 oracle oracle 18608938 Apr 19 21:44 report_spa.log [oracle@lunardb tmp]$
