---------------------------------------------------------------------------------------------------- 基本业务功能包 4G/3G流量提醒 15rows 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
【故障处理141119】一次数据库不繁忙时一条sql语句2个执行计划导致业务超时的故障处理(四)
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;