首页 > 代码库 > 【故障处理141119】一次数据库不繁忙时一条sql语句2个运行计划导致业务超时的故障处理

【故障处理141119】一次数据库不繁忙时一条sql语句2个运行计划导致业务超时的故障处理


1,故障描写叙述:

一条select有两个运行计划。在sqlplus中运行选择好的运行计划。仅仅要40毫秒。而在程序中运行选择了差的运行计划,要1分23秒左右,导致前台业务超时报错。

2。故障解决:

使用outline固定好的运行计划后攻克了该故障。


3,故障发展顺序:

(1),早上一上班,说CRM的一个业务报错,crm应用开发者、接口的、tuxdo、dba集中到一起開始诊断错误。

(2),业务返回超时错误

(3),数据库这边抓取AWR报告发现例如以下信息:

技术分享

技术分享

技术分享


技术分享


技术分享

技术分享

(4),此时应用开发者也发过来了该条sql说业务会调用到这条sql。

(5),于是在sqlplus中运行了该sql,发现运行时间非常快,逻辑读也非常低运行计划也没有问题。

通过v$sql_bind_capture得到sql运行时的绑定变量值,在sqlplus中再次运行该sql
select trade_id,
       accept_month,
       user_id,
       t4.service_id,
       modify_tag,
       t4.start_date,
       t4.end_date,
       item_id,
       user_id_a,
       t4.package_id,
       t4.product_id,
       t1.product_name,
       t2.package_name,
       t3.service_name
  FROM uop_crm2.TD_B_PRODUCT t1,
       uop_crm2.TD_B_PACKAGE t2,
       uop_crm2.TD_B_SERVICE t3,
       (SELECT to_char(0) trade_id,
               0 accept_month,
               to_char(user_id) user_id,
               service_id,
               ‘A‘ modify_tag,
               to_char(start_date, ‘yyyy-mm-dd hh24:mi:ss‘) start_date,
               to_char(end_date, ‘yyyy-mm-dd hh24:mi:ss‘) end_date,
               to_char(ITEM_ID) ITEM_ID,
               to_char(USER_ID_A) USER_ID_A,
               PACKAGE_ID,
               PRODUCT_ID
          FROM uop_crm2.tf_f_user_svc a
         WHERE user_id = TO_NUMBER(‘3114042824225916‘)
           AND partition_id = MOD(TO_NUMBER(‘3114042824225916‘), 10000)
           AND end_date > sysdate
           AND NOT EXISTS
         (SELECT 1
                  FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade c
                 WHERE c.user_id = TO_NUMBER(‘3114042824225916‘)
                   AND c.cancel_tag = ‘0‘
                   AND c.accept_month = TO_NUMBER(‘11‘)
                   AND b.trade_id = c.trade_id
                   AND b.accept_month = c.accept_month
                   AND b.modify_tag in (‘1‘, ‘B‘)
                   AND b.product_id = a.product_id
                   AND b.package_id = a.package_id
                   AND b.service_id = a.service_id
                   AND b.start_date = a.start_date)
        UNION ALL
        SELECT to_char(d.TRADE_ID) TRADE_ID,
               d.ACCEPT_MONTH,
               to_char(d.USER_ID) USER_ID,
               SERVICE_ID,
               MODIFY_TAG,
               to_char(START_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) START_DATE,
               to_char(END_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) END_DATE,
               to_char(ITEM_ID) ITEM_ID,
               to_char(d.USER_ID_A) USER_ID_A,
               d.PACKAGE_ID,
               d.PRODUCT_ID
          FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e
         WHERE e.user_id = TO_NUMBER(‘3114042824225916‘)
           AND e.cancel_tag = ‘0‘
           AND e.accept_month = TO_NUMBER(‘11‘)
           AND d.trade_id = e.trade_id
           AND d.user_id = TO_NUMBER(‘3114042824225916‘)
           AND d.accept_month = TO_NUMBER(‘11‘)
           AND d.modify_tag in (‘0‘, ‘A‘)
           AND NOT EXISTS
         (SELECT 1
                  FROM uop_crm2.tf_b_trade_svc
                 WHERE trade_id = TO_NUMBER(‘3114111918985865‘)
                   AND accept_month = TO_NUMBER(‘11‘)
                   AND modify_tag in (‘1‘, ‘B‘)
                   AND product_id = d.product_id
                   AND package_id = d.package_id
                   AND service_id = d.service_id
                   AND start_date = d.start_date)
        UNION ALL
        SELECT to_char(d.TRADE_ID) TRADE_ID,
               d.accept_month H,
               to_char(d.USER_ID) USER_ID,
               SERVICE_ID,
               MODIFY_TAG,
               to_char(START_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) START_DATE,
               to_char(END_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) END_DATE,
               to_char(ITEM_ID) ITEM_ID,
               to_char(d.USER_ID_A) USER_ID_A,
               d.PACKAGE_ID,
               d.PRODUCT_ID
          FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e
         WHERE e.user_id = TO_NUMBER(‘3114042824225916‘)
           AND e.cancel_tag = ‘0‘
           AND e.accept_month = TO_NUMBER(‘11‘)
           AND d.trade_id = e.trade_id
           AND d.user_id = TO_NUMBER(‘3114042824225916‘)
           AND d.accept_month = TO_NUMBER(‘11‘)
           AND d.modify_tag in (‘1‘, ‘B‘)
           AND SYSDATE < d.end_date
           AND d.start_date < d.end_date) t4
 WHERE t1.product_id(+) = t4.product_id
   AND t2.package_id(+) = t4.package_id
   AND t3.service_id(+) = t4.service_id;
   
PACKAGE_NAME
----------------------------------------------------------------------------------------------------
SERVICE_NAME
----------------------------------------------------------------------------------------------------
基本业务功能包
呼叫保持


15 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1183257532

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                       |     3 |   903 |    38   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS OUTER                       |                       |     3 |   903 |    38   (0)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS OUTER                      |                       |     3 |   702 |    35   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS OUTER                     |                       |     3 |   498 |    32   (0)| 00:00:01 |       |       |
|   4 |     VIEW                                  |                       |     3 |   429 |    29   (0)| 00:00:01 |       |       |
|   5 |      UNION-ALL                            |                       |       |       |            |          |       |       |
|   6 |       NESTED LOOPS ANTI                   |                       |     1 |    69 |    11   (0)| 00:00:01 |       |       |
|   7 |        PARTITION RANGE SINGLE             |                       |     1 |    61 |     4   (0)| 00:00:01 |     6 |     6 |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID | TF_F_USER_SVC         |     1 |    61 |     4   (0)| 00:00:01 |     6 |     6 |
|*  9 |          INDEX RANGE SCAN                 | PK_TF_F_USER_SVC      |     1 |       |     3   (0)| 00:00:01 |     6 |     6 |
|  10 |        VIEW PUSHED PREDICATE              | VW_SQ_1               |     1 |     8 |     7   (0)| 00:00:01 |       |       |
|  11 |         NESTED LOOPS                      |                       |     1 |    65 |     7   (0)| 00:00:01 |       |       |
|* 12 |          TABLE ACCESS BY INDEX ROWID      | TF_B_TRADE            |     1 |    25 |     4   (0)| 00:00:01 |       |       |
|* 13 |           INDEX RANGE SCAN                | IDX_TF_B_TRADE_USERID |     1 |       |     3   (0)| 00:00:01 |       |       |
|  14 |          PARTITION RANGE SINGLE           |                       |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |
|* 15 |           INDEX RANGE SCAN                | PK_TF_B_TRADE_SVC     |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |
|  16 |       NESTED LOOPS ANTI                   |                       |     1 |   137 |    11   (0)| 00:00:01 |       |       |
|  17 |        NESTED LOOPS                       |                       |     1 |    97 |     8   (0)| 00:00:01 |       |       |
|* 18 |         TABLE ACCESS BY INDEX ROWID       | TF_B_TRADE            |     1 |    25 |     4   (0)| 00:00:01 |       |       |
|* 19 |          INDEX RANGE SCAN                 | IDX_TF_B_TRADE_USERID |     1 |       |     3   (0)| 00:00:01 |       |       |
|  20 |         PARTITION RANGE SINGLE            |                       |     1 |    72 |     4   (0)| 00:00:01 |    11 |    11 |
|  21 |          TABLE ACCESS BY LOCAL INDEX ROWID| TF_B_TRADE_SVC        |     1 |    72 |     4   (0)| 00:00:01 |    11 |    11 |
|* 22 |           INDEX RANGE SCAN                | PK_TF_B_TRADE_SVC     |     1 |       |     3   (0)| 00:00:01 |    11 |    11 |
|  23 |        PARTITION RANGE SINGLE             |                       |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |
|* 24 |         INDEX RANGE SCAN                  | PK_TF_B_TRADE_SVC     |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |
|  25 |       NESTED LOOPS                        |                       |     1 |    97 |     7   (0)| 00:00:01 |       |       |
|  26 |        NESTED LOOPS                       |                       |     1 |    97 |     7   (0)| 00:00:01 |       |       |
|  27 |         PARTITION RANGE SINGLE            |                       |     1 |    72 |     5   (0)| 00:00:01 |    11 |    11 |
|* 28 |          TABLE ACCESS BY LOCAL INDEX ROWID| TF_B_TRADE_SVC        |     1 |    72 |     5   (0)| 00:00:01 |    11 |    11 |
|* 29 |           INDEX RANGE SCAN                | IDX1_TF_B_TRADE_SVC   |     4 |       |     3   (0)| 00:00:01 |    11 |    11 |
|* 30 |         INDEX UNIQUE SCAN                 | PK_TF_B_TRADE         |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 31 |        TABLE ACCESS BY INDEX ROWID        | TF_B_TRADE            |     1 |    25 |     2   (0)| 00:00:01 |       |       |
|  32 |     TABLE ACCESS BY INDEX ROWID           | TD_B_SERVICE          |     1 |    23 |     1   (0)| 00:00:01 |       |       |
|* 33 |      INDEX UNIQUE SCAN                    | PK_TD_B_SERVICE       |     1 |       |     0   (0)| 00:00:01 |       |       |
|  34 |    TABLE ACCESS BY INDEX ROWID            | TD_B_PRODUCT          |     1 |    68 |     1   (0)| 00:00:01 |       |       |
|* 35 |     INDEX UNIQUE SCAN                     | PK_TD_B_PRODUCT       |     1 |       |     0   (0)| 00:00:01 |       |       |
|  36 |   TABLE ACCESS BY INDEX ROWID             | TD_B_PACKAGE          |     1 |    67 |     1   (0)| 00:00:01 |       |       |
|* 37 |    INDEX UNIQUE SCAN                      | PK_TD_B_PACKAGE       |     1 |       |     0   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - filter("END_DATE">SYSDATE@!)
   9 - access("USER_ID"=3114042824225916 AND "PARTITION_ID"=5916)
  12 - filter("C"."ACCEPT_MONTH"=11 AND "C"."CANCEL_TAG"=‘0‘)
  13 - access("C"."USER_ID"=3114042824225916)
  15 - access("B"."TRADE_ID"="C"."TRADE_ID" AND "B"."ACCEPT_MONTH"=11 AND "B"."SERVICE_ID"="A"."SERVICE_ID" AND
              "B"."START_DATE"="A"."START_DATE" AND "B"."PRODUCT_ID"="A"."PRODUCT_ID" AND "B"."PACKAGE_ID"="A"."PACKAGE_ID")
       filter("B"."START_DATE"="A"."START_DATE" AND "B"."SERVICE_ID"="A"."SERVICE_ID" AND
              "B"."PRODUCT_ID"="A"."PRODUCT_ID" AND "B"."PACKAGE_ID"="A"."PACKAGE_ID" AND ("B"."MODIFY_TAG"=‘1‘ OR "B"."MODIFY_TAG"=‘B‘))
  18 - filter("E"."ACCEPT_MONTH"=11 AND "E"."CANCEL_TAG"=‘0‘)
  19 - access("E"."USER_ID"=3114042824225916)
  22 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "D"."ACCEPT_MONTH"=11 AND "D"."USER_ID"=3114042824225916)
       filter("D"."MODIFY_TAG"=‘0‘ OR "D"."MODIFY_TAG"=‘A‘)
  24 - access("TRADE_ID"=3114111918985865 AND "ACCEPT_MONTH"=11 AND "SERVICE_ID"="D"."SERVICE_ID" AND
              "START_DATE"="D"."START_DATE" AND "PRODUCT_ID"="D"."PRODUCT_ID" AND "PACKAGE_ID"="D"."PACKAGE_ID")
       filter(("MODIFY_TAG"=‘1‘ OR "MODIFY_TAG"=‘B‘) AND "PRODUCT_ID"="D"."PRODUCT_ID" AND "PACKAGE_ID"="D"."PACKAGE_ID"
              AND "SERVICE_ID"="D"."SERVICE_ID" AND "START_DATE"="D"."START_DATE")
  28 - filter(("D"."MODIFY_TAG"=‘1‘ OR "D"."MODIFY_TAG"=‘B‘) AND "D"."START_DATE"<"D"."END_DATE" AND
              "D"."END_DATE">SYSDATE@! AND "D"."ACCEPT_MONTH"=11)
  29 - access("D"."USER_ID"=3114042824225916)
  30 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "E"."ACCEPT_MONTH"=11 AND "E"."CANCEL_TAG"=‘0‘)
  31 - filter("E"."USER_ID"=3114042824225916)
  33 - access("T3"."SERVICE_ID"(+)="T4"."SERVICE_ID")
  35 - access("T1"."PRODUCT_ID"(+)="T4"."PRODUCT_ID")
  37 - access("T2"."PACKAGE_ID"(+)="T4"."PACKAGE_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         82  consistent gets
          0  physical reads
          0  redo size
       2390  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL> 

(6),运行非常快,于是让应用开发者在该sql运行前后加一个时间输出到日志里面,看看sql究竟运行了多长时间,測试结果是58秒。

(7),此时就确认应该sql在sqlplus和程序里的运行计划不一样,于是考虑针对sql_id:1huatx9vws2u3做一个sqlrpt。

技术分享


(8),为了再现这个差的运行计划是否真的运行非常长时间。于是对该sqlid做了一个advanced的运行计划显示。并取出当中的差的Outline Data加入到sql里面然后再次运行。

SQL> select * from table(dbms_xplan.display_cursor(‘1huatx9vws2u3‘,null,‘advanced‘));

把plan hash value为353242268 outline data拿出来,outline data事实上就是个hint,加到select后面,再现差的运行计划的运行效率:

SELECT /*+                                                                                                                                                                                                                                     
      BEGIN_OUTLINE_DATA                                                                                                                                                                                                                  
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                                                                                         
      OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4‘)                                                                                                                                                                                               
      DB_VERSION(‘11.2.0.4‘)                                                                                                                                                                                                              
      ALL_ROWS                                                                                                                                                                                                                            
      OUTLINE_LEAF(@"SEL$2AD7F9D9")
      PUSH_PRED(@"SEL$B29E968D" "VW_SQ_1"@"SEL$E9784550" 7 6 5 4)                                                                                                                                                                         
      OUTLINE_LEAF(@"SEL$B29E968D")                                                                                                                                                                                                       
      UNNEST(@"SEL$3")                                                                                                                                                                                                                    
      OUTLINE_LEAF(@"SEL$385088EC")                                                                                                                                                                                                       
      UNNEST(@"SEL$5")                                                                                                                                                                                                                    
      OUTLINE_LEAF(@"SEL$6")                                                                                                                                                                                                              
      OUTLINE_LEAF(@"SET$1")                                                                                                                                                                                                              
      OUTLINE_LEAF(@"SEL$1")                                                                                                                                                                                                              
      OUTLINE(@"SEL$291F8F59")                                                                                                                                                                                                            
      OUTLINE(@"SEL$B29E968D")                                                                                                                                                                                                            
      UNNEST(@"SEL$3") 
      OUTLINE(@"SEL$E9784550")                                                                                                                                                                                                            
      OUTLINE(@"SEL$3")                                                                                                                                                                                                                   
      OUTLINE(@"SEL$4")                                                                                                                                                                                                                   
      OUTLINE(@"SEL$5")                                                                                                                                                                                                                   
      OUTLINE(@"SEL$2")                                                                                                                                                                                                                   
      NO_ACCESS(@"SEL$1" "T4"@"SEL$1")                                                                                                                                                                                                    
      INDEX_RS_ASC(@"SEL$1" "T3"@"SEL$1" ("TD_B_SERVICE"."SERVICE_ID"))                                                                                                                                                                   
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("TD_B_PRODUCT"."PRODUCT_ID"))                                                                                                                                                                   
      INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("TD_B_PACKAGE"."PACKAGE_ID"))                                                                                                                                                                   
      LEADING(@"SEL$1" "T4"@"SEL$1" "T3"@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")                                                                                                                                                               
      USE_NL(@"SEL$1" "T3"@"SEL$1")  
            USE_NL(@"SEL$1" "T1"@"SEL$1")                                                                                                                                                                                                       
      USE_NL(@"SEL$1" "T2"@"SEL$1")                                                                                                                                                                                                       
      INDEX_RS_ASC(@"SEL$6" "D"@"SEL$6" ("TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID"))                                                                                                                                       
      INDEX(@"SEL$6" "E"@"SEL$6" ("TF_B_TRADE"."TRADE_ID" "TF_B_TRADE"."ACCEPT_MONTH" "TF_B_TRADE"."CANCEL_TAG"))                                                                                                                         
      LEADING(@"SEL$6" "D"@"SEL$6" "E"@"SEL$6")                                                                                                                                                                                           
      USE_NL(@"SEL$6" "E"@"SEL$6")                                                                                                                                                                                                        
      NLJ_BATCHING(@"SEL$6" "E"@"SEL$6")                                                                                                                                                                                                  
      INDEX_RS_ASC(@"SEL$385088EC" "D"@"SEL$4" ("TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID"))                                                                                                                                
      INDEX(@"SEL$385088EC" "TF_B_TRADE_SVC"@"SEL$5" ("TF_B_TRADE_SVC"."TRADE_ID" "TF_B_TRADE_SVC"."ACCEPT_MONTH"                                                                                                                         
              "TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID" "TF_B_TRADE_SVC"."START_DATE" "TF_B_TRADE_SVC"."PRODUCT_ID"                                                                                                        
              "TF_B_TRADE_SVC"."PACKAGE_ID" "TF_B_TRADE_SVC"."USER_ID_A" "TF_B_TRADE_SVC"."MODIFY_TAG"))   
                    INDEX(@"SEL$385088EC" "E"@"SEL$4" ("TF_B_TRADE"."TRADE_ID" "TF_B_TRADE"."ACCEPT_MONTH" "TF_B_TRADE"."CANCEL_TAG"))                                                                                                                  
      LEADING(@"SEL$385088EC" "D"@"SEL$4" "TF_B_TRADE_SVC"@"SEL$5" "E"@"SEL$4")                                                                                                                                                           
      USE_NL(@"SEL$385088EC" "TF_B_TRADE_SVC"@"SEL$5")                                                                                                                                                                                    
      USE_NL(@"SEL$385088EC" "E"@"SEL$4")                                                                                                                                                                                                 
      NLJ_BATCHING(@"SEL$385088EC" "E"@"SEL$4")                                                                                                                                                                                           
      INDEX_RS_ASC(@"SEL$B29E968D" "A"@"SEL$2" ("TF_F_USER_SVC"."USER_ID" "TF_F_USER_SVC"."PARTITION_ID"                                                                                                                                  
              "TF_F_USER_SVC"."SERVICE_ID" "TF_F_USER_SVC"."START_DATE"))                                                                                                                                                                 
      NO_ACCESS(@"SEL$B29E968D" "VW_SQ_1"@"SEL$E9784550")                                                                                                                                                                                 
      LEADING(@"SEL$B29E968D" "A"@"SEL$2" "VW_SQ_1"@"SEL$E9784550")                                                                                                                                                                       
      USE_NL(@"SEL$B29E968D" "VW_SQ_1"@"SEL$E9784550")                                                                                                                                                                                    
      INDEX_SS(@"SEL$2AD7F9D9" "B"@"SEL$3" ("TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID"))  
            INDEX(@"SEL$2AD7F9D9" "C"@"SEL$3" ("TF_B_TRADE"."TRADE_ID" "TF_B_TRADE"."ACCEPT_MONTH" "TF_B_TRADE"."CANCEL_TAG"))                                                                                                                  
      LEADING(@"SEL$2AD7F9D9" "B"@"SEL$3" "C"@"SEL$3")                                                                                                                                                                                    
      USE_NL(@"SEL$2AD7F9D9" "C"@"SEL$3")                                                                                                                                                                                                 
      NLJ_BATCHING(@"SEL$2AD7F9D9" "C"@"SEL$3")                                                                                                                                                                                           
      END_OUTLINE_DATA                                                                                                                                                                                                                    
  */   trade_id,
       accept_month,
       user_id,
       t4.service_id,
       modify_tag,
       t4.start_date,
       t4.end_date,
       item_id,
       user_id_a,
       t4.package_id,
       t4.product_id,
       t1.product_name,
       t2.package_name,
       t3.service_name
  FROM uop_crm2.TD_B_PRODUCT t1,
       uop_crm2.TD_B_PACKAGE t2,
       uop_crm2.TD_B_SERVICE t3,
       (SELECT to_char(0) trade_id,
               0 accept_month,
               to_char(user_id) user_id,
               service_id,
               ‘A‘ modify_tag,
               to_char(start_date, ‘yyyy-mm-dd hh24:mi:ss‘) start_date,
               to_char(end_date, ‘yyyy-mm-dd hh24:mi:ss‘) end_date,
               to_char(ITEM_ID) ITEM_ID,
               to_char(USER_ID_A) USER_ID_A,
               PACKAGE_ID,
               PRODUCT_ID
          FROM uop_crm2.tf_f_user_svc a
         WHERE user_id = TO_NUMBER(‘3114042824225916‘)
           AND partition_id = MOD(TO_NUMBER(‘3114042824225916‘), 10000)
           AND end_date > sysdate
           AND NOT EXISTS
         (SELECT 1
                  FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade c
                 WHERE c.user_id = TO_NUMBER(‘3114042824225916‘)
                   AND c.cancel_tag = ‘0‘
                   AND c.accept_month = TO_NUMBER(‘11‘)
                   AND b.trade_id = c.trade_id
                   AND b.accept_month = c.accept_month
                   AND b.modify_tag in (‘1‘, ‘B‘)
                   AND b.product_id = a.product_id
                   AND b.package_id = a.package_id
                   AND b.service_id = a.service_id
                   AND b.start_date = a.start_date)
        UNION ALL
        SELECT to_char(d.TRADE_ID) TRADE_ID,
               d.ACCEPT_MONTH,
               to_char(d.USER_ID) USER_ID,
               SERVICE_ID,
               MODIFY_TAG,
               to_char(START_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) START_DATE,
               to_char(END_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) END_DATE,
               to_char(ITEM_ID) ITEM_ID,
               to_char(d.USER_ID_A) USER_ID_A,
               d.PACKAGE_ID,
               d.PRODUCT_ID
          FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e
         WHERE e.user_id = TO_NUMBER(‘3114042824225916‘)
           AND e.cancel_tag = ‘0‘
           AND e.accept_month = TO_NUMBER(‘11‘)
           AND d.trade_id = e.trade_id
           AND d.user_id = TO_NUMBER(‘3114042824225916‘)
           AND d.accept_month = TO_NUMBER(‘11‘)
           AND d.modify_tag in (‘0‘, ‘A‘)
           AND NOT EXISTS
         (SELECT 1
                  FROM uop_crm2.tf_b_trade_svc
                 WHERE trade_id = TO_NUMBER(‘3114111918985865‘)
                   AND accept_month = TO_NUMBER(‘11‘)
                   AND modify_tag in (‘1‘, ‘B‘)
                   AND product_id = d.product_id
                   AND package_id = d.package_id
                   AND service_id = d.service_id
                   AND start_date = d.start_date)
        UNION ALL
        SELECT to_char(d.TRADE_ID) TRADE_ID,
               d.accept_month H,
               to_char(d.USER_ID) USER_ID,
               SERVICE_ID,
               MODIFY_TAG,
               to_char(START_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) START_DATE,
               to_char(END_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) END_DATE,
               to_char(ITEM_ID) ITEM_ID,
               to_char(d.USER_ID_A) USER_ID_A,
               d.PACKAGE_ID,
               d.PRODUCT_ID
          FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e
         WHERE e.user_id = TO_NUMBER(‘3114042824225916‘)
           AND e.cancel_tag = ‘0‘
           AND e.accept_month = TO_NUMBER(‘11‘)
           AND d.trade_id = e.trade_id
           AND d.user_id = TO_NUMBER(‘3114042824225916‘)
           AND d.accept_month = TO_NUMBER(‘11‘)
           AND d.modify_tag in (‘1‘, ‘B‘)
           AND SYSDATE < d.end_date
           AND d.start_date < d.end_date) t4
 WHERE t1.product_id(+) = t4.product_id
   AND t2.package_id(+) = t4.package_id
   AND t3.service_id(+) = t4.service_id;
   

----------------------------------------------------------------------------------------------------
基本业务功能包
4G/3G流量提醒


15 rows selected.

Elapsed: 00:01:22.05

Execution Plan
----------------------------------------------------------
Plan hash value: 2411435412

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                     |     3 |   903 | 78573   (1)| 00:15:43 |       |       |
|   1 |  NESTED LOOPS OUTER                         |                     |     3 |   903 | 78573   (1)| 00:15:43 |       |       |
|   2 |   NESTED LOOPS OUTER                        |                     |     3 |   702 | 78570   (1)| 00:15:43 |       |       |
|   3 |    NESTED LOOPS OUTER                       |                     |     3 |   498 | 78567   (1)| 00:15:43 |       |       |
|   4 |     VIEW                                    |                     |     3 |   429 | 78564   (1)| 00:15:43 |       |       |
|   5 |      UNION-ALL                              |                     |       |       |            |          |       |       |
|   6 |       NESTED LOOPS ANTI                     |                     |     1 |    69 | 78492   (1)| 00:15:42 |       |       |
|   7 |        PARTITION RANGE SINGLE               |                     |     1 |    61 |     4   (0)| 00:00:01 |     6 |     6 |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID   | TF_F_USER_SVC       |     1 |    61 |     4   (0)| 00:00:01 |     6 |     6 |
|*  9 |          INDEX RANGE SCAN                   | PK_TF_F_USER_SVC    |     1 |       |     3   (0)| 00:00:01 |     6 |     6 |
|  10 |        VIEW PUSHED PREDICATE                | VW_SQ_1             |     1 |     8 | 78488   (1)| 00:15:42 |       |       |
|  11 |         NESTED LOOPS                        |                     |     1 |    65 | 78488   (1)| 00:15:42 |       |       |
|  12 |          NESTED LOOPS                       |                     |     1 |    65 | 78488   (1)| 00:15:42 |       |       |
|  13 |           PARTITION RANGE SINGLE            |                     |     1 |    40 | 78486   (1)| 00:15:42 |    11 |    11 |
|* 14 |            TABLE ACCESS BY LOCAL INDEX ROWID| TF_B_TRADE_SVC      |     1 |    40 | 78486   (1)| 00:15:42 |    11 |    11 |
|* 15 |             INDEX SKIP SCAN                 | IDX1_TF_B_TRADE_SVC | 62939 |       | 62209   (1)| 00:12:27 |    11 |    11 |
|* 16 |           INDEX UNIQUE SCAN                 | PK_TF_B_TRADE       |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 17 |          TABLE ACCESS BY INDEX ROWID        | TF_B_TRADE          |     1 |    25 |     2   (0)| 00:00:01 |       |       |
|  18 |       NESTED LOOPS                          |                     |     1 |   137 |    64   (0)| 00:00:01 |       |       |
|  19 |        NESTED LOOPS                         |                     |    14 |   137 |    64   (0)| 00:00:01 |       |       |
|  20 |         NESTED LOOPS ANTI                   |                     |    14 |  1568 |    47   (0)| 00:00:01 |       |       |
|  21 |          PARTITION RANGE SINGLE             |                     |    14 |  1008 |     5   (0)| 00:00:01 |    11 |    11 |
|* 22 |           TABLE ACCESS BY LOCAL INDEX ROWID | TF_B_TRADE_SVC      |    14 |  1008 |     5   (0)| 00:00:01 |    11 |    11 |
|* 23 |            INDEX RANGE SCAN                 | IDX1_TF_B_TRADE_SVC |     4 |       |     3   (0)| 00:00:01 |    11 |    11 |
|  24 |          PARTITION RANGE SINGLE             |                     |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |
|* 25 |           INDEX RANGE SCAN                  | PK_TF_B_TRADE_SVC   |     1 |    40 |     3   (0)| 00:00:01 |    11 |    11 |
|* 26 |         INDEX UNIQUE SCAN                   | PK_TF_B_TRADE       |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 27 |        TABLE ACCESS BY INDEX ROWID          | TF_B_TRADE          |     1 |    25 |     2   (0)| 00:00:01 |       |       |
|  28 |       NESTED LOOPS                          |                     |     1 |    97 |     7   (0)| 00:00:01 |       |       |
|  29 |        NESTED LOOPS                         |                     |     1 |    97 |     7   (0)| 00:00:01 |       |       |
|  30 |         PARTITION RANGE SINGLE              |                     |     1 |    72 |     5   (0)| 00:00:01 |    11 |    11 |
|* 31 |          TABLE ACCESS BY LOCAL INDEX ROWID  | TF_B_TRADE_SVC      |     1 |    72 |     5   (0)| 00:00:01 |    11 |    11 |
|* 32 |           INDEX RANGE SCAN                  | IDX1_TF_B_TRADE_SVC |     4 |       |     3   (0)| 00:00:01 |    11 |    11 |
|* 33 |         INDEX UNIQUE SCAN                   | PK_TF_B_TRADE       |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 34 |        TABLE ACCESS BY INDEX ROWID          | TF_B_TRADE          |     1 |    25 |     2   (0)| 00:00:01 |       |       |
|  35 |     TABLE ACCESS BY INDEX ROWID             | TD_B_SERVICE        |     1 |    23 |     1   (0)| 00:00:01 |       |       |
|* 36 |      INDEX UNIQUE SCAN                      | PK_TD_B_SERVICE     |     1 |       |     0   (0)| 00:00:01 |       |       |
|  37 |    TABLE ACCESS BY INDEX ROWID              | TD_B_PRODUCT        |     1 |    68 |     1   (0)| 00:00:01 |       |       |
|* 38 |     INDEX UNIQUE SCAN                       | PK_TD_B_PRODUCT     |     1 |       |     0   (0)| 00:00:01 |       |       |
|  39 |   TABLE ACCESS BY INDEX ROWID               | TD_B_PACKAGE        |     1 |    67 |     1   (0)| 00:00:01 |       |       |
|* 40 |    INDEX UNIQUE SCAN                        | PK_TD_B_PACKAGE     |     1 |       |     0   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - filter("END_DATE">SYSDATE@!)
   9 - access("USER_ID"=3114042824225916 AND "PARTITION_ID"=5916)
  14 - filter("B"."START_DATE"="A"."START_DATE" AND "B"."PRODUCT_ID"="A"."PRODUCT_ID" AND
              "B"."PACKAGE_ID"="A"."PACKAGE_ID" AND ("B"."MODIFY_TAG"=‘1‘ OR "B"."MODIFY_TAG"=‘B‘) AND "B"."ACCEPT_MONTH"=11)
  15 - access("B"."SERVICE_ID"="A"."SERVICE_ID")
       filter("B"."SERVICE_ID"="A"."SERVICE_ID")
  16 - access("B"."TRADE_ID"="C"."TRADE_ID" AND "C"."ACCEPT_MONTH"=11 AND "C"."CANCEL_TAG"=‘0‘)
  17 - filter("C"."USER_ID"=3114042824225916)
  22 - filter(("D"."MODIFY_TAG"=‘0‘ OR "D"."MODIFY_TAG"=‘A‘) AND "D"."ACCEPT_MONTH"=11)
  23 - access("D"."USER_ID"=3114042824225916)
  25 - access("TRADE_ID"=3114111918985865 AND "ACCEPT_MONTH"=11 AND "SERVICE_ID"="D"."SERVICE_ID" AND
              "START_DATE"="D"."START_DATE" AND "PRODUCT_ID"="D"."PRODUCT_ID" AND "PACKAGE_ID"="D"."PACKAGE_ID")
       filter(("MODIFY_TAG"=‘1‘ OR "MODIFY_TAG"=‘B‘) AND "PRODUCT_ID"="D"."PRODUCT_ID" AND "PACKAGE_ID"="D"."PACKAGE_ID"
              AND "SERVICE_ID"="D"."SERVICE_ID" AND "START_DATE"="D"."START_DATE")
  26 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "E"."ACCEPT_MONTH"=11 AND "E"."CANCEL_TAG"=‘0‘)
  27 - filter("E"."USER_ID"=3114042824225916)
  31 - filter(("D"."MODIFY_TAG"=‘1‘ OR "D"."MODIFY_TAG"=‘B‘) AND "D"."START_DATE"<"D"."END_DATE" AND
              "D"."END_DATE">SYSDATE@! AND "D"."ACCEPT_MONTH"=11)
  32 - access("D"."USER_ID"=3114042824225916)
  33 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "E"."ACCEPT_MONTH"=11 AND "E"."CANCEL_TAG"=‘0‘)
  34 - filter("E"."USER_ID"=3114042824225916)
  36 - access("T3"."SERVICE_ID"(+)="T4"."SERVICE_ID")
  38 - access("T1"."PRODUCT_ID"(+)="T4"."PRODUCT_ID")
  40 - access("T2"."PACKAGE_ID"(+)="T4"."PACKAGE_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    7065698  consistent gets
     180962  physical reads
       2212  redo size
       2390  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

(9),由此能够看出sql在使用差的运行计划时在id=15那里使用了索引跳扫导致代价大增,逻辑读达到700百万。这样问题已经非常明白了,就是这个sql导致业务超时。

(10),通过outline固定运行计划后立即问题就攻克了。


4。故障解决

(1),能够通过hint强制索引

(2),使用outline固定运行计划

(3),base line固定运行计划


5。解决步骤

在这里我使用的是outline的方法:

(1),给程序用户赋权

grant create any outline to uop_crm2;

(2),创建outline

conn uop_crm2/xxxxxx;

create outline outline_crm2 for category crm2_cat1 on select trade_id,
       accept_month,
       user_id,
       t4.service_id,
       modify_tag,
       t4.start_date,
       t4.end_date,
       item_id,
       user_id_a,
       t4.package_id,
       t4.product_id,
       t1.product_name,
       t2.package_name,
       t3.service_name
  FROM uop_crm2.TD_B_PRODUCT t1,
       uop_crm2.TD_B_PACKAGE t2,
       uop_crm2.TD_B_SERVICE t3,
       (SELECT to_char(0) trade_id,
               0 accept_month,
               to_char(user_id) user_id,
               service_id,
               ‘A‘ modify_tag,
               to_char(start_date, ‘yyyy-mm-dd hh24:mi:ss‘) start_date,
               to_char(end_date, ‘yyyy-mm-dd hh24:mi:ss‘) end_date,
               to_char(ITEM_ID) ITEM_ID,
               to_char(USER_ID_A) USER_ID_A,
               PACKAGE_ID,
               PRODUCT_ID
          FROM uop_crm2.tf_f_user_svc a
         WHERE user_id = TO_NUMBER(‘3114042824225916‘)
           AND partition_id = MOD(TO_NUMBER(‘3114042824225916‘), 10000)
           AND end_date > sysdate
           AND NOT EXISTS
         (SELECT 1
                  FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade c
                 WHERE c.user_id = TO_NUMBER(‘3114042824225916‘)
                   AND c.cancel_tag = ‘0‘
                   AND c.accept_month = TO_NUMBER(‘11‘)
                   AND b.trade_id = c.trade_id
                   AND b.accept_month = c.accept_month
                   AND b.modify_tag in (‘1‘, ‘B‘)
                   AND b.product_id = a.product_id
                   AND b.package_id = a.package_id
                   AND b.service_id = a.service_id
                   AND b.start_date = a.start_date)
        UNION ALL
        SELECT to_char(d.TRADE_ID) TRADE_ID,
               d.ACCEPT_MONTH,
               to_char(d.USER_ID) USER_ID,
               SERVICE_ID,
               MODIFY_TAG,
               to_char(START_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) START_DATE,
               to_char(END_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) END_DATE,
               to_char(ITEM_ID) ITEM_ID,
               to_char(d.USER_ID_A) USER_ID_A,
               d.PACKAGE_ID,
               d.PRODUCT_ID
          FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e
         WHERE e.user_id = TO_NUMBER(‘3114042824225916‘)
           AND e.cancel_tag = ‘0‘
           AND e.accept_month = TO_NUMBER(‘11‘)
           AND d.trade_id = e.trade_id
           AND d.user_id = TO_NUMBER(‘3114042824225916‘)
           AND d.accept_month = TO_NUMBER(‘11‘)
           AND d.modify_tag in (‘0‘, ‘A‘)
           AND NOT EXISTS
         (SELECT 1
                  FROM uop_crm2.tf_b_trade_svc
                 WHERE trade_id = TO_NUMBER(‘3114111918985865‘)
                   AND accept_month = TO_NUMBER(‘11‘)
                   AND modify_tag in (‘1‘, ‘B‘)
                   AND product_id = d.product_id
                   AND package_id = d.package_id
                   AND service_id = d.service_id
                   AND start_date = d.start_date)
        UNION ALL
        SELECT to_char(d.TRADE_ID) TRADE_ID,
               d.accept_month H,
               to_char(d.USER_ID) USER_ID,
               SERVICE_ID,
               MODIFY_TAG,
               to_char(START_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) START_DATE,
               to_char(END_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) END_DATE,
               to_char(ITEM_ID) ITEM_ID,
               to_char(d.USER_ID_A) USER_ID_A,
               d.PACKAGE_ID,
               d.PRODUCT_ID
          FROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade e
         WHERE e.user_id = TO_NUMBER(‘3114042824225916‘)
           AND e.cancel_tag = ‘0‘
           AND e.accept_month = TO_NUMBER(‘11‘)
           AND d.trade_id = e.trade_id
           AND d.user_id = TO_NUMBER(‘3114042824225916‘)
           AND d.accept_month = TO_NUMBER(‘11‘)
           AND d.modify_tag in (‘1‘, ‘B‘)
           AND SYSDATE < d.end_date
           AND d.start_date < d.end_date) t4
 WHERE t1.product_id(+) = t4.product_id
   AND t2.package_id(+) = t4.package_id
   AND t3.service_id(+) = t4.service_id;
(3)

alter system set use_stored_outlines=crm2_cat1;

6。原因分析:

技术分享

技术分享

此处为什么oracle优化器选择走了IDX1_TF_B_TRADE_SVC的service_id列索引跳扫而没有使用PK_TF_B_TRADE_SVC上的trade_id呢。我们能够看一下索引的信息:

SQL> l
  1* select index_name,blevel,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY from dba_indexes where TABLE_NAME=‘TF_B_TRADE_SVC‘
SQL> /

INDEX_NAME                         BLEVEL AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------------------- -----------------------
IDX1_TF_B_TRADE_SVC                     2                       1                       1
PK_TF_B_TRADE_SVC                       3                       1                       1

当两个索引AVG_LEAF_BLOCKS_PER_KEY+AVG_DATA_BLOCKS_PER_KEY的值同样时,就推断blevel。此处因为IDX1_TF_B_TRADE_SVC的blevel=2。oracle

觉得走这个索引会有较少的IO,而sql语句中正好有service_id的谓词,因此选择走了跳扫。具体描写叙述參考为什么Oracle不使用我的索引?! 一文。

TABLE_NAME                     TABLE_OWNER                    INDEX_NAME                     COLUMN_NAME   COLUMN_POSITION
------------------------------ ------------------------------ ------------------------------ ------------- ---------------
TF_B_TRADE_SVC                 UCR_CRM2                       IDX1_TF_B_TRADE_SVC            USER_ID                     1
TF_B_TRADE_SVC                 UCR_CRM2                       IDX1_TF_B_TRADE_SVC            SERVICE_ID                  2

TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              TRADE_ID                    1
TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              ACCEPT_MONTH                2
TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              USER_ID                     3
TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              SERVICE_ID                  4
TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              START_DATE                  5
TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              PRODUCT_ID                  6
TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              PACKAGE_ID                  7
TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              USER_ID_A                   8
TF_B_TRADE_SVC                 UCR_CRM2                       PK_TF_B_TRADE_SVC              MODIFY_TAG                  9

7,续:

经过这两天对该sql的运行计划的深入分析。发现前面对oracle为何选择差的运行计划的推断有偏差,至少在此次案例中应该不是因为上面第6步说的那样。

出现该问题的解决办法是这种

(1)当产生差的运行计划的时候。我们发现绑定变量传入的值accept_month=‘33‘,然而月份正常就应该是1-12。大家能够从以下的运行计划中看到

【4 - :VACCEPT_MONTH (VARCHAR2(30), CSID=852): ‘33‘  】。

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1huatx9vws2u3, child number 27                                                                                                                                              
--------------------------------------                                                                                                                                              
SELECT trade_id,accept_month,user_id,t4.service_id,modify_tag,t4.start_d                                                                                                            
ate,t4.end_date,        item_id,user_id_a,t4.package_id,t4.product_id,t1                                                                                                            
.product_name,t2.package_name,        t3.service_name   FROM                                                                                                                        
TD_B_PRODUCT t1,TD_B_PACKAGE t2,TD_B_SERVICE t3,( SELECT to_char(0)                                                                                                                 
trade_id,0 accept_month,to_char(user_id) user_id,service_id,‘A‘                                                                                                                     
modify_tag,        to_char(start_date,‘yyyy-mm-dd hh24:mi:ss‘)                                                                                                                      
start_date,to_char(end_date,‘yyyy-mm-dd hh24:mi:ss‘) end_date,                                                                                                                      
to_char(ITEM_ID) ITEM_ID,to_char(USER_ID_A)                                                                                                                                         
USER_ID_A,PACKAGE_ID,PRODUCT_ID   FROM tf_f_user_svc a  WHERE user_id =                                                                                                             

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TO_NUMBER(:VUSER_ID)    AND partition_id =                                                                                                                                          
MOD(TO_NUMBER(:VUSER_ID),10000)    AND end_date>sysdate    AND NOT                                                                                                                  
EXISTS (SELECT 1 FROM tf_b_trade_svc b,tf_b_trade c                                                                                                                                 
WHERE c.user_id = TO_NUMBER(:VUSER_ID) AND c.cancel_tag=‘0‘                                                                                                                         
          AND c.accept_month = TO_NUMBER(:VACCEPT_MONTH)                                                                                                                            
       AND b.trade_id = c.trade_id                       AND                                                                                                                        
b.accept_month = c.accept_m                                                                                                                                                         
                                                                                                                                                                                    
Plan hash value: 353242268                                                                                                                                                          
                                                                                                                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------                                                 

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                 
-----------------------------------------------------------------------------------------------------------------------------------                                                 
|   0 | SELECT STATEMENT                            |                     |       |       |    17 (100)|          |       |       |                                                 
|   1 |  NESTED LOOPS OUTER                         |                     |     3 |   870 |    17   (0)| 00:00:01 |       |       |                                                 
|   2 |   NESTED LOOPS OUTER                        |                     |     3 |   669 |    14   (0)| 00:00:01 |       |       |                                                 
|   3 |    NESTED LOOPS OUTER                       |                     |     3 |   465 |    11   (0)| 00:00:01 |       |       |                                                 
|   4 |     VIEW                                    |                     |     3 |   396 |     8   (0)| 00:00:01 |       |       |                                                 
|   5 |      UNION-ALL                              |                     |       |       |            |          |       |       |                                                 
|   6 |       NESTED LOOPS ANTI                     |                     |     1 |    69 |     6   (0)| 00:00:01 |       |       |                                                 
|   7 |        PARTITION RANGE SINGLE               |                     |     1 |    61 |     4   (0)| 00:00:01 |   KEY |   KEY |                                                 
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID   | TF_F_USER_SVC       |     1 |    61 |     4   (0)| 00:00:01 |   KEY |   KEY |                                                 

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  9 |          INDEX RANGE SCAN                   | PK_TF_F_USER_SVC    |     1 |       |     3   (0)| 00:00:01 |   KEY |   KEY |                                                 
|  10 |        VIEW PUSHED PREDICATE                | VW_SQ_1             |     1 |     8 |     2   (0)| 00:00:01 |       |       |                                                 
|  11 |         NESTED LOOPS                        |                     |     1 |   102 |     2   (0)| 00:00:01 |       |       |                                                 
|  12 |          NESTED LOOPS                       |                     |     1 |   102 |     2   (0)| 00:00:01 |       |       |                                                 
|  13 |           PARTITION RANGE SINGLE            |                     |     1 |    77 |     1   (0)| 00:00:01 |   KEY |   KEY |                                                 
|* 14 |            TABLE ACCESS BY LOCAL INDEX ROWID| TF_B_TRADE_SVC      |     1 |    77 |     1   (0)| 00:00:01 |   KEY |   KEY |                                                 
|* 15 |             INDEX SKIP SCAN                 | IDX1_TF_B_TRADE_SVC |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |                                                 
|* 16 |           INDEX UNIQUE SCAN                 | PK_TF_B_TRADE       |     1 |       |     1   (0)| 00:00:01 |       |       |                                                 
|* 17 |          TABLE ACCESS BY INDEX ROWID        | TF_B_TRADE          |     1 |    25 |     1   (0)| 00:00:01 |       |       |                                                 
|  18 |       NESTED LOOPS                          |                     |     1 |   227 |     1   (0)| 00:00:01 |       |       |                                                 
|  19 |        NESTED LOOPS                         |                     |     1 |   227 |     1   (0)| 00:00:01 |       |       |                                                 

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  20 |         NESTED LOOPS ANTI                   |                     |     1 |   202 |     0   (0)|          |       |       |                                                 
|  21 |          PARTITION RANGE SINGLE             |                     |     1 |   125 |     0   (0)|          |   KEY |   KEY |                                                 
|* 22 |           TABLE ACCESS BY LOCAL INDEX ROWID | TF_B_TRADE_SVC      |     1 |   125 |     0   (0)|          |   KEY |   KEY |                                                 
|* 23 |            INDEX RANGE SCAN                 | IDX1_TF_B_TRADE_SVC |     1 |       |     0   (0)|          |   KEY |   KEY |                                                 
|  24 |          PARTITION RANGE SINGLE             |                     |     1 |    77 |     0   (0)|          |   KEY |   KEY |                                                 
|* 25 |           INDEX RANGE SCAN                  | PK_TF_B_TRADE_SVC   |     1 |    77 |     0   (0)|          |   KEY |   KEY |                                                 
|* 26 |         INDEX UNIQUE SCAN                   | PK_TF_B_TRADE       |     1 |       |     1   (0)| 00:00:01 |       |       |                                                 
|* 27 |        TABLE ACCESS BY INDEX ROWID          | TF_B_TRADE          |     1 |    25 |     1   (0)| 00:00:01 |       |       |                                                 
|  28 |       NESTED LOOPS                          |                     |     1 |   150 |     1   (0)| 00:00:01 |       |       |                                                 
|  29 |        NESTED LOOPS                         |                     |     1 |   150 |     1   (0)| 00:00:01 |       |       |                                                 
|  30 |         PARTITION RANGE SINGLE              |                     |     1 |   125 |     0   (0)|          |   KEY |   KEY |                                                 

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 31 |          TABLE ACCESS BY LOCAL INDEX ROWID  | TF_B_TRADE_SVC      |     1 |   125 |     0   (0)|          |   KEY |   KEY |                                                 
|* 32 |           INDEX RANGE SCAN                  | IDX1_TF_B_TRADE_SVC |     1 |       |     0   (0)|          |   KEY |   KEY |                                                 
|* 33 |         INDEX UNIQUE SCAN                   | PK_TF_B_TRADE       |     1 |       |     1   (0)| 00:00:01 |       |       |                                                 
|* 34 |        TABLE ACCESS BY INDEX ROWID          | TF_B_TRADE          |     1 |    25 |     1   (0)| 00:00:01 |       |       |                                                 
|  35 |     TABLE ACCESS BY INDEX ROWID             | TD_B_SERVICE        |     1 |    23 |     1   (0)| 00:00:01 |       |       |                                                 
|* 36 |      INDEX UNIQUE SCAN                      | PK_TD_B_SERVICE     |     1 |       |     0   (0)|          |       |       |                                                 
|  37 |    TABLE ACCESS BY INDEX ROWID              | TD_B_PRODUCT        |     1 |    68 |     1   (0)| 00:00:01 |       |       |                                                 
|* 38 |     INDEX UNIQUE SCAN                       | PK_TD_B_PRODUCT     |     1 |       |     0   (0)|          |       |       |                                                 
|  39 |   TABLE ACCESS BY INDEX ROWID               | TD_B_PACKAGE        |     1 |    67 |     1   (0)| 00:00:01 |       |       |                                                 
|* 40 |    INDEX UNIQUE SCAN                        | PK_TD_B_PACKAGE     |     1 |       |     0   (0)|          |       |       |                                                 
-----------------------------------------------------------------------------------------------------------------------------------                                                 

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                                                    
Query Block Name / Object Alias (identified by operation id):                                                                                                                       
-------------------------------------------------------------                                                                                                                       
                                                                                                                                                                                    
   1 - SEL$1                                                                                                                                                                        
   4 - SET$1        / T4@SEL$1                                                                                                                                                      
   5 - SET$1                                                                                                                                                                        
   6 - SEL$B29E968D                                                                                                                                                                 
   8 - SEL$B29E968D / A@SEL$2                                                                                                                                                       
   9 - SEL$B29E968D / A@SEL$2                                                                                                                                                       
  10 - SEL$2AD7F9D9 / VW_SQ_1@SEL$E9784550                                                                                                                                          

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  11 - SEL$2AD7F9D9                                                                                                                                                                 
  14 - SEL$2AD7F9D9 / B@SEL$3                                                                                                                                                       
  15 - SEL$2AD7F9D9 / B@SEL$3                                                                                                                                                       
  16 - SEL$2AD7F9D9 / C@SEL$3                                                                                                                                                       
  17 - SEL$2AD7F9D9 / C@SEL$3                                                                                                                                                       
  18 - SEL$385088EC                                                                                                                                                                 
  22 - SEL$385088EC / D@SEL$4                                                                                                                                                       
  23 - SEL$385088EC / D@SEL$4                                                                                                                                                       
  25 - SEL$385088EC / TF_B_TRADE_SVC@SEL$5                                                                                                                                          
  26 - SEL$385088EC / E@SEL$4                                                                                                                                                       
  27 - SEL$385088EC / E@SEL$4                                                                                                                                                       

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  28 - SEL$6                                                                                                                                                                        
  31 - SEL$6        / D@SEL$6                                                                                                                                                       
  32 - SEL$6        / D@SEL$6                                                                                                                                                       
  33 - SEL$6        / E@SEL$6                                                                                                                                                       
  34 - SEL$6        / E@SEL$6                                                                                                                                                       
  35 - SEL$1        / T3@SEL$1                                                                                                                                                      
  36 - SEL$1        / T3@SEL$1                                                                                                                                                      
  37 - SEL$1        / T1@SEL$1                                                                                                                                                      
  38 - SEL$1        / T1@SEL$1                                                                                                                                                      
  39 - SEL$1        / T2@SEL$1                                                                                                                                                      
  40 - SEL$1        / T2@SEL$1                                                                                                                                                      

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                                                    
Outline Data                                                                                                                                                                        
-------------                                                                                                                                                                       
                                                                                                                                                                                    
  /*+                                                                                                                                                                               
      BEGIN_OUTLINE_DATA                                                                                                                                                            
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                                   
      OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4‘)                                                                                                                                         
      DB_VERSION(‘11.2.0.4‘)                                                                                                                                                        
      ALL_ROWS                                                                                                                                                                      
      OUTLINE_LEAF(@"SEL$2AD7F9D9")                                                                                                                                                 

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      PUSH_PRED(@"SEL$B29E968D" "VW_SQ_1"@"SEL$E9784550" 7 6 5 4)                                                                                                                   
      OUTLINE_LEAF(@"SEL$B29E968D")                                                                                                                                                 
      UNNEST(@"SEL$3")                                                                                                                                                              
      OUTLINE_LEAF(@"SEL$385088EC")                                                                                                                                                 
      UNNEST(@"SEL$5")                                                                                                                                                              
      OUTLINE_LEAF(@"SEL$6")                                                                                                                                                        
      OUTLINE_LEAF(@"SET$1")                                                                                                                                                        
      OUTLINE_LEAF(@"SEL$1")                                                                                                                                                        
      OUTLINE(@"SEL$291F8F59")                                                                                                                                                      
      OUTLINE(@"SEL$B29E968D")                                                                                                                                                      
      UNNEST(@"SEL$3")                                                                                                                                                              

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      OUTLINE(@"SEL$E9784550")                                                                                                                                                      
      OUTLINE(@"SEL$3")                                                                                                                                                             
      OUTLINE(@"SEL$4")                                                                                                                                                             
      OUTLINE(@"SEL$5")                                                                                                                                                             
      OUTLINE(@"SEL$2")                                                                                                                                                             
      NO_ACCESS(@"SEL$1" "T4"@"SEL$1")                                                                                                                                              
      INDEX_RS_ASC(@"SEL$1" "T3"@"SEL$1" ("TD_B_SERVICE"."SERVICE_ID"))                                                                                                             
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("TD_B_PRODUCT"."PRODUCT_ID"))                                                                                                             
      INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("TD_B_PACKAGE"."PACKAGE_ID"))                                                                                                             
      LEADING(@"SEL$1" "T4"@"SEL$1" "T3"@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")                                                                                                         
      USE_NL(@"SEL$1" "T3"@"SEL$1")                                                                                                                                                 

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      USE_NL(@"SEL$1" "T1"@"SEL$1")                                                                                                                                                 
      USE_NL(@"SEL$1" "T2"@"SEL$1")                                                                                                                                                 
      INDEX_RS_ASC(@"SEL$6" "D"@"SEL$6" ("TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID"))                                                                                 
      INDEX(@"SEL$6" "E"@"SEL$6" ("TF_B_TRADE"."TRADE_ID" "TF_B_TRADE"."ACCEPT_MONTH" "TF_B_TRADE"."CANCEL_TAG"))                                                                   
      LEADING(@"SEL$6" "D"@"SEL$6" "E"@"SEL$6")                                                                                                                                     
      USE_NL(@"SEL$6" "E"@"SEL$6")                                                                                                                                                  
      NLJ_BATCHING(@"SEL$6" "E"@"SEL$6")                                                                                                                                            
      INDEX_RS_ASC(@"SEL$385088EC" "D"@"SEL$4" ("TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID"))                                                                          
      INDEX(@"SEL$385088EC" "TF_B_TRADE_SVC"@"SEL$5" ("TF_B_TRADE_SVC"."TRADE_ID" "TF_B_TRADE_SVC"."ACCEPT_MONTH"                                                                   
              "TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID" "TF_B_TRADE_SVC"."START_DATE" "TF_B_TRADE_SVC"."PRODUCT_ID"                                                  
              "TF_B_TRADE_SVC"."PACKAGE_ID" "TF_B_TRADE_SVC"."USER_ID_A" "TF_B_TRADE_SVC"."MODIFY_TAG"))                                                                            

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      INDEX(@"SEL$385088EC" "E"@"SEL$4" ("TF_B_TRADE"."TRADE_ID" "TF_B_TRADE"."ACCEPT_MONTH" "TF_B_TRADE"."CANCEL_TAG"))                                                            
      LEADING(@"SEL$385088EC" "D"@"SEL$4" "TF_B_TRADE_SVC"@"SEL$5" "E"@"SEL$4")                                                                                                     
      USE_NL(@"SEL$385088EC" "TF_B_TRADE_SVC"@"SEL$5")                                                                                                                              
      USE_NL(@"SEL$385088EC" "E"@"SEL$4")                                                                                                                                           
      NLJ_BATCHING(@"SEL$385088EC" "E"@"SEL$4")                                                                                                                                     
      INDEX_RS_ASC(@"SEL$B29E968D" "A"@"SEL$2" ("TF_F_USER_SVC"."USER_ID" "TF_F_USER_SVC"."PARTITION_ID"                                                                            
              "TF_F_USER_SVC"."SERVICE_ID" "TF_F_USER_SVC"."START_DATE"))                                                                                                           
      NO_ACCESS(@"SEL$B29E968D" "VW_SQ_1"@"SEL$E9784550")                                                                                                                           
      LEADING(@"SEL$B29E968D" "A"@"SEL$2" "VW_SQ_1"@"SEL$E9784550")                                                                                                                 
      USE_NL(@"SEL$B29E968D" "VW_SQ_1"@"SEL$E9784550")                                                                                                                              
      INDEX_SS(@"SEL$2AD7F9D9" "B"@"SEL$3" ("TF_B_TRADE_SVC"."USER_ID" "TF_B_TRADE_SVC"."SERVICE_ID"))                                                                              

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      INDEX(@"SEL$2AD7F9D9" "C"@"SEL$3" ("TF_B_TRADE"."TRADE_ID" "TF_B_TRADE"."ACCEPT_MONTH" "TF_B_TRADE"."CANCEL_TAG"))                                                            
      LEADING(@"SEL$2AD7F9D9" "B"@"SEL$3" "C"@"SEL$3")                                                                                                                              
      USE_NL(@"SEL$2AD7F9D9" "C"@"SEL$3")                                                                                                                                           
      NLJ_BATCHING(@"SEL$2AD7F9D9" "C"@"SEL$3")                                                                                                                                     
      END_OUTLINE_DATA                                                                                                                                                              
  */                                                                                                                                                                                
                                                                                                                                                                                    
Peeked Binds (identified by position):                                                                                                                                              
--------------------------------------                                                                                                                                              
                                                                                                                                                                                    
   1 - :VUSER_ID (VARCHAR2(30), CSID=852): ‘8714102826192154‘                                                                                                                       

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - :VUSER_ID (VARCHAR2(30), CSID=852, Primary=1)                                                                                                                                
   3 - :VUSER_ID (VARCHAR2(30), CSID=852, Primary=1)                                                                                                                                
   4 - :VACCEPT_MONTH (VARCHAR2(30), CSID=852): ‘33‘                                                                                                                                
   5 - :VUSER_ID (VARCHAR2(30), CSID=852, Primary=1)                                                                                                                                
   6 - :VACCEPT_MONTH (VARCHAR2(30), CSID=852, Primary=4)                                                                                                                           
   7 - :VUSER_ID (VARCHAR2(30), CSID=852, Primary=1)                                                                                                                                
   8 - :VACCEPT_MONTH (VARCHAR2(30), CSID=852, Primary=4)                                                                                                                           
   9 - :VTRADE_ID (VARCHAR2(30), CSID=852): ‘1416331900142000‘                                                                                                                      
  10 - :VACCEPT_MONTH (VARCHAR2(30), CSID=852, Primary=4)                                                                                                                           
  11 - :VUSER_ID (VARCHAR2(30), CSID=852, Primary=1)                                                                                                                                
  12 - :VACCEPT_MONTH (VARCHAR2(30), CSID=852, Primary=4)                                                                                                                           

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  13 - :VUSER_ID (VARCHAR2(30), CSID=852, Primary=1)                                                                                                                                
  14 - :VACCEPT_MONTH (VARCHAR2(30), CSID=852, Primary=4)                                                                                                                           
                                                                                                                                                                                    
Predicate Information (identified by operation id):                                                                                                                                 
---------------------------------------------------                                                                                                                                 
                                                                                                                                                                                    
   8 - filter("END_DATE">SYSDATE@!)                                                                                                                                                 
   9 - access("USER_ID"=TO_NUMBER(:VUSER_ID) AND "PARTITION_ID"=MOD(TO_NUMBER(:VUSER_ID),10000))                                                                                    
  14 - filter(("B"."START_DATE"="A"."START_DATE" AND "B"."PACKAGE_ID"="A"."PACKAGE_ID" AND                                                                                          
              "B"."PRODUCT_ID"="A"."PRODUCT_ID" AND "B"."ACCEPT_MONTH"=TO_NUMBER(:VACCEPT_MONTH) AND                                                                                
              INTERNAL_FUNCTION("B"."MODIFY_TAG")))                                                                                                                                 

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  15 - access("B"."SERVICE_ID"="A"."SERVICE_ID")                                                                                                                                    
       filter("B"."SERVICE_ID"="A"."SERVICE_ID")                                                                                                                                    
  16 - access("B"."TRADE_ID"="C"."TRADE_ID" AND "C"."ACCEPT_MONTH"=TO_NUMBER(:VACCEPT_MONTH) AND "C"."CANCEL_TAG"=‘0‘)                                                              
  17 - filter("C"."USER_ID"=TO_NUMBER(:VUSER_ID))                                                                                                                                   
  22 - filter(("D"."ACCEPT_MONTH"=TO_NUMBER(:VACCEPT_MONTH) AND INTERNAL_FUNCTION("D"."MODIFY_TAG")))                                                                               
  23 - access("D"."USER_ID"=TO_NUMBER(:VUSER_ID))                                                                                                                                   
  25 - access("TRADE_ID"=TO_NUMBER(:VTRADE_ID) AND "ACCEPT_MONTH"=TO_NUMBER(:VACCEPT_MONTH) AND                                                                                     
              "SERVICE_ID"="D"."SERVICE_ID" AND "START_DATE"="D"."START_DATE" AND "PRODUCT_ID"="D"."PRODUCT_ID" AND                                                                 
              "PACKAGE_ID"="D"."PACKAGE_ID")                                                                                                                                        
       filter((INTERNAL_FUNCTION("MODIFY_TAG") AND "PRODUCT_ID"="D"."PRODUCT_ID" AND "PACKAGE_ID"="D"."PACKAGE_ID" AND                                                              
              "SERVICE_ID"="D"."SERVICE_ID" AND "START_DATE"="D"."START_DATE"))                                                                                                     

PLAN_TABLE_OUTPUT                                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  26 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "E"."ACCEPT_MONTH"=TO_NUMBER(:VACCEPT_MONTH) AND "E"."CANCEL_TAG"=‘0‘)                                                              
  27 - filter("E"."USER_ID"=TO_NUMBER(:VUSER_ID))                                                                                                                                   
  31 - filter(("D"."START_DATE"<"D"."END_DATE" AND "D"."ACCEPT_MONTH"=TO_NUMBER(:VACCEPT_MONTH) AND                                                                                 
              "D"."END_DATE">SYSDATE@! AND INTERNAL_FUNCTION("D"."MODIFY_TAG")))                                                                                                    
  32 - access("D"."USER_ID"=TO_NUMBER(:VUSER_ID))                                                                                                                                   
  33 - access("D"."TRADE_ID"="E"."TRADE_ID" AND "E"."ACCEPT_MONTH"=TO_NUMBER(:VACCEPT_MONTH) AND "E"."CANCEL_TAG"=‘0‘)                                                              
  34 - filter("E"."USER_ID"=TO_NUMBER(:VUSER_ID))                                                                                                                                   
  36 - access("T3"."SERVICE_ID"="T4"."SERVICE_ID")                                                                                                                                  
  38 - access("T1"."PRODUCT_ID"="T4"."PRODUCT_ID")                                                                                                                                  
  40 - access("T2"."PACKAGE_ID"="T4"."PACKAGE_ID")     

(2),因为表中根本就没有accept_month=33的数据,因此Oracle又生成了一个新的运行计划。并且该运行计划(也就是差的运行计划)

在33的时候代价很低,上面的输出能够看出,cost=17.

COLUMN_NAME            NUM_ROWS CARDINALITY SELECTIVITY  NUM_NULLS    DENSITY HISTOGRAM       NUM_BUCKETS
-------------------- ---------- ----------- ----------- ---------- ---------- --------------- -----------
USER_ID_A              34141892           1           0          0 1.4309E-08 FREQUENCY                 1
ITEM_ID                34141892    33290240       97.51          0 3.0039E-08 NONE                      1
PACKAGE_ID             34141892          38           0          0 1.4304E-08 FREQUENCY                10
PRODUCT_ID             34141892          82           0          0 1.4304E-08 FREQUENCY                33
END_DATE               34141892       82824         .24          0 .000804505 HEIGHT BALANCED         254
START_DATE             34141892     1024768           3          0 .000467071 HEIGHT BALANCED         255
MODIFY_TAG             34141892           4           0          0 1.4307E-08 FREQUENCY                 3
SERVICE_ID             34141892          90           0          0 1.4307E-08 FREQUENCY                25
USER_ID                34141892     2220288         6.5          0 4.5039E-07 NONE                      1
ACCEPT_MONTH           34141892          10           0          0 1.4309E-08 FREQUENCY                10
TRADE_ID               34141892     2425344         7.1          0 4.1231E-07 NONE                      1

11 rows selected.

(3)。这样就非常明确了,由于生成差的运行计划的时候的代价比最初的运行计划的代价更小,因此在以后当你accept_month传入

1-12的正常值时,Oracle也选择了这个运行计划,可是当传入正常值时这个运行计划的cost却是7w+。终于导致延迟。

(4),这也解释了为什么我们从sqlplus代入变量运行快的原因就是由于我们运行的时候是在sys用户下运行的,表名前面都加入了

username,这也文本不同sql_id也不同,并且我们传入了一个正常值sccept_month=11,所以生成的运行计划是正确的,就无法再现

这个问题。假设要再现这个问题也非常easy,仅仅要传入一个无效值就能产生新的运行计划,然后再用有效值。就能够再现。

(5),该问题反馈给业务及开发者后,开发者也确认了是因为台账表中订单ACCEPT_MONTH(受理月份)不在规定范围1-12月内导致。

------------------结束---------------------


【故障处理141119】一次数据库不繁忙时一条sql语句2个运行计划导致业务超时的故障处理