ORACLE 11G 使用SPM來調整SQL語句的執行計劃

跟大家說明一下：
 ITSM 資料庫遷移升級到11G後，有幾條SQL語句的執行計劃不正確，而且這些語句都是使用繫結變數的。
最初的調整想法是獲得這些的語句的繫結變數值，將獲得的字面值直接替換SQL語句的繫結變數，調整該SQL到正確的執行計劃後執行，取得正確的執行計劃並匯入SPM。然而實際調整時發現，使用字面量獲得的執行計劃雖然可以正常匯入到SPM，但是無法被相應的SQL語句使用，SQL語句仍然使用錯誤的計劃執行查詢。後面調整時，與實際生產時使用SQL語句方式一致，使用繫結變數的方式來執行調整後SQL語句，然後將獲得計劃匯入SPM,發現語句可以使用SPM中的正確計劃了。
具體的操作步驟可參看第一封郵件。

發件人: 張思明(Simon Cheung)
傳送時間: 2011年10月18日 19:16
收件人: 聶寶紅; ML_平安科技資料庫技術支援部
主題: RE: ORACLE 11G 使用SPM來調整SQL語句的執行計劃

聶寶紅，要你發這個電郵是希望其他DBA能從你這次的問題處理上吸取經驗。你這個電郵沒有前文後理，沒有背影介紹，只會讓大家看得一頭霧水。

From: 聶寶紅
Sent: 2011年10月18日 19:00
To: ML_平安科技資料庫技術支援部
Subject: ORACLE 11G 使用SPM來調整SQL語句的執行計劃

1)獲得執行計劃錯誤的SQL語句的SQL_ID,並當前將壞的執行計劃裝載到SPM裡：
variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => '&SQL_ID', PLAN_HASH_VALUE => &HASH_VALUE) ;
檢查SPM，確認相關的SQL計劃已經被裝載到SPM。LOAD進來的一般是最新的：
select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT from dba_SQL_PLAN_BASELINES where ACCEPTED = 'YES'
order by LAST_MODIFIED；
SQL_HANDLE PLAN_NAME
-------------------------------------------------------------
SQL_4079a044d6e19677 SQL_PLAN_40yd08mbfffddfdw555d8

2)調整SQL語句，如增加新的hint，確認獲得好的正確的執行計劃。執行一下調整後的語句，取得SQL_ID和Plan hash value:
select sql_id,plan_hash_value from v$sql where sql_text like '%/*+ test2-nbh INDEX(demand_state_alias%';

注意：對於繫結變數的SQL，最好也使用繫結變數的方式來獲得正確的執行計劃，如果使用字面量，執行計劃雖然被裝載，但可能無法被SQL語句使用。同時可以在SQL語句增加一些特別的提示，以容易獲得修改後的語句，如上面的查詢增加test2-nbh這樣一個標識。

3)將正確的執行計劃裝載到SPM,準備用來替換錯誤的執行計劃：
variable cnt number ;
exec :cnt :=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '&SQL_ID',PLAN_HASH_VALUE => &plan_hash_value,SQL_HANDLE => '&SQL_HANDLE' ) ;
SQL_ID: dzfky5zdzc231 –這個從步驟2中查詢獲得
Plan hash value: 751013780  –這個從步驟2中查詢獲得
SQL_HANDLE
SQL_4079a044d6e19677 --這個sql_handle是步驟1生成來的sql_handle

4)驗證SPM執行計劃是否正確
select * from dba_sql_plan_baselines where CREATED>sysdate-1/48 order by created;
--SQL_HANDLE為SQL_4079a044d6e19677的SPM記錄有兩個，可以透過時間的先後順序來確定哪一個是
好的執行計劃，也可以透過以下方 式：
select * from
table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('&sql_handle','&PLAN_NAME');
--這裡的sql_handle和PLAN_NAME來自步驟1生成的

5)驗證了那個是錯誤的執行計劃之後，將壞的執行計劃從SPM裡邊刪除
variable cnt number ;
exec :cnt :=dbms_spm.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=> '&SQL_HANDLE', PLAN_NAME=> '&PLAN_NAME')

5)重新執行語句

6)檢查語句執行計劃是否正常
select
EXECUTIONS,PLAN_HASH_VALUE,ELAPSED_TIME/1000000,ELAPSED_TIME/1000000/EXECUTIONS,LAST_ACTIVE_TIME,ROWS_PROCESSED
from v$sql where EXECUTIONS>0 and sql_id='&sql'; select * from table(dbms_xplan.display_cursor('&sql'));