rat的两个主要组件database replay和sql performance analyzer(SPA),其中SPA来预测任何更改对sql工作负载的影响,以主动发现升级问题.
spa可用于数据库和应用升级,硬件更改,操作系统更改,初始化参数更改,sql调整活动,模式更改.
spa基本步骤是sql调整集capture,测试更改前的sql性能,进行系统更改,测试更改后的sql性能,对比产生报告.
以下是一个简单的测试:
db1:ct6604
db2:ct6604sb
1.创建sql调整集
--ct6604
begin
dbms_sqltune.create_sqlset(sqlset_name => 'upgrade_set',
description=>'11g upgrade workload');
end;
2.装载sql调整集
--ct6604
declare
mycur dbms_sqltune.sqlset_cursor;
begin
open mycur for
select value(P) from table(dbms_sqltune.select_cursor_cache(
'parsing_schema_name<> ''SYS'' and elapsed_time > 2500000',
null,null,null,null,1,null,'ALL')) P;
dbms_sqltune.load_sqlset(sqlset_name=>'upgrade_set',populate_cursor => mycur);
end;
3.创建sts中转表,将sts导入
--ct6604
begin
dbms_sqltune.create_stgtab_sqlset(table_name=>'stagetab');
end;
begin
dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'upgrade_set',staging_table_name => 'stagetab');
end;
4.导出sts表
--ct6604
[oracle@ct6604 ~]$expdp system/system directory=HOME_DUMP tables=system."stagetab" logfile=stagetab.log dumpfile=expdat.dmp
5.传输sts表
--ct6604
[oracle@ct6604 ~]$scp expdat.dmp 192.108.56.120:~/
6.将sts导入测试库
--ct6604sb
begin
dbms_sqltune.unpack_stgtab_sqlset(
replace=>true,staging_table_name=>'stagetab'
);
end;
7.创建sts调整任务
var sname varchar2(30);
exec :sname:=dbms_sqlpa.create_analysis_task(sqlset_name=>'upgrade_set',
task_name=>'spa_task1');
8.分析更改前的sql工作负载
--ct6604sb
alter system set optimizer_features_enable='10.2.0.4';
begin
dbms_sqlpa.execute_analysis_task(task_name=>'spa_task1',execution_type => 'test execute',execution_name=>'before_change');
end;
9.分析更改后的sql工作负载
--ct6604sb
alter system set optimizer_features_enable='11.2.0.4';
begin
dbms_sqlpa.execute_analysis_task(task_name=>'spa_task1',
execution_type => 'test execute',
execution_name=>'after_change');
end;
10.比较sql性能
--ct6604sb
begin
dbms_sqlpa.execute_analysis_task(task_name=>'spa_task1',execution_type=>'compare performance',execution_params => dbms_advisor.argList('comparison_metric','disk_reads'));
end;
11.生产报告
--ct6604sb
select dbms_sqlpa.report_analysis_task('spa_task1','text','typical','summary') from dual;
spa可用于数据库和应用升级,硬件更改,操作系统更改,初始化参数更改,sql调整活动,模式更改.
spa基本步骤是sql调整集capture,测试更改前的sql性能,进行系统更改,测试更改后的sql性能,对比产生报告.
以下是一个简单的测试:
db1:ct6604
db2:ct6604sb
1.创建sql调整集
--ct6604
begin
dbms_sqltune.create_sqlset(sqlset_name => 'upgrade_set',
description=>'11g upgrade workload');
end;
2.装载sql调整集
--ct6604
declare
mycur dbms_sqltune.sqlset_cursor;
begin
open mycur for
select value(P) from table(dbms_sqltune.select_cursor_cache(
'parsing_schema_name<> ''SYS'' and elapsed_time > 2500000',
null,null,null,null,1,null,'ALL')) P;
dbms_sqltune.load_sqlset(sqlset_name=>'upgrade_set',populate_cursor => mycur);
end;
3.创建sts中转表,将sts导入
--ct6604
begin
dbms_sqltune.create_stgtab_sqlset(table_name=>'stagetab');
end;
begin
dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'upgrade_set',staging_table_name => 'stagetab');
end;
4.导出sts表
--ct6604
[oracle@ct6604 ~]$expdp system/system directory=HOME_DUMP tables=system."stagetab" logfile=stagetab.log dumpfile=expdat.dmp
5.传输sts表
--ct6604
[oracle@ct6604 ~]$scp expdat.dmp 192.108.56.120:~/
6.将sts导入测试库
--ct6604sb
begin
dbms_sqltune.unpack_stgtab_sqlset(
replace=>true,staging_table_name=>'stagetab'
);
end;
7.创建sts调整任务
var sname varchar2(30);
exec :sname:=dbms_sqlpa.create_analysis_task(sqlset_name=>'upgrade_set',
task_name=>'spa_task1');
8.分析更改前的sql工作负载
--ct6604sb
alter system set optimizer_features_enable='10.2.0.4';
begin
dbms_sqlpa.execute_analysis_task(task_name=>'spa_task1',execution_type => 'test execute',execution_name=>'before_change');
end;
9.分析更改后的sql工作负载
--ct6604sb
alter system set optimizer_features_enable='11.2.0.4';
begin
dbms_sqlpa.execute_analysis_task(task_name=>'spa_task1',
execution_type => 'test execute',
execution_name=>'after_change');
end;
10.比较sql性能
--ct6604sb
begin
dbms_sqlpa.execute_analysis_task(task_name=>'spa_task1',execution_type=>'compare performance',execution_params => dbms_advisor.argList('comparison_metric','disk_reads'));
end;
11.生产报告
--ct6604sb
select dbms_sqlpa.report_analysis_task('spa_task1','text','typical','summary') from dual;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-1811442/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28539951/viewspace-1811442/