首页 > 代码库 > 【故障处理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个运行计划导致业务超时的故障处理