[20120803]11G SPM的學習1.txt

[20120803]11G SPM的學習1.txt

    開始學習SQL Plan Management(SPM) ,11G開始提供SPM，在10g下我經常使用sql profile看一些bad sql語句，sql profile我覺得已經做的很好，

有時候能夠提供很好的建議。我開始學習SPM的時候感覺不習慣（也許是因為在toad下使用sql profile很簡單）為什麼oracle還有搞出SPM來，慢

慢看資料，才明白其中一些細節。

    我看過别人在從8i更新到9i的時候，出現性能波動，裡面的一些sql語句執行計劃發生了變化。我自己也遇到從8i更新到10g時出現sql語句性能問

題，10g下的自動分析導緻建立了一些字段建立不必要的直方圖。

    SPM對于sql執行計劃的穩定起到很好的作用，對于更新和穩定sql語句起到很好的作用。

    通過一些例子來學習，我的測試環境如下：

1.SPM設定與這兩個參數有關：

以上兩個參數是預設設定，如果設定optimizer_capture_sql_plan_baselines=true,可以讓oracle自動捕獲與建立sql plan baseline。

一般預設設定是false。

optimizer_use_sql_plan_baselines=true,當查詢sql分析時，oracle産生幾個執行計劃,對比sql plan baseline是否存在，如果存在

，oracle直接選取該計劃。

建立測試表：

2.建立sql plan baseline:

也許有許多方法，一般方法如下：

方法1:

設定optimizer_capture_sql_plan_baselines＝true

alter session set optimizer_capture_sql_plan_baselines＝true;

執行sql語句兩次以上！

alter session set optimizer_capture_sql_plan_baselines＝false;

方法2:

使用dbms_spm包的load_plans_from_cursor_cache函數導入，我測試使用該方法。

--知道sql_id=3yxwagyspybax,plan_hash_value=1601196873

--我一直有一個疑問？如果通過這個查詢到sql_id. 直接查詢sql_text當然可以。

--可以發現EXACT_MATCHING_SIGNATURE，FORCE_MATCHING_SIGNATURE與SIGNATURE相等。而SQL_PLAN_BASELINE為NULL。

--執行select * from t where id=:x;2次，每次後通路v$sql視圖看看！

--可以發現sql_id=3yxwagyspybax産生了子光标,v$sql.SQL_PLAN_BASELINE與dba_sql_plan_baselines.PLAN_NAME相等。

--可以通過這些資訊,查詢相關資訊。

3.改動sql語句看看。（把select改為Select看看);

這樣可以通過v$sql的SQL_PLAN_BASELINE與EXACT_MATCHING_SIGNATURE與dba_sql_plan_baselines的PLAN_NAME和SIGNATURE建立聯系。

再改動sql語句，使用文字變量看看。

--大家可以通過這些例子了解EXACT_MATCHING_SIGNATURE，FORCE_MATCHING_SIGNATURE。有點離題了,^_^。

--FORCE_MATCHING_SIGNATURE視乎是轉成了綁定變量來對比，并且忽略sql語句大小寫。這些知識自己不是很懂，跳過.

4.建立索引:

--可以發現由于sql plan baseline存在，執行計劃依舊使用全表掃描.

--查詢dba_sql_plan_baselines可以發現多了1條記錄,即使我們設定optimizer_capture_sql_plan_baselines=FALSE,

--oracle可以捕獲新的計劃和baseline,隻不過accepted=no,也就是執行不采用該計劃。

--通過origin字段，也可以看到增加的記錄是AUTO-CAPTURE。

--檢視SQL_HANDLE=SYS_SQL_a45a9e109f85e5a4, PLAN_NAME=SQL_PLAN_a8qny22gsbtd40893a4b2的執行計劃可以使用

--為了後面能操作比較快，我定義如下變量：

5.修改sql plan baseline:

--奇怪，沒有ACCEPTED這個屬性。

--   attribute_name   - One of the following possible attribute names:

--                      'ENABLED',

--                      'FIXED',

--                      'AUTOPURGE',

--                      'PLAN_NAME',

--                      'DESCRIPTION'

--   attribute_value  - If the attribute name denotes a plan status then

--                      the legal values are: 'YES', 'NO'.

--                      If the attribute name denotes a plan name then the

--                      supplied value should not conflict with already

--                      stored plan name.

--                      If the attribute name denotes plan description then

--                      any character string is allowed.

-- 也就是不能通過DBMS_SPM.ALTER_SQL_PLAN_BASELINE來修改ACCEPTED屬性？

--我看一些blog，屬性ACCEPTED修改為NO,這樣操作是可以的。不知道是否是版本的問題。

連結如下:https://forums.oracle.com/forums/thread.jspa?messageID=9470036

From 11R2 onwards to accept the plan the script. has changed but to unaccept we have to change the enable status to 'NO'

so that the plan is not in use and therefore we have suggested the script. which will make the plan enable as 'NO' and

thus the plan will become unusable.

If the ACCEPTED=YES and ENABLE=NO,then the plan will not be used as for the plan to be used the ENABLE has to 'YES',in

11R1 we were able to change the status of ACCEPTED to 'NO' and 'YES' but from 11R2 we can change the plan status of

ACCEPTED from 'NO' to 'YES'.

    Therefore we requested you to change the enable status to 'NO' for which we have provided the script.

    Here "verify" indicates that it will not do any performance review and plan will be accepted forcefully,if we set this

as 'YES' then it will do the performance review and then only will accept the plan.

    For accepting the plan the "commit" has to be 'YES'.

    var pbsts varcahr2(1000)

    exec :pbsts := dbms_spm.evolve_sql_plan_baseline('SYS_SQL_7de69bb90f3e54d2','SQL_PLAN_7vtnvr47mwp6k54bc8843',verify=> 'NO', commit=>'YES');

要将屬性ACCEPTED修改為YES,僅僅這樣操作。

--可以發現oracle使用新的執行計劃，plan_name=SQL_PLAN_a8qny22gsbtd40893a4b2 .

--還可以使用另外的方法：

--exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => :v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ACCEPTED',attribute_value => 'NO');

--使用這個執行計劃

--可以發現plan_name=SQL_PLAN_a8qny22gsbtd40893a4b2,ORIGIN=MANUAL-LOAD.

--可以發現執行計劃使用索引掃描。