【問題分類】性能優化，功能使用

【關鍵字】outline

【問題描述】防止SQL執行計劃突變，用outline固化執行計劃

【問題原因分析】防止SQL執行計劃突變，用outline固化執行計劃

【解決/規避方法】

SQL--創建測試outline：ol_ab/ol_baconn sales/salesCREATE OUTLINE ol_ab FOR CATEGORY ctgy_ab ONSELECT /*+ leading(a,b) */ a.area_name, b.branch_nameFROM area a, branches bWHERE a.area_no = b.area_noAND b.branch_no LIKE '01%'AND a.area_no LIKE '01';CREATE OUTLINE ol_ba FOR CATEGORY ctgy_ab ONSELECT a.area_name, b.branch_nameFROM area a, branches bWHERE a.area_no = b.area_noAND b.branch_no LIKE '01%'AND a.area_no LIKE '01';--修改基表，交換outlineconn / as sysdbaUPDATE OL$HINTS SET OL_NAME=DECODE(OL_NAME,'OL_AB','OL_BA','OL_BA','OL_AB') WHERE OL_NAME IN ('OL_AB','OL_BA');--打開outline開關ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;--驗證應用SQL執行計劃conn sales/salesexplain SELECT a.area_name, b.branch_nameFROM area a, branches bWHERE a.area_no = b.area_noAND b.branch_no LIKE '01%'AND a.area_no LIKE '01';

【影響范圍】嚴重降低SQL執行效率

【修復版本】22.2