ORACLE 11G 使用SPM來(lái)調(diào)整SQL語(yǔ)句的執(zhí)行過(guò)程

這篇文章將為大家詳細(xì)講解有關(guān)ORACLE 11G 使用SPM來(lái)調(diào)整SQL語(yǔ)句的執(zhí)行過(guò)程，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

跟大家說(shuō)明一下：
 ITSM 數(shù)據(jù)庫(kù)遷移升級(jí)到11G后，有幾條SQL語(yǔ)句的執(zhí)行計(jì)劃不正確，而且這些語(yǔ)句都是使用綁定變量的。
最初的調(diào)整想法是獲得這些的語(yǔ)句的綁定變量值，將獲得的字面值直接替換SQL語(yǔ)句的綁定變量，調(diào)整該SQL到正確的執(zhí)行計(jì)劃后執(zhí)行，取得正確的執(zhí)行計(jì)劃并導(dǎo)入SPM。然而實(shí)際調(diào)整時(shí)發(fā)現(xiàn)，使用字面量獲得的執(zhí)行計(jì)劃雖然可以正常導(dǎo)入到SPM，但是無(wú)法被相應(yīng)的SQL語(yǔ)句使用，SQL語(yǔ)句仍然使用錯(cuò)誤的計(jì)劃執(zhí)行查詢。后面調(diào)整時(shí)，與實(shí)際生產(chǎn)時(shí)使用SQL語(yǔ)句方式一致，使用綁定變量的方式來(lái)執(zhí)行調(diào)整后SQL語(yǔ)句，然后將獲得計(jì)劃導(dǎo)入SPM,發(fā)現(xiàn)語(yǔ)句可以使用SPM中的正確計(jì)劃了。

ORACLE 11G 使用SPM來(lái)調(diào)整SQL語(yǔ)句的執(zhí)行

1)獲得執(zhí)行計(jì)劃錯(cuò)誤的SQL語(yǔ)句的SQL_ID,并當(dāng)前將壞的執(zhí)行計(jì)劃裝載到SPM里：
variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => '&SQL_ID', PLAN_HASH_VALUE => &HASH_VALUE) ;
檢查SPM，確認(rèn)相關(guān)的SQL計(jì)劃已經(jīng)被裝載到SPM。LOAD進(jìn)來(lái)的一般是最新的：
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)調(diào)整SQL語(yǔ)句，如增加新的hint，確認(rèn)獲得好的正確的執(zhí)行計(jì)劃。執(zhí)行一下調(diào)整后的語(yǔ)句，取得SQL_ID和Plan hash value:
select sql_id,plan_hash_value from v$sql where sql_text like '%/*+ test2-nbh INDEX(demand_state_alias%';

注意：對(duì)于綁定變量的SQL，最好也使用綁定變量的方式來(lái)獲得正確的執(zhí)行計(jì)劃，如果使用字面量，執(zhí)行計(jì)劃雖然被裝載，但可能無(wú)法被SQL語(yǔ)句使用。同時(shí)可以在SQL語(yǔ)句增加一些特別的提示，以容易獲得修改后的語(yǔ)句，如上面的查詢?cè)黾觮est2-nbh這樣一個(gè)標(biāo)識(shí)。

3)將正確的執(zhí)行計(jì)劃裝載到SPM,準(zhǔn)備用來(lái)替換錯(cuò)誤的執(zhí)行計(jì)劃：
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 –這個(gè)從步驟2中查詢獲得
Plan hash value: 751013780  –這個(gè)從步驟2中查詢獲得
SQL_HANDLE
SQL_4079a044d6e19677 --這個(gè)sql_handle是步驟1生成來(lái)的sql_handle

4)驗(yàn)證SPM執(zhí)行計(jì)劃是否正確
select * from dba_sql_plan_baselines where CREATED>sysdate-1/48 order by created;
--SQL_HANDLE為SQL_4079a044d6e19677的SPM記錄有兩個(gè)，可以通過(guò)時(shí)間的先后順序來(lái)確定哪一個(gè)是
好的執(zhí)行計(jì)劃，也可以通過(guò)以下方 式：
select * from
table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('&sql_handle','&PLAN_NAME');
--這里的sql_handle和PLAN_NAME來(lái)自步驟1生成的

5)驗(yàn)證了那個(gè)是錯(cuò)誤的執(zhí)行計(jì)劃之后，將壞的執(zhí)行計(jì)劃從SPM里邊刪除
variable cnt number ;
exec :cnt :=dbms_spm.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=> '&SQL_HANDLE', PLAN_NAME=> '&PLAN_NAME')

5)重新執(zhí)行語(yǔ)句

6)檢查語(yǔ)句執(zhí)行計(jì)劃是否正常
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'));

關(guān)于ORACLE 11G 使用SPM來(lái)調(diào)整SQL語(yǔ)句的執(zhí)行過(guò)程就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。