一次SQL Performance Analyzer的使用過程

資料庫版本升級、變更系統引數前使用SQL Performance Analyzer可以衡量升級前後、引數變更前後SQL語句的執行效能是否有變化，以及這些變化對於整體效能的影響程度，對於效能惡化的sql語句結合SQL Tuning Advisor可是進一步實現調優，確保系統效能在升級或者引數變更後依然維持穩定。關於SPA的詳細介紹可以參考” Real Application Testing User's Guide”。本文對於如何實施SPA的完整過程做了一個演示，大家如果要用到SPA可以直接往裡套，不用去看手冊上的繁瑣介紹。

SPA核心步驟有三步：變更前執行SQL->變更後執行SQL--&gt變更前後的效能比對，為了模擬出SQL語句效能下降進而影響系統整體效能的效果，演示中將按照如下過程進行：”1.變更前執行SQL--&gt2.drop掉SQL所訪問的某張表上的索引來模擬變更的動作--&gt3.變更後執行SQL--&gt4.變更前後效能比對(此時訪問這張表的SQL效能會有明顯下降)--&gt5.使用SQL Tuning Advisor調優--&gt6.再次作效能比對(此時SQL效能恢復到變更前狀態)

--將生產庫awr中的負荷load到sql tuning set裡

declare

begin

DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name=>'chhsts1',description=>'chhsts1',sqlset_owner=>'SYSTEM');

end;

/

--加了commit_rows引數之後可以在load時觀察DBA_SQLSET的statement_count欄位瞭解load進度

DECLARE

type chhtype is ref cursor;

cur chhtype;

BEGIN

OPEN cur FOR SELECT VALUE(P) FROM table(dbms_sqltune.select_workload_repository(begin_snap=>17740,end_snap=>17741,recursive_sql=>dbms_sqltune.NO_RECURSIVE_SQL)) P;

dbms_sqltune.load_sqlset(sqlset_name=>'chhsts1',populate_cursor=>cur,commit_rows=>2);

CLOSE cur;

END;

/

--檢視sqlset內容，可以使用下面任何語句

select value(p) from table(dbms_sqltune.select_sqlset(sqlset_name=>'chhsts1')) p;

select * from dba_sqlset_statements where sqlset_name='chhsts1';

--生產庫上將STS內容export到CHHSTS1_TAB表

declare

begin

DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name=>'CHHSTS1_TAB',schema_name=>'SYSTEM',tablespace_name=>'TS_PUB');

end;

/

declare

begin

dbms_sqltune.pack_stgtab_sqlset(sqlset_name=>'chhsts1',sqlset_owner=>'SYSTEM',staging_table_name=>'CHHSTS1_TAB',staging_schema_owner=>'SYSTEM');

end;

/

--傳輸chhsts1_tab表到測試資料庫，將表內容匯入到測試庫的STS，測試庫上的資料必須儘可能和生產庫一致，我環境裡的測試庫資料是通過儲存底層複製的方式從生產庫拷貝而來，所以測試庫和生產庫上的資料完全相同，省去了將生產庫資料匯出至測試庫的步驟

**生產庫expdp

expdp system/shzw_2013 tables=chhsts1_tab directory=hisdmp logfile=exp_chhsts1_tab.log dumpfile=chhsts1_tab.dmp

**測試庫impdp

impdp system/abcd_1234 directory=hisdmp logfile=imp_chhsts1_tab.log dumpfile=chhsts1_tab.dmp

**從表中將sql語句匯入測試庫的STS

begin

dbms_sqltune.UNPACK_STGTAB_SQLSET(sqlset_name=>'chhsts1',sqlset_owner=>'SYSTEM',replace=>FALSE,staging_table_name=>'CHHSTS1_TAB',staging_schema_owner=>'SYSTEM');

end;

/

--測試庫上建立analysis task，名稱chhtask1

set serveroutput on

declare

v_taskname varchar2(1000);

begin

v_taskname:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'chhsts1',task_name => 'chhtask1');

dbms_output.put_line(v_taskname);

end;

/

--測試庫上create pre-change sql trial，設定超時時間20分鐘，不應用session中設定的環境變數，這裡為節省執行時間，只針對特定的SQL語句生成pre-change sql trial即用basic_filter引數限制只針對CA_BNK_ZDZ_DS_PAY_REC、CM_RES_LIFECYCLE兩張表的訪問語句進行優化

**為了觀察analyze task的效果先清空shared pool，實際大家做的時候不需要這一步

alter system flush shared_pool;

declare

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'chhtask1',execution_type=>'TEST EXECUTE',execution_name=>'chhexec_pre1',execution_params=>dbms_advisor.arglist('APPLY_CAPTURED_COMPILENV', 'NO','TIME_LIMIT','1200','basic_filter','sql_text like ''%select t.* from ad.CA_BNK_ZDZ_DS_PAY_REC%'' or sql_text like ''%CM_RES_LIFECYCLE%'''));

end;

/

**執行完後從shared pool裡看到了以/* SQL Analyze(5663,0) */開頭的語句，這些都是SQLPA發起的，5663代表執行execute_analysis_task的sessionid可以看到每條語句的執行次數都為10次，第1次執行oracle稱為warm buffer，也就是排除掉物理磁碟因素對SQL語句執行的干擾，真正計入統計的是後面9次的執行效能平均值

select sql_text,executions from v$sql where sql_text like '%CA_BNK_ZDZ_DS_PAY_REC%' or sql_text like '%CM_RES_LIFECYCLE%';

SQL_TEXT EXECUTIONS

/* SQL Analyze(5663,0) */ select t.* from ad.CA_BNK_ZDZ_DS_PAY_REC t where t.TRANSACTION_ID=:1 and t.ACTION_DATE=:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_0 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_1 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_10 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_11 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_12 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_13 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_14 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_15 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_16 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_17 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_18 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_19 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_2 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_20 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_21 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_22 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_23 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_24 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_25 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_26 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_27 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_28 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_29 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_3 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_30 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_31 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_32 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_33 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_34 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_35 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_36 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_37 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_38 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_39 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_4 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_40 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_41 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_42 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_43 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_44 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_45 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_46 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_47 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_48 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_49 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_5 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_6 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_7 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_8 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_9 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10

--測試庫上執行變更，為模擬實現變更後sql語句執行效能下降的效果，刪除掉ad.CA_BNK_ZDZ_DS_PAY_REC表欄位transaction_id上的索引

drop index ad.INX_CA_BNK_ZDZ_DS_PAY_REC_1;

--測試庫上create post-change sql trial，設定超時時間20分鐘，不應用session中設定的環境變數，同樣利用basic_filter只針對CA_BNK_ZDZ_DS_PAY_REC、CM_RES_LIFECYCLE這兩套表進行分析

alter system flush shared_pool;

declare

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'chhtask1',execution_type=>'TEST EXECUTE',execution_name=>'chhexec_post1',execution_params=>dbms_advisor.arglist('APPLY_CAPTURED_COMPILENV', 'NO','TIME_LIMIT','1200','basic_filter','sql_text like ''%select t.* from ad.CA_BNK_ZDZ_DS_PAY_REC%'' or sql_text like ''%CM_RES_LIFECYCLE%'''));

end;

/

--再次查詢post_change sql trial時SQLPA執行的語句，發現對於CA_BNK_ZDZ_DS_PAY_REC表的語句執行次數只有兩次，這是因為表上的索引被drop，語句採用的是FTS執行計劃，執行2次共用了42秒，對於這類耗時長的語句oracle最多執行2次，第1次也是起到warm作用，體現在報告中的應該是第2次的效能值

select sql_text,executions from v$sql where sql_text like '%CA_BNK_ZDZ_DS_PAY_REC%' or sql_text like '%CM_RES_LIFECYCLE%';

SQL_TEXT EXECUTIONS ELAPSED_TIME

/* SQL Analyze(5663,0) */ select t.* from ad.CA_BNK_ZDZ_DS_PAY_REC t where t.TRANSACTION_ID=:1 and t.ACTION_DATE=:2 2 42796162

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_0 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6507

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_1 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6261

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_10 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 2095

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_11 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6513

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_12 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 2986

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_13 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6113

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_14 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6320

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_15 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6451

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_16 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6236

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_17 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6554

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_18 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6350

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_19 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6497

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_2 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6658

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_20 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6808

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_21 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6344

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_22 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 2069

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_23 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6280

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_24 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 9791

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_25 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 2560

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_26 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6221

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_27 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6219

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_28 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6398

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_29 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6314

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_3 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6579

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_30 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6549

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_31 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6203

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_32 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 7464

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_33 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6522

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_34 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6321

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_35 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6236

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_36 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6225

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_37 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6455

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_38 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 7576

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_39 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6273

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_4 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6463

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_40 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6286

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_41 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6522

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_42 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6284

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_43 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6734

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_44 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6463

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_45 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6571

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_46 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6511

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_47 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 5889

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_48 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6297

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_49 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6310

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_5 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6242

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_6 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6537

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_7 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6240

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_8 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6480

/* SQL Analyze(5663,0) */ select t.* from CD.CM_RES_LIFECYCLE_9 t where t.RESOURCE_ID=:1 and t.EXPIRE_DATE>:2 10 6323

--基於前面兩次取樣結果，執行效能比較任務

declare

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'chhtask1',execution_type => 'COMPARE PERFORMANCE', execution_name => 'chhexec_comp1', execution_params =>dbms_advisor.arglist('EXECUTION_NAME1','chhexec_pre1','EXECUTION_NAME2','chhexec_post1'));

end;

/

--測試庫上執行結果比對生成報告，報告支援text、html、xml、active四種方式

***生成報告格式為HTML

set serveroutput on

set heading off

set pagesize 2000

set long 20000

spool /home/oracle/spa_chhexec_comp1.html

select DBMS_SQLPA.REPORT_ANALYSIS_TASK('chhtask1','HTML','ALL','ALL') from dual;

spool off

**報告首部的概要部分：各次執行開始結束時間、比較的指標預設是elapsed_time，效能影響超過1%的情況才會體現在報告中

***後面緊接著的是報告中的量化資料，就是這部分資料反應了效能變好or變差，負值表示變差，impact on workload表明這條sql語句對於整體效能的影響，impact on sql表明語句本身的效能變化情況，plan change=y表示執行計劃發生了變化

***生成報告格式為ACTIVE，這種格式的報告是圖形介面的，看效果有點像OEM，它在資料庫關閉時依然能檢視，前提是開啟html檔案的機器必須能連線到http://download.oracle.com/otn_software才能動態載入內容

set serveroutput on

set heading off

set pagesize 2000

set long 20000

spool /home/oracle/spa_chhexec_comp1_active.html

select DBMS_SQLPA.REPORT_ANALYSIS_TASK('chhtask1','ACTIVE','ALL','ALL') from dual;

spool off

***ACTIVE格式報告介面如下

--閱讀報告，對於有問題的sql語句sql_id=f7qmfbcgqjy0p進行下鑽分析，object_id=153是從report summary中獲得的，生成html格式的報告

set serveroutput on

set heading off

set pagesize 2000

set long 20000

spool /home/oracle/spa_chhexec_comp1_detail.html

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'chhtask1',type=>'HTML',object_id =>153) from dual;

spool off

--報告中顯示了這條語句的各項效能引數及其對整體效能的影響

--tune regressed sql，針對spa結果中效能下降的語句建立調優任務

declare

v_result varchar2(1000);

begin

v_result:=DBMS_SQLTUNE.CREATE_TUNING_TASK(spa_task_name => 'chhtask1',spa_task_owner => 'SYSTEM',task_name=>'tune_regre1',spa_compare_exec => 'chhexec_comp1');

end;

/

--生成優化建議

declare

begin

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tune_regre1');

end;

/

--通過dba_advisor_系列檢視瞭解優化建議

**Sql Tuning Advisor推薦的調優方法

col task_name format a20

set linesize 120

select task_name,finding_id,type from dba_advisor_recommendations where task_name='tune_regre1';

TASK_NAME FINDING_ID TYPE

-------------------- ---------- ------------------------------

tune_regre1 1 PARALLEL EXECUTION

tune_regre1 2 INDEX

tune_regre1 3 ALTERNATE PLAN

**實現調優所要執行的操作，oracle推薦使用並行執行的sql profile來提高語句的執行效率，也推薦使用sql Access Advisor來為CA_BNK_ZDZ_DS_PAY_REC表選擇合適的索引，我們當然選擇後者

col attr1 format a10

col attr2 format a10

col attr3 format a10

col message format a50

col command format a30

set linesize 160

set pagesize 200

select task_name,execution_name,command,attr1,attr2,attr3,message from DBA_ADVISOR_actions where task_name='tune_regre1';

TASK_NAME EXECUTION_NAME COMMAND ATTR1 ATTR2 ATTR3 MESSAGE

---------- -------------------- ------------------------------ ---------- ---------- ---------- --------------------------------------------------

tune_regre EXEC_139802 ACCEPT SQL PROFILE tune_regre 2 PARALLEL E Consider accepting the recommended SQL profile to

1 1 XECUTION use parallel execution for this statement.

tune_regre EXEC_139802 CREATE INDEX AD.IDX$$_2 AD.CA_BNK_ Consider running the Access Advisor to improve the

1 F3230001 ZDZ_DS_PAY physical schema design or creating the recommende

_REC d index.

tune_regre EXEC_139802 UNDEFINED All alternative plans other than the Original Plan

1 could not be reproduced in the current environment

--下面來生成實現上述調優操作所需的命令

set long 2000

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('tune_regre1','ALL') FROM DUAL;

DBMS_SQLTUNE.SCRIPT_TUNING_TASK('TUNE_REGRE1','ALL')

--------------------------------------------------------------------------------

-----------------------------------------------------------------

-- Script generated by DBMS_SQLTUNE package, advisor framework --

-- Use this script to implement some of the recommendations --

-- made by the SQL tuning advisor. --

-- --

-- NOTE: this script may need to be edited for your system --

-- (index names, privileges, etc) before it is executed. --

-----------------------------------------------------------------

create index AD.IDX$$_2F3230001 on AD.CA_BNK_ZDZ_DS_PAY_REC("TRANSACTION_ID","ACTION_DATE");

--重新建上索引

create index AD.IDX$$_2F3230001 on AD.CA_BNK_ZDZ_DS_PAY_REC("TRANSACTION_ID","ACTION_DATE");

--第三次進行SQL語句的執行取樣

declare

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'chhtask1',execution_type=>'TEST EXECUTE',execution_name=>'chhexec_post2',execution_params=>dbms_advisor.arglist('APPLY_CAPTURED_COMPILENV', 'NO','TIME_LIMIT','1200','basic_filter','sql_text like ''%select t.* from ad.CA_BNK_ZDZ_DS_PAY_REC%'' or sql_text like ''%CM_RES_LIFECYCLE%'''));

end;

/

declare

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'chhtask1',execution_name=>'chhcomp_comp1',execution_type=>'compare performance',execution_params=>dbms_advisor.arglist('EXECUTION_NAME1','chhexec_pre1','EXECUTION_NAME2','chhexec_post2','WORKLOAD_IMPACT_THRESHOLD',0,'SQL_IMPACT_THRESHOLD',0));

end;

/

--第二次生成比較報告

set long 20000

set pagesize 2000

spool /home/oracle/spa_2nd_comp.html

select DBMS_SQLPA.REPORT_ANALYSIS_TASK('chhtask1','HTML','ALL','ALL') from dual;

spool off

--效能問題解決了

--最後附上一些關於task的有用的檢視

**dba_advisor_executions包含了task明細

select task_name,advisor_name,execution_type from dba_advisor_tasks;

TASK_NAME ADVISOR_NAME EXECUTION_TYPE

-------------------- ------------------------------ --------------------

chhtask1 SQL Performance Analyzer COMPARE PERFORMANCE

tune_regre1 SQL Tuning Advisor TUNE SQL

**dba_advisor_executions包含了task裡每次執行的任務狀態

alter session set nls_date_format='yyyymmdd hh24:mi:ss';

col task_name format a10

col execution_name format a20

col execution_type format a20

set linesize 140

select task_name,execution_name,execution_type,execution_start,execution_end,advisor_name,status from dba_advisor_executions

TASK_NAME EXECUTION_NAME EXECUTION_TYPE EXECUTION_START EXECUTION_END ADVISOR_NAME STATUS

---------- -------------------- -------------------- ----------------- ----------------- ------------------------------ -----------

chhtask1 chhexec_post1 TEST EXECUTE 20141016 14:27:17 20141016 14:27:22 SQL Performance Analyzer COMPLETED

chhtask1 chhexec_pre1 TEST EXECUTE 20141016 13:44:11 20141016 13:44:47 SQL Performance Analyzer COMPLETED

**dba_advisor_log包含了每個任務的執行日誌記錄，當前進度等

col task_name format a30

select task_name,execution_start,execution_end,status from dba_advisor_log

TASK_NAME EXECUTION_START EXECUTION_END STATUS

------------------------------ ----------------- ----------------- -----------

chhtask1 20141016 19:51:27 20141016 19:51:28 COMPLETED

tune_regre1 20141016 19:59:11 20141016 20:01:41 COMPLETED

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1304240/，如需轉載，請註明出處，否則將追究法律責任。

一次SQL Performance Analyzer的使用過程

相關文章