使用Outline固定执行计划
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/03/use_outline.html
链接：http://www.dbaroad.me/archives/2009/03/use_outline.html
检查数据库时发现，60几个会话都在执行一条长时间语句，并且都集中在实例1上，迟迟不能结束：
SELECT T7.CONFLICT_ID, ...... ...... FROM USER_N.T_TAB1_INT T1, USER_N.T_TABLE T2, USER_N.T_TABLE_ITEM T3, USER_N.T_TABLE T4, USER_N.T_TAB1_CFGVER T5, USER_N.T_TABLE_ITEM_OM T6, USER_N.T_TABLE_ITEM T7 WHERE T7.ROOT_ORDER_ITEM_ID = T3.ROW_ID(+) AND T7.ORDER_ID = T4.ROW_ID AND T3.PROD_ID = T5.PRODUCT_ID(+) AND T5.CURR_VER_FLG(+) = 'Y' AND T7.PROD_ID = T1.ROW_ID(+) AND T7.ROW_ID = T6.PAR_ROW_ID(+) AND T7.ORDER_ID = T2.ROW_ID(+) AND ((T7.PAR_ORDER_ITEM_ID IS NULL) AND (T7.PROD_ID IS NULL OR T5.CURR_VER_FLG = 'Y' OR T5.CURR_VER_FLG IS NULL OR T5.RELEASED_FLG = 'N' AND T5.VERSION_NUM = :1)) AND (T7.STATUS_CD = :2) AND (T4.ACCNT_ID = :3); |
查看语句的执行计划，发现实例1与实例2不同，查看历史执行计划信息，却发现是相同的，看来执行计划是刚刚发生了变化，估计跟昨晚代码更新，做过一些DDL操作有关。
实例1上的执行计划：
[Execution Plan Information] ------------------------------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | ------------------------------------------------------------------------------------------------------- |000[000]SELECT STATEMENT |---- 1126790038.2 ---- | | | 7 | |001[001]NESTED LOOPS OUTER | | 1 | 353 | 7 | |002[002] NESTED LOOPS | | 1 | 335 | 6 | |003[003] NESTED LOOPS OUTER | | 1 | 288 | 5 | |004[004] FILTER | | | | | |005[005] NESTED LOOPS OUTER | | | | | |006[006] NESTED LOOPS OUTER | | 1 | 206 | 3 | |007[007] NESTED LOOPS OUTER | | 1 | 188 | 2 | |008[008] TABLE ACCESS BY INDEX ROWID |T_TABLE_ITEM | 1 | 124 | 1 | |009[009] INDEX RANGE SCAN |CTZJ_ORDER_ITEM_F11_X | 1 | | 4 | |010[008] TABLE ACCESS BY INDEX ROWID |T_TAB1_INT | 1 | 64 | 1 | |011[009] INDEX UNIQUE SCAN |T_TAB1_INT_P1 | 1 | | | |012[007] TABLE ACCESS BY INDEX ROWID |T_TABLE_ITEM | 1 | 18 | 1 | |013[008] INDEX UNIQUE SCAN |T_TABLE_ITEM_P1 | 1 | | 2 | |014[006] TABLE ACCESS BY INDEX ROWID |T_TAB1_CFGVER | 1 | 20 | 1 | |015[007] INDEX RANGE SCAN |TA_T_TAB1_CFGVER_HOTFIX1_IDX_X | 1 | | | |016[004] TABLE ACCESS BY INDEX ROWID |T_TABLE_ITEM_OM | 1 | 62 | 1 | |017[005] INDEX RANGE SCAN |T_TABLE_ITEM_OM_U1 | 1 | | 3 | |018[003] TABLE ACCESS BY INDEX ROWID |T_TABLE | 1 | 47 | 1 | |019[004] INDEX UNIQUE SCAN |T_TABLE_P1 | 1 | | 1 | |020[002] TABLE ACCESS BY INDEX ROWID |T_TABLE | 1 | 18 | 1 | |021[003] INDEX UNIQUE SCAN |T_TABLE_P1 | 1 | | 1 | ------------------------------------------------------------------------------------- [Predicate Information] --------------------------------------------------------------- 4 filter:"T7"."PROD_ID" IS NULL OR "T5"."CURR_VER_FLG"='Y' OR "T5"."CURR _VER_FLG" IS NULL OR "T5"."RELEASED_FLG"='N' AND "T5"."VERSION_NUM"=:1 8 filter:"T7"."PAR_ORDER_ITEM_ID" IS NULL 9 access:"T7"."STATUS_CD"=:2 11 access:"T7"."PROD_ID"="T1"."ROW_ID"(+) 13 access:"T7"."ROOT_ORDER_ITEM_ID"="T3"."ROW_ID"(+) 15 access:"T3"."PROD_ID"="T5"."PRODUCT_ID"(+) AND "T5"."CURR_VER_FLG"(+)= 'Y' 17 access:"T7"."ROW_ID"="T6"."PAR_ROW_ID"(+) 18 filter:"T4"."ACCNT_ID"=:3 19 access:"T7"."ORDER_ID"="T4"."ROW_ID" 21 access:"T7"."ORDER_ID"="T2"."ROW_ID"(+) |
实例2上的执行计划：
[Execution Plan Information] ------------------------------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | ------------------------------------------------------------------------------------------------------- |000[000]SELECT STATEMENT |---- 1126790038.1 ---- | | | 7 | |001[001]NESTED LOOPS OUTER | | 10 | 3K| 7 | |002[002] NESTED LOOPS OUTER | | 10 | 3K| 6 | |003[003] FILTER | | | | | |004[004] NESTED LOOPS OUTER | | | | | |005[005] NESTED LOOPS OUTER | | 10 | 2K| 4 | |006[006] NESTED LOOPS OUTER | | 10 | 2K| 3 | |007[007] NESTED LOOPS | | 10 | 1K| 2 | |008[008] TABLE ACCESS BY INDEX ROWID |T_TABLE | 10 | 470 | 1 | |009[009] INDEX RANGE SCAN |T_TABLE_F1 | 125 | | 3 | |010[008] TABLE ACCESS BY INDEX ROWID |T_TABLE_ITEM | 1 | 124 | 1 | |011[009] INDEX RANGE SCAN |T_TABLE_ITEM_U1 | 11 | | 3 | |012[007] TABLE ACCESS BY INDEX ROWID |T_TAB1_INT | 1 | 64 | 1 | |013[008] INDEX UNIQUE SCAN |T_TAB1_INT_P1 | 1 | | | |014[006] TABLE ACCESS BY INDEX ROWID |T_TABLE_ITEM | 1 | 18 | 1 | |015[007] INDEX UNIQUE SCAN |T_TABLE_ITEM_P1 | 1 | | 2 | |016[005] TABLE ACCESS BY INDEX ROWID |T_TAB1_CFGVER | 1 | 20 | 1 | |017[006] INDEX RANGE SCAN |TA_T_TAB1_CFGVER_HOTFIX1_IDX_X | 1 | | | |018[003] TABLE ACCESS BY INDEX ROWID |T_TABLE_ITEM_OM | 1 | 62 | 1 | |019[004] INDEX RANGE SCAN |T_TABLE_ITEM_OM_U1 | 1 | | 3 | |020[002] TABLE ACCESS BY INDEX ROWID |T_TABLE | 1 | 18 | 1 | |021[003] INDEX UNIQUE SCAN |T_TABLE_P1 | 1 | | 1 | ------------------------------------------------------------------------------------- [Predicate Information] --------------------------------------------------------------- 3 filter:"T7"."PROD_ID" IS NULL OR "T5"."CURR_VER_FLG"='Y' OR "T5"."CURR _VER_FLG" IS NULL OR "T5"."RELEASED_FLG"='N' AND "T5"."VERSION_NUM"=:1 9 access:"T4"."ACCNT_ID"=:3 10 filter:"T7"."PAR_ORDER_ITEM_ID" IS NULL AND "T7"."STATUS_CD"=:2 11 access:"T7"."ORDER_ID"="T4"."ROW_ID" 13 access:"T7"."PROD_ID"="T1"."ROW_ID"(+) 15 access:"T7"."ROOT_ORDER_ITEM_ID"="T3"."ROW_ID"(+) 17 access:"T3"."PROD_ID"="T5"."PRODUCT_ID"(+) AND "T5"."CURR_VER_FLG"(+)= 'Y' 19 access:"T7"."ROW_ID"="T6"."PAR_ROW_ID"(+) 21 access:"T7"."ORDER_ID"="T2"."ROW_ID"(+) |
尝试使用Outline固定执行计划，使实例1的执行计划与实例2相同。
1、使用原语句建Outline
Create outline OL1126790038_ORIG for category CATEGORY_LONG on SELECT T7.CONFLICT_ID, ...... ...... |
2、查看Outline执行计划
SQL> r 1 select HINT_TEXT,USER_TABLE_NAME,JOIN_PRED,cardinality,bytes,cost 2 from OUTLN.OL$HINTS 3 where ol_name = 'OL1126790038_ORIG' 4 AND hint_text <> 'NO_EXPAND' 5 AND hint_text NOT LIKE 'PQ_DIS%' 6 AND hint_text NOT LIKE 'NO_FAC%' 7 AND hint_text NOT LIKE 'NOREWRITE' 8* order by node#, stage#, table_pos HINT_TEXT USER_TABLE_NAME JOIN_PRED CARDINALITY BYTES COST ------------------------------ ------------------------- -------------------- ----------- ---------- ---------- USE_NL(T1) USER_N.T_TAB1_INT T7.PROD_ID = T1.ROW_ 141 49773 29 USE_NL(T2) USER_N.T_TABLE T7.ORDER_ID = T2.ROW 134 38726 28 USE_NL(T5) USER_N.T_TAB1_CFGVER T3.PROD_ID = T5.PROD 0 0 0 USE_NL(T3) USER_N.T_TABLE_ITEM T7.ROOT_ORDER_ITEM_I 134 33634 24 USE_NL(T6) USER_N.T_TABLE_ITEM_OM T7.ROW_ID = T6.PAR_R 134 31222 20 USE_NL(T7) USER_N.T_TABLE_ITEM T7.ORDER_ID = T4.ROW 134 22914 14 ORDERED 0 0 0 INDEX(T4 T_TABLE_F1) USER_N.T_TABLE 124 5828 2 INDEX(T7 T_TABLE_ITEM_U1) USER_N.T_TABLE_ITEM 1 124 1 INDEX(T6 T_TABLE_ITEM_OM_U1) USER_N.T_TABLE_ITEM_OM 1 62 1 INDEX(T3 T_TABLE_ITEM_P1) USER_N.T_TABLE_ITEM 1 18 1 INDEX(T5 TA_T_TAB1_CFGVER_HOTF USER_N.T_TAB1_CFGVER 1 20 1 INDEX(T2 T_TABLE_P1) USER_N.T_TABLE 1 18 1 INDEX(T1 T_TAB1_INT_P1) USER_N.T_TAB1_INT 1 64 1 |
发现与实例1、实例2上的都不同。没关系，接下来做第三步。
(注：如果可以得到与实例2上相同的执行计划，则可直接进入最后一步：启用Outline)
3、在SQL上加HINT使语句执行计划与实例2上相同：
Create outline OL1126790038_MOD for category CATEGORY_LONG on SELECT /*+ ORDERED */ T7.CONFLICT_ID, ...... ...... FROM USER_N.T_TABLE T4, USER_N.T_TABLE_ITEM T7, USER_N.T_TAB1_INT T1, USER_N.T_TABLE_ITEM T3, USER_N.T_TAB1_CFGVER T5, USER_N.T_TABLE_ITEM_OM T6, USER_N.T_TABLE T2 WHERE ...... ...... SQL> select HINT_TEXT,USER_TABLE_NAME,JOIN_PRED,cardinality,bytes,cost 2 from OUTLN.OL$HINTS 3 where ol_name = 'OL1126790038_MOD' 4 AND hint_text <> 'NO_EXPAND' 5 AND hint_text NOT LIKE 'PQ_DIS%' 6 AND hint_text NOT LIKE 'NO_FAC%' 7 AND hint_text NOT LIKE 'NOREWRITE' 8 order by node#, stage#, table_pos; HINT_TEXT USER_TABLE_NAME JOIN_PRED CARDINALITY BYTES COST ------------------------------ ------------------------- -------------------- ----------- ---------- ---------- USE_NL(T2) USER_N.T_TABLE T7.ORDER_ID = T2.ROW 141 49773 30 USE_NL(T6) USER_N.T_TABLE_ITEM_OM T7.ROW_ID = T6.PAR_R 141 47235 27 USE_NL(T5) USER_N.T_TAB1_CFGVER T3.PROD_ID = T5.PROD 0 0 0 USE_NL(T3) USER_N.T_TABLE_ITEM T7.ROOT_ORDER_ITEM_I 141 35673 20 USE_NL(T1) USER_N.T_TAB1_INT T7.PROD_ID = T1.ROW_ 141 33135 16 USE_NL(T7) USER_N.T_TABLE_ITEM T7.ORDER_ID = T4.ROW 134 22914 14 ORDERED 0 0 0 INDEX(T4 T_TABLE_F1) USER_N.T_TABLE 124 5828 2 INDEX(T7 T_TABLE_ITEM_U1) USER_N.T_TABLE_ITEM 1 124 1 INDEX(T1 T_TAB1_INT_P1) USER_N.T_TAB1_INT 1 64 1 INDEX(T3 T_TABLE_ITEM_P1) USER_N.T_TABLE_ITEM 1 18 1 INDEX(T5 TA_T_TAB1_CFGVER_HOTF USER_N.T_TAB1_CFGVER 1 20 1 INDEX(T6 T_TABLE_ITEM_OM_U1) USER_N.T_TABLE_ITEM_OM 1 62 1 INDEX(T2 T_TABLE_P1) USER_N.T_TABLE 1 18 1 |
4、得到与实例2相同的执行计划后，将两个Outline的执行计划进行互换：
UPDATE OUTLN.OL$HINTS SET OL_NAME = DECODE(OL_NAME, 'OL1126790038_MOD', 'OL1126790038_ORIG', 'OL1126790038_ORIG', 'OL1126790038_MOD') WHERE OL_NAME IN ('OL1126790038_MOD', 'OL1126790038_ORIG'); |
5、启用Outline
确认OL1126790038_ORIG使用的执行计划与实例2相同后，就可以启用Outline了。
这里需要注意的是，如果是第一次应用Outline，需要执行：
ALTER SYSTEM SET USE_STORED_OUTLINES=CATEGORY_LONG; |
这个操作是会产生Library cache pin的，需谨慎。
如果系统之前已经启过OUTLINE，那么，只需要将OL1126790038_ORIG的CATEGORY更换为当前在使用的CATEGORY：
alter outline OL1126790038_ORIG change category to CATEGORY_CUR; |
该操作不会产生Library cache pin的。
另外，要查看系统当前在使用哪个CATEGORY，可以查查v$sql中的OUTLINE_CATEGORY，而dba_outlines中的USED字段，是不能做为依据的
— The End —
关键字: SQL优化 | 性能优化
小子写的不错,有前途!
Reply