oracle spa-snowhill

yanqiufang

　　oracle spa属于real application test的内容，用来对比sql前后性能变化，比如我要比较一条sql前后的的性能变化：
　　--单条sql流程
　　

alter session set optimizer_features_enable='10.2.0.4';　　
variable g_task varchar2(100);
　　
exec :g_task:= dbms_sqlpa.create_analysis_task(task_name=>'TASK2',sql_text=>'select count(*) from employees where employee_id>10')
　　
exec  dbms_sqlpa.execute_analysis_task(task_name=>:g_task,execution_type=>'test execute',execution_name=>'before_change');
　　
alter table employees drop primary key drop index;
　　
alter session set optimizer_features_enable='11.2.0.4';
　　
--EXEC DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER( :g_task,'comparison_metric', 'buffer_gets');
　　
exec  dbms_sqlpa.execute_analysis_task(task_name=>:g_task,execution_type=>'test execute',execution_name=>'after change');
　　
exec  dbms_sqlpa.execute_analysis_task(task_name=>:g_task,execution_type=>'compare performance',execution_name=>'compar');
　　
spool d:\spa_report_elapsed_time.html
　　
SELECT dbms_sqlpa.report_analysis_task(:g_task, 'HTML', 'ALL','ALL') FROM dual;
　　
spool off;
　　
exec  DBMS_SQLPA.DROP_ANALYSIS_TASK('TASK2');
　　
begin
　　
DBMS_SQLPA.DROP_ANALYSIS_TASK('TASK2');
　　
end;
　　

　　当然一条SQ闹这么大动静是不值得的，用dba_hist视图就可以对比出来了，不用这么烦神；一个schema下的所有sql这个就比较劳神了，用spa可以很好的解决这个问题,流程如下：
　　**1.在生产上捕获工作负载
　　2.将SQL工作负载传输到一个测试系统
　　3.构建“更改前”性能数据
　　4.进行SQL重放，生成性能数据
　　5.对比，生成报告
　　具体实施步骤：
　　源端：oracle 10.2.0.1　,hr用户，权限：grant advisor,administer sql tuning set to hr;
　　１　找到awr对应的snapshot;
　　

begin　　dbms_workload_repository.create_snapshot();
　　
end;
　　
select * from dba_hist_snapshot where begin_interval_time>sysdate-1/24
　　

　　2  建立sqlset ，采集sql性能数据
　　--创建sqlset
　　

begin　　dbms_sqltune.create_sqlset('sql_1') ;
　　end;
　　

declare cur sys_refcursor;　　begin
　　open cur for
　　SELECT VALUE(P) FROM
　　TABLE(DBMS_SQLTUNE.select_workload_repository(49,50,q'[ parsing_schema_name in ('HR') ]')) p;
　　dbms_sqltune.load_sqlset(sqlset_name => 'sql_1',populate_cursor => cur);
　　close cur;
　　end;
　　/
　　

　　--查看sqlset集
　　select * from table(dbms_sqltune.select_sqlset('sql_1')) where buffer_gets>0;
　　--创建sqlset表
　　

begin　　dbms_sqltune.create_stgtab_sqlset(table_name => 'SPA_2',schema_name => 'HR');
　　end;
　　

　　--执行pack，将数据打包到表
　　

begin　　DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'upg_sql',
　　SQLSET_OWNER=>'HR',
　　staging_table_name => 'SPA_2',
　　staging_schema_owner => 'HR');
　　
end;
　　

　　--删除sqlset
　　

exec  dbms_sqltune.drop_sqlset('sql_1') ;　　

　　
select  sql_id,sql_text,buffer_gets from spa_2;
　　

　　
expdp hr/hr directory=exp dumpfile=spa.dmp logfile=spa.log tables=SPA_2
　　

　　传输到11g的服务器，目标端：oracle 11.2.0.4
　　impdp之后解压：
　　

begin　　DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name         => 'sql_1',
　　sqlset_owner        => 'HR',
　　replace             => TRUE,
　　staging_table_name  => 'SPA_2');
　　end;
　　

　　建立分析任务
　　

VAR aname varchar2(30);　　
EXEC :aname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(task_name => 'compares',sqlset_name =>'sql_1');
　　

　　
--执行源端10g分析
　　
begin
　　DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'compares',
　　execution_type => 'convert sqlset',
　　execution_name => 'first trial');
　　end;
　　
--执行目标端11分析
　　
begin
　　

　　DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'compares',
　　execution_type => 'test execute',
　　execution_name => 'second trial'
　　);
　　

　　
end;
　　

　　
--生成比较任务
　　
begin
　　DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'compares',
　　execution_type => 'compare',
　　execution_name => 'compare_1'
　　);
　　end;
　　--生成报告
　　spool reports.html
　　SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('compares', 'html', 'TYPICAL', 'ALL') from dual;
　　

　　删除任务
　　

begin　　DBMS_SQLPA.DROP_ANALYSIS_TASK('compares');
　　end;
　　


　　相关包说明参考：Oracle® Database PL/SQL Packages and Types Reference 11g>　　1 相关错误

　　ERROR at line 1:
　　ORA-13757: "SQL Tuning Set" "sql_1" owned by user "HR" is active.
　　说明sql_1正在被使用，找到被引用的分析任务
　　

select description, created, owner　　
from DBA_SQLSET_REFERENCES
　　
where sqlset_name ='sql_1';
　　
DESCRIPTION                                 CREATED   OWNER
　　
--------------------------------------------------------------------------------------------- --------- ------------------------------
　　
created by: SQL Performance Analyzer - task: compare_1                20-JUL-18 HR
　　

　　

select owner,description, created,last_modified　　from DBA_ADVISOR_TASKS
　　where task_name = 'compare_1';
　　

　　删除即可：
　　

execute dbms_sqltune.drop_tuning_task('compare_1');　　
begin
　　dbms_sqltune.drop_sqlset('sql_1');
　　
end;