ORACLE 11G 使用SPM來調整SQL語句的執行過程

這篇文章將為大家詳細講解有關ORACLE 11G 使用SPM來調整SQL語句的執行過程，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

跟大家說明一下：
 ITSM 數據庫遷移升級到11G后，有幾條SQL語句的執行計劃不正確，而且這些語句都是使用綁定變量的。
最初的調整想法是獲得這些的語句的綁定變量值，將獲得的字面值直接替換SQL語句的綁定變量，調整該SQL到正確的執行計劃后執行，取得正確的執行計劃并導入SPM。然而實際調整時發現，使用字面量獲得的執行計劃雖然可以正常導入到SPM，但是無法被相應的SQL語句使用，SQL語句仍然使用錯誤的計劃執行查詢。后面調整時，與實際生產時使用SQL語句方式一致，使用綁定變量的方式來執行調整后SQL語句，然后將獲得計劃導入SPM,發現語句可以使用SPM中的正確計劃了。

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'));

關于ORACLE 11G 使用SPM來調整SQL語句的執行過程就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。