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