oracle 8:hint
oracle 8i&9: stored outline
oracle 10: sql profile
oracle 11: sql plan manangement
rbo->cbo 由規則序列生成執行計劃,向着智能靈活化發展,根據資料對象的統計資訊進行執行計劃生成,根據cost選擇最優。
但是cbo依賴統計資訊,統計量又不能和資料表資料完全同步,當統計資訊與實際資料差距大就會導緻錯誤執行計劃生成。
(1)沒有定期收集統計量的方案,使得統計量過舊;
(2)當系統更新移植,版本不一緻使得導入的對應統計量錯誤;
(3)在夜間剛剛收集完統計量之後調用大作業,導入海量資料直接改變了原有的資料分布情況。第二天直接生成錯誤的執行計劃。
plan capture 捕獲
存儲sql對應plan的相關資訊。這些資訊可以唯一确定一個plan。
plan selection 選擇
在系統的運作時,oracle要保證每次執行sql的執行計劃都是使用sql baseline中的确定執行計劃。同時,跟蹤所有該statement執行中生成的新執行計劃,作為plan histroy資訊儲存下來。
plan evolution 進化
添加新的plan到sql baselines中,自動或手動。
通過使用baseline,減少性能回退。可以為新應用生成baselines,逐漸演化更好的plan。
首先把optimizer_capture_sql_plan_baselines設定成true
從這個時刻開始,所有執行兩次以上的sql語句會被觀測,執行計劃會進入plan history。
生成的第一個執行計劃被标記為enabled并且是accepted,後續的執行計劃會被标記為enabled但不是accepted。
這時把optimizer_capture_sql_plan_baselines設定會false,新的語句将不會建立baseline。
需要注意的是,即使關閉了自動捕捉,針對存在baseline的sql,仍舊會有新的plan生成,新的plan仍會進入plan history,标記為enabled但不是accepted。
oralce提供四種方式把計劃導入到sql plan baseline中。
從 sql tuning set sts 導dbms_spm.load_plans_from_sqlset
從stored outlines 中導入dbms_spm.migrate_stored_outline
從記憶體中存在的計劃中導入dbms_spm.load_plans_from_cursor_cache;
通過staging table從另外一個系統中移植
(這些導入的baseline都會被自動标記為accepted)
optimizer_use_sql_plan_baselines被設定成預設值true,sql plan baseline就會起作用。
過程:
首先,無論是否存在baseline,oracle都會正常進行硬解析或者軟解析,為sql生成一個執行計劃。 由于acs和bind peeking的作用,存在baseline的sql有可能在這時生成一個不同于baseline的執行計劃。
如果baseline不存在,就按生成的計劃執行。如果baseline存在,那麼要檢視history裡是否有這個計劃,如果沒有,就将這個計劃插入,并标記為enabled,non-accepted。
在baseline中檢視是否有fixed的計劃存在,如果存在,執行fixed的計劃,如果存在多個fixed的計劃,根據統計資訊重新計算cost,選擇cost小的那個。
如果fixed的計劃不存在,就選擇accepted的計劃執行。 如果存在多個accepted的計劃,根據統計資訊重新計算cost,選擇cost小的那個。
注意這裡每次重新計算cost的代價不大,因為執行計劃是已知的,優化器不必周遊所有的可能,隻需根據算法計算出已知計劃的cost便可。
執行計劃的演化指plan history裡的執行計劃從non-accepted,變成accepted的過程。 如果上所述,由于acs和bind peeking的作用,存在baseline的sql有可能生成新的執行計劃,被儲存到plan history中。 oracle提供了api,通過自動或手工的方式,将一個計劃标記為accepted,這個計劃就會被後續的執行所選擇。有自動和手動兩種方式,見後面的示範。