oracle sql performance analyzer
一、概述
1.1 Oracle SPA介绍
11G的新特性SPA(SQL Performance Analyze)现在被广泛的应用到升级和迁移的场景。当然还有一些其他的场景可以考虑使用，比如(参数修改，I/O子系统变更)，但是主要是为了帮助我们检测升级之后性能退化的那些SQL语句，用以防止升级后SQL性能退化导致无法使用的问题
SPA的主要功能集实施步骤如下：
在生产系统上捕捉SQL负载，并生成SQL Tuning Set；
创建一个中转表，将SQL Tuning Set导入到中转表，导出中转表并传输到测试库；
导入中转表，并解压中转表的数据到SQL Tuning Set；
创建SPA任务，先生成10g的trail，然后在11g中再生成11g的trail；
执行比较任务，再生成SPA报告；
分析性能退化的SQL语句；
2.2 术语介绍
2.1 SQLSET
SQLSET即SQL集合，SQLSET用于收集数据库中被执行过的SQL语句，一般可以通过历史的SNAPSHOT采样或者通过游标缓存中收集。SQLSET的所有者在执行的时候就会默认通过这个用户来执行SQL语句。
2.2 SPA任务
SPA任务用于执行SQLSET集中SQL语句的任务，SQL任务建议用SYS或者SYSTEM用户创建。将SQLSET绑定到SPA任务中，进行执行。SPA任务可以执行为CONVERT SQLSET，TEST EXECUTE、SQL PLAN等方式。CONVERT SQLSET是保留SQL集的执行计划、执行资源消耗，不会执行操作；TEST EXECUTE是将SQL集中的SQL语句均执行10次，进行综合数据采集；SQL PLAN只生成执行计划。
二、操作步骤
2.1 创建SPA用户，授予相应权限
源端与目标端均用SYS用户执行。
|
create user spaer identified by "spaer1!spaer" default tablespace TELESALE_HISTDATA; grant connect ,resource to spaer; grant ADMINISTER SQL TUNING SET to spaer; grant execute on dbms_sqltune to spaer; grant select any dictionary to spaer; grant ADVISOR to spaer; grant ADMINISTER ANY SQL TUNING SET to spaer; |
2.2 创建SQLSET（源端SPAER用户）
|
exec dbms_sqltune.create_sqlset(sqlset_name=>'KEFU_SQLSET',sqlset_owner=>'SPAER'); select name,OWNER,CREATED,STATEMENT_COUNT from dba_sqlset; |
2.3 采集SQL语句到SQLSET（源端SPAER用户）
2.3.1 SNAPSHOT采集方式
|
DECLARE cur sys_refcursor; BEGIN open cur for select value(p) from table(dbms_sqltune.select_workload_repository(begin_snap => 56100, end_snap => 58360, basic_filter => 'parsing_schema_name <> ''SYS'' and parsing_schema_name is not NULL')) p; dbms_sqltune.load_sqlset('KEFU_SQLSET', cur); close cur; END; / select count(*) from (select value(p) from table(dbms_sqltune.select_sqlset(sqlset_name=>'KEFU_SQLSET',sqlset_owner=>'KEFU')) p) x; |
2.3.2 游标采集方式
|
DECLARE mycur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN mycur FOR SELECT value(P) FROM TABLE(dbms_sqltune.select_cursor_cache('module = ''JDBC Thin Client'' and parsing_schema_name <>''SYS'' and parsing_schema_name is not NULL', NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) p; dbms_sqltune.load_sqlset(sqlset_name => ' KEFU_SQLSET, sqlset_owner => 'SPAER', populate_cursor => mycur, load_option => 'MERGE'); CLOSE mycur; END; / select count(*) from (select value(p) from table(dbms_sqltune.select_sqlset(sqlset_name=>'KEFU_SQLSET',sqlset_owner=>'SPAER')) p) x; |
2.4 将采集到的SQLSET打包到中间表（源端SPAER用户）
创建中间表
|
declare begin DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name=>'SQLSET_TAB',schema_name=>'SPAER',tablespace_name=>'TELESALE_HISTDATA'); end; / |
将sqlset打包到中间表
|
declare begin dbms_sqltune.pack_stgtab_sqlset(sqlset_name=>'KEFU_SQLSET',sqlset_owner=>'SPAER',staging_table_name=>'SQLSET_TAB',staging_schema_owner=>'SPAER'); end; /
select count(*) from (select distinct sql_id from sqlset_tab) a ; |
打包好后，可以查看SPAER.SQLSET_TAB表中的数据。
2.5 导出中间表（源端 spaer与oracle用户）
导出中间表分为2种方式，一种是业务系统SQL语句操作对象均是用 [用户名.表名] 的形式，这样只需要导出SQL集然后倒入到目标库，直接执行；另一种是业务系统SQL语句操作对象均直接用 [表名] 的时间，这种方式倒入之后会出现表或视图不存在的错误。
2.5.1 [用户名.表名]形式导出中间表
|
exp spaer/"spaer1!spaer" file=/oracle/expdpdir/SQLSET_TAB.dmp query=\"where parsing_schema_name is not null\" log=/oracle/expdpdir/SQLSET_TAB.log tables=SQLSET_TAB |
2.5.2 [表名]形式导出中间表
这种方式比较麻烦，业务系统有多少用户，就需要按多少种用户导出。下面举一个ICDPOOL用户导出。首先将SPAER.SQLSET_TAB生成一张缓存表SPAER.SQLSET_TAB_TMP，然后删除SPAER.SQLSET_TAB，重新创建新的SPAER.SQLSET_TAB。
|
create table spaer.sqlset_tab_tmp as select * from spaer.sqlset_tab;
create table spaer.sqlset_tab as select * from spaer.sqlset_tab_tmp where parsing_schema_name='ICDPOOL';
exp spaer/"spaer1!spaer" file=/oracle/expdpdir/SQLSET_TAB.dmp query=\"where parsing_schema_name is not null\" log=/oracle/expdpdir/SQLSET_TAB.log tables=SQLSET_TAB |
2.5.3 拷贝中间表到目标库
|
scp SQLSET_TAB.dmp oracle@10.180.213.68:/home/oracle/expdpdir |
2.6 导入中间表（目标端）
|
imp spaer/"spaer1!spaer" file=/home/oracle/expdpdir/SQLSET_TAB.dmp log=/home/oracle/expdpdir/SQLSET_TAB.log tables=SQLSET_TAB |
2.7 将中间表upload到SQLSET集（目标端SYS用户）
同样在导入的时候也分为2种情况
2.7.1 [用户名.表名]形式
|
begin dbms_sqltune.UNPACK_STGTAB_SQLSET(sqlset_name=>'KEFU_SQLSET_ICDPOOL',sqlset_owner=>'SPAER',replace=>FALSE,staging_table_name=>'SQLSET_TAB',staging_schema_owner=>'SPAER'); end; /
select count(*) from (select value(p) from table(dbms_sqltune.select_sqlset(sqlset_name=>'KEFU_SQLSET_ICDPOOL',sqlset_owner=>'ICDPOOL')) p) x; |
2.7.2 [表名]形式
需要先转换SQLSET的所有者。
|
exec dbms_sqltune.remap_stgtab_sqlset(old_sqlset_name =>'KEFU_SQLSET',old_sqlset_owner => 'SPAER', new_sqlset_name => 'KEFU_SQLSET_ICDPOOL',new_sqlset_owner => 'ICDPOOL', staging_table_name => 'SQLSET_TAB',staging_schema_owner => 'SPAER'); begin dbms_sqltune.UNPACK_STGTAB_SQLSET(sqlset_name=>'KEFU_SQLSET_ICDPOOL',sqlset_owner=>'ICDPOOL',replace=>FALSE,staging_table_name=>'SQLSET_TAB',staging_schema_owner=>'SPAER'); end; / select count(*) from (select value(p) from table(dbms_sqltune.select_sqlset(sqlset_name=>'KEFU_SQLSET_ICDPOOL',sqlset_owner=>'ICDPOOL')) p) x; |
2.8 创建SPA任务（目标端SYS用户）
2.8.1 [用户名.表名]形式
|
set serveroutput on declare v_taskname varchar2(1000); begin v_taskname:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'KEFU_SQLSET',sqlset_owner=>'SPAER',task_name => 'KEFU_SQLSET_TASK'); dbms_output.put_line(v_taskname); end; / |
2.8.2 [表名]形式
|
set serveroutput on declare v_taskname varchar2(1000); begin v_taskname:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'KEFU_SQLSET_ICDPOOL',sqlset_owner=>'ICDPOOL',task_name => 'KEFU_SQLSET_ICDPOOL_TASK'); dbms_output.put_line(v_taskname); end; / |
2.9 生成源端trail文件（目标端SYS用户）
2.9.1 [用户名.表名]形式
|
declare begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'KEFU_SQLSET_TASK',execution_type=>'CONVERT SQLSET',execution_name=>'KEFU_CONVERT',execution_params=>dbms_advisor.arglist('APPLY_CAPTURED_COMPILENV', 'NO','TIME_LIMIT','3600','basic_filter','upper(sql_text) like ''SELECT%''')); end; / |
2.9.2 [表名]形式 （SYS用户执行）
|
declare begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'KEFU_SQLSET_ICDPOOL_TASK',execution_type=>'TEST EXECUTE ',execution_name=>'KEFU_EXEC',execution_params=>dbms_advisor.arglist('APPLY_CAPTURED_COMPILENV', 'NO','TIME_LIMIT','3600','basic_filter','upper(sql_text) like ''SELECT%''')); end; / |
2.10 生成目标端trail文件（目标端SYS用户执行）
2.10.1 [用户名.表名]形式
|
我们可以从三个维度来进行对比,包括执行时间、CPU_TIME、Buffer_GET等.
-------------从elapsed_time来进行比较 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'KEFU_SQLSET_TASK', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_elapsed_time', execution_params => dbms_advisor.arglist('execution_name1', 'KEFU_CONVERT', 'execution_name2', 'KEFU_EXEC', 'comparison_metric', 'elapsed_time') ); end; / -------------从cpu_time来进行比较 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'KEFU_SQLSET_TASK', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_CPU_time', execution_params => dbms_advisor.arglist('execution_name1', 'KEFU_CONVERT', 'execution_name2', 'KEFU_EXEC', 'comparison_metric', 'CPU_TIME') ); end; / -------------从buffer_gets来进行比较 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'KEFU_SQLSET_TASK', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_BUFFER_GETS_time', execution_params => dbms_advisor.arglist('execution_name1', 'KEFU_CONVERT', 'execution_name2', 'KEFU_EXEC', 'comparison_metric', 'BUFFER_GETS') ); end; / |
2.10.2 [用户名]形式
|
我们可以从三个维度来进行对比,包括执行时间、CPU_TIME、Buffer_GET等.
-------------从elapsed_time来进行比较 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'KEFU_SQLSET_ICDPOOL_TASK', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_elapsed_time', execution_params => dbms_advisor.arglist('execution_name1', 'KEFU_CONVERT', 'execution_name2', 'KEFU_EXEC', 'comparison_metric', 'elapsed_time') ); end; / -------------从cpu_time来进行比较 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'KEFU_SQLSET_ICDPOOL_TASK', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_CPU_time', execution_params => dbms_advisor.arglist('execution_name1', 'KEFU_CONVERT', 'execution_name2', 'KEFU_EXEC', 'comparison_metric', 'CPU_TIME') ); end; / -------------从buffer_gets来进行比较 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'KEFU_SQLSET_ICDPOOL_TASK', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_BUFFER_GETS_time', execution_params => dbms_advisor.arglist('execution_name1', 'KEFU_CONVERT', 'execution_name2', 'KEFU_EXEC', 'comparison_metric', 'BUFFER_GETS') ); end; / |
2.11 生成报告（目标端SYS用户执行）
2.11.1 [用户名.表名]形式
|
-------------生成SPA报告 set trimspool on set trim on set pages 0 set linesize 10000 set long 999999999 set longchunksize 10000000
spool /home/oracle/expdpdir/spa_report_elapsed_time.html SELECT dbms_sqlpa.report_analysis_task('KEFU_SQLSET_TASK', 'HTML', 'ALL','ALL', top_sql=>18,execution_name=>'Compare_elapsed_time') FROM dual; spool off;
spool /home/oracle/expdpdir/spa_report_CPU_time.html SELECT dbms_sqlpa.report_analysis_task('KEFU_SQLSET_TASK', 'HTML', 'ALL','ALL', top_sql=>18,execution_name=>'Compare_CPU_time') FROM dual; spool off;
spool /home/oracle/expdpdir/spa_report_buffer_time.html SELECT dbms_sqlpa.report_analysis_task('KEFU_SQLSET_TASK','HTML','ALL','ALL',top_sql=>20,execution_name=>'Compare_BUFFER_GETS_time') FROM dual; spool off;
spool /home/oracle/expdpdir/spa_report_errors.html SELECT dbms_sqlpa.report_analysis_task('KEFU_SQLSET_TASK', 'HTML', 'errors','summary') FROM dual; spool off; |
2.11.2 [用户名]形式
|
-------------生成SPA报告 set trimspool on set trim on set pages 0 set linesize 10000 set long 999999999 set longchunksize 10000000
spool /home/oracle/expdpdir/spa_report_elapsed_time.html SELECT dbms_sqlpa.report_analysis_task('KEFU_SQLSET_ICDPOOL_TASK', 'HTML', 'ALL','ALL', top_sql=>18,execution_name=>'Compare_elapsed_time') FROM dual; spool off;
spool /home/oracle/expdpdir/spa_report_CPU_time.html SELECT dbms_sqlpa.report_analysis_task('KEFU_SQLSET_ICDPOOL_TASK', 'HTML', 'ALL','ALL', top_sql=>18,execution_name=>'Compare_CPU_time') FROM dual; spool off;
spool /home/oracle/expdpdir/spa_report_buffer_time.html SELECT dbms_sqlpa.report_analysis_task('KEFU_SQLSET_ICDPOOL_TASK','HTML','ALL','ALL',top_sql=>20,execution_name=>'Compare_BUFFER_GETS_time') FROM dual; spool off;
spool /home/oracle/expdpdir/spa_report_errors.html SELECT dbms_sqlpa.report_analysis_task('KEFU_SQLSET_ICDPOOL_TASK', 'HTML', 'errors','summary') FROM dual; spool off;
spool /home/oracle/expdpdir/spa_report_unsupport.html SELECT dbms_sqlpa.report_analysis_task('KEFU_SQLSET_ICDPOOL_TASK', 'HTML', 'unsupported','all') FROM dual; spool off; |
三、报告说明
