10G database 執行

14:35:34 SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'RAC38N1',description => 'SPA TEST');

PL/SQL procedure successfully completed

Executed in 0.109 seconds

將快照ID 20660,20661 的SQL 放到SQLSET

14:46:04 SQL> declare cur sys_refcursor;

2 begin

3 open cur for

4 SELECT VALUE(P) FROM

5 TABLE(DBMS_SQLTUNE.select_workload_repository(20660,20661)) p;

6 dbms_sqltune.load_sqlset(sqlset_name => 'RAC38N1',populate_cursor => cur);

7 close cur;

8 end;

9 /

PL/SQL procedure successfully completed

Executed in 0.546 seconds

--也可以用V$SQL中的符合條件的SQL_TEXT建立SQLSET

-- dbms_sqltune.select_cursor_cache('sql_text like ''select * from mytab%''',

null,null,null,null,null,null,'ALL')) P;

--也可以用V$SQL中的符合條件的SQL_ID建立SQLSET

-- dbms_sqltune.select_cursor_cache('sql_text = ''YOUR SQL_ID''',

null,null,null,null,null,null,'ALL')) P;

檢視SQLSET中的內容

select * from table(dbms_sqltune.select_sqlset('RAC38N1'));

......result

將10G環境的SQLSET傳輸到 11G 環境

--transporting

首先在10G中建立儲存SQLSET 的表

14:46:06 SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => 'SPA_38N1',schema_name => 'PAYADM');

PL/SQL procedure successfully completed

Executed in 0.438 seconds

將SQLSET的內容PACK到表中

14:51:24 SQL> EXEC DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'RAC38N1',

staging_table_name => 'SPA_38N1',

staging_schema_owner => 'PAYADM');

PL/SQL procedure successfully completed

Executed in 1.047 seconds

11G database 執行

使用EXPDP或者DBLINK的方式將儲存SQLSET內容的表結構內容傳到11G資料庫55.52

執行下面語句將SQLSET 載入到11G SQLSET

15:04:43 SQL> EXEC DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'RAC38N1',

replace => TRUE,

staging_table_name => 'SPA_38N1',

staging_schema_owner => 'PAYADM');

PL/SQL procedure successfully completed

Executed in 0.422 seconds

建立analysis_task RAC38N1

DECLARE V_SPA_NAME VARCHAR2(100);

BEGIN

V_SPA_NAME:=spa_dbms_xhl.create_analysis_task(sqlset_name => 'RAC38N1',task_name => 'spa_38_task');

dbms_output.put_line('spa_name='||v_spa_name);

end;

/

如果沒有安裝real application testing 元件將報下面錯誤

ORA-00438: 未安裝 Real Application Testing 選件

ORA-06512: 在 "SYS.PRVT_SMGUTIL", line 80

ORA-06512: 在 "SYS.DBMS_SQLPA", line 220

ORA-06512: 在 line 4

shutdown 資料庫執行$ORACLE_HOME/oui/runInstaller 新增此元件

9:38:20 SQL> DECLARE V_SPA_NAME VARCHAR2(100);

2 BEGIN

3 V_SPA_NAME:=dbms_sqlpa.create_analysis_task(sqlset_name => 'RAC38N1',task_name => 'spa_38_task');

4 dbms_output.put_line('spa_name='||v_spa_name);

5 end;

6 /

PL/SQL procedure successfully completed

Executed in 0.578 seconds

生成10G 的trail

9:42:35 SQL> exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',

execution_type => 'CONVERT SQLSET',

execution_name => 'SPA_3810G');

PL/SQL procedure successfully completed

生成11G 的trail

9:43:57 SQL> exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',

execution_type => 'TEST EXECUTE',

execution_name => 'SPA_5211G');

PL/SQL procedure successfully completed

Executed in 57.484 seconds

開始比較

9:54:54 SQL>

elapsed_time 比較

exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',execution_type => 'COMPARE PERFORMANCE',

execution_name => 'compare_elp_time',

execution_params => dbms_advisor.argList('comparison_metric','elapsed_time'));

buffer_gets 比較

exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',execution_type => 'COMPARE PERFORMANCE', execution_name => 'compare_buff_gets', execution_params => dbms_advisor.argList('comparison_metric','buffer_gets'));

PL/SQL procedure successfully completed

Executed in 0.532 seconds

生成比較報告

select dbms_sqlpa.report_analysis_task('spa_38_task','HTML','ALL','ALL') FROM DUAL

10G遷移升級到11G使用SPA 分析SQL效能例項

相關文章