【故障处理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_nameFROM 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_IDFROM uop_crm2.tf_f_user_svc aWHERE user_id = TO_NUMBER('3114042824225916')AND partition_id = MOD(TO_NUMBER('3114042824225916'), 10000)AND end_date > sysdateAND NOT EXISTS(SELECT 1FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade cWHERE c.user_id = TO_NUMBER('3114042824225916')AND c.cancel_tag = '0'AND c.accept_month = TO_NUMBER('11')AND b.trade_id = c.trade_idAND b.accept_month = c.accept_monthAND b.modify_tag in ('1', 'B')AND b.product_id = a.product_idAND b.package_id = a.package_idAND b.service_id = a.service_idAND b.start_date = a.start_date)UNION ALLSELECT 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_IDFROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade eWHERE e.user_id = TO_NUMBER('3114042824225916')AND e.cancel_tag = '0'AND e.accept_month = TO_NUMBER('11')AND d.trade_id = e.trade_idAND d.user_id = TO_NUMBER('3114042824225916')AND d.accept_month = TO_NUMBER('11')AND d.modify_tag in ('0', 'A')AND NOT EXISTS(SELECT 1FROM uop_crm2.tf_b_trade_svcWHERE trade_id = TO_NUMBER('3114111918985865')AND accept_month = TO_NUMBER('11')AND modify_tag in ('1', 'B')AND product_id = d.product_idAND package_id = d.package_idAND service_id = d.service_idAND start_date = d.start_date)UNION ALLSELECT 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_IDFROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade eWHERE e.user_id = TO_NUMBER('3114042824225916')AND e.cancel_tag = '0'AND e.accept_month = TO_NUMBER('11')AND d.trade_id = e.trade_idAND d.user_id = TO_NUMBER('3114042824225916')AND d.accept_month = TO_NUMBER('11')AND d.modify_tag in ('1', 'B')AND SYSDATE < d.end_dateAND d.start_date < d.end_date) t4WHERE t1.product_id(+) = t4.product_idAND t2.package_id(+) = t4.package_idAND t3.service_id(+) = t4.service_id;PACKAGE_NAME
----------------------------------------------------------------------------------------------------
SERVICE_NAME
----------------------------------------------------------------------------------------------------
基本业务功能包
呼叫保持15 rows selected.Elapsed: 00:00:00.01Execution 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 calls0 db block gets82 consistent gets0 physical reads0 redo size2390 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)15 rows processedSQL> (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_nameFROM 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_IDFROM uop_crm2.tf_f_user_svc aWHERE user_id = TO_NUMBER('3114042824225916')AND partition_id = MOD(TO_NUMBER('3114042824225916'), 10000)AND end_date > sysdateAND NOT EXISTS(SELECT 1FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade cWHERE c.user_id = TO_NUMBER('3114042824225916')AND c.cancel_tag = '0'AND c.accept_month = TO_NUMBER('11')AND b.trade_id = c.trade_idAND b.accept_month = c.accept_monthAND b.modify_tag in ('1', 'B')AND b.product_id = a.product_idAND b.package_id = a.package_idAND b.service_id = a.service_idAND b.start_date = a.start_date)UNION ALLSELECT 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_IDFROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade eWHERE e.user_id = TO_NUMBER('3114042824225916')AND e.cancel_tag = '0'AND e.accept_month = TO_NUMBER('11')AND d.trade_id = e.trade_idAND d.user_id = TO_NUMBER('3114042824225916')AND d.accept_month = TO_NUMBER('11')AND d.modify_tag in ('0', 'A')AND NOT EXISTS(SELECT 1FROM uop_crm2.tf_b_trade_svcWHERE trade_id = TO_NUMBER('3114111918985865')AND accept_month = TO_NUMBER('11')AND modify_tag in ('1', 'B')AND product_id = d.product_idAND package_id = d.package_idAND service_id = d.service_idAND start_date = d.start_date)UNION ALLSELECT 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_IDFROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade eWHERE e.user_id = TO_NUMBER('3114042824225916')AND e.cancel_tag = '0'AND e.accept_month = TO_NUMBER('11')AND d.trade_id = e.trade_idAND d.user_id = TO_NUMBER('3114042824225916')AND d.accept_month = TO_NUMBER('11')AND d.modify_tag in ('1', 'B')AND SYSDATE < d.end_dateAND d.start_date < d.end_date) t4WHERE t1.product_id(+) = t4.product_idAND t2.package_id(+) = t4.package_idAND t3.service_id(+) = t4.service_id;----------------------------------------------------------------------------------------------------
基本业务功能包
4G/3G流量提醒15 rows selected.Elapsed: 00:01:22.05Execution 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 calls0 db block gets7065698 consistent gets180962 physical reads2212 redo size2390 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 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_nameFROM 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_IDFROM uop_crm2.tf_f_user_svc aWHERE user_id = TO_NUMBER('3114042824225916')AND partition_id = MOD(TO_NUMBER('3114042824225916'), 10000)AND end_date > sysdateAND NOT EXISTS(SELECT 1FROM uop_crm2.tf_b_trade_svc b, uop_crm2.tf_b_trade cWHERE c.user_id = TO_NUMBER('3114042824225916')AND c.cancel_tag = '0'AND c.accept_month = TO_NUMBER('11')AND b.trade_id = c.trade_idAND b.accept_month = c.accept_monthAND b.modify_tag in ('1', 'B')AND b.product_id = a.product_idAND b.package_id = a.package_idAND b.service_id = a.service_idAND b.start_date = a.start_date)UNION ALLSELECT 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_IDFROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade eWHERE e.user_id = TO_NUMBER('3114042824225916')AND e.cancel_tag = '0'AND e.accept_month = TO_NUMBER('11')AND d.trade_id = e.trade_idAND d.user_id = TO_NUMBER('3114042824225916')AND d.accept_month = TO_NUMBER('11')AND d.modify_tag in ('0', 'A')AND NOT EXISTS(SELECT 1FROM uop_crm2.tf_b_trade_svcWHERE trade_id = TO_NUMBER('3114111918985865')AND accept_month = TO_NUMBER('11')AND modify_tag in ('1', 'B')AND product_id = d.product_idAND package_id = d.package_idAND service_id = d.service_idAND start_date = d.start_date)UNION ALLSELECT 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_IDFROM uop_crm2.TF_B_TRADE_SVC d, uop_crm2.tf_b_trade eWHERE e.user_id = TO_NUMBER('3114042824225916')AND e.cancel_tag = '0'AND e.accept_month = TO_NUMBER('11')AND d.trade_id = e.trade_idAND d.user_id = TO_NUMBER('3114042824225916')AND d.accept_month = TO_NUMBER('11')AND d.modify_tag in ('1', 'B')AND SYSDATE < d.end_dateAND d.start_date < d.end_date) t4WHERE t1.product_id(+) = t4.product_idAND t2.package_id(+) = t4.package_idAND t3.service_id(+) = t4.service_id;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> l1* 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 2TF_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 97，续：
经过这两天对该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 111 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月内导致。
------------------结束---------------------
posted on 2017-04-26 08:16 mthoutai 阅读( ...) 评论( ...) 编辑 收藏
