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_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;
1 找到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 Release 2 (11.2)--》dbms_spa/dbms_sqltune
总结
以上是编程之家为你收集整理的oracle spa全部内容,希望文章能够帮你解决oracle spa所遇到的程序开发问题。
如果觉得编程之家网站内容还不错,欢迎将编程之家网站推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。