【故障处理141119】一次数据库不繁忙时一条sql语句2个执行计划导致业务超时的故障处理(三)
EL$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