SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report

联系: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]$ 
此条目发表在 未分类 分类目录。将固定链接加入收藏夹。

评论功能已关闭。