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;

oracle rat spa

相關文章