ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用Outline固定执行计划

使用Outline固定执行计划

原创 Linux操作系统 作者:fengjin821 时间:2009-06-09 13:33:06 0 删除 编辑

检查数据库时发现,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> 
  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 —

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13165828/viewspace-605638/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-29

  • 博文量
    191
  • 访问量
    505041