SQL执行异常系列之——绑定变量窥探导致的执行异常
应用反映批量进展缓慢，通过查询得知此sql在生成执行计划时，带入的变量为数据分布较小的值，但在数据分布较大值的情况下，再走之前的执行计划就会变得很慢。以下为收集的部分数据，仅作归类记录：
原始代码：
SELECT DEAL_ID FROM TQE_PS_DEALS WHERE GENSYS = :B3 AND DEALDATE_CP <= :B2 AND CPTY_BANK = :B1
1.当前sql的cursor如下：
SQL_ID 5vxuubbx3hqqv, child number 0 ------------------------------------- SELECT "A1"."DEAL_ID", "A1"."DTYPE", "A1"."DFLAG", "A1"."BANK_ID", "A1"."ORG_ID", "A1"."ORG2_ID", "A1"."CPTY", "A1"."CPTY_BANK", "A1"."GENSYS", "A1"."GENSYS_DEALID", "A1"."SYSTEMDATE", "A1"."SYSTEMTIME", "A1"."DEALDATE", "A1"."DEALTIME", "A1"."DEALDATE_CP", "A1"."DEALTIME_CP", "A1"."ACCOUNTDATE", "A1"."VALUEDATE", "A1"."VALUEDATE_CP", "A1"."CTYPE", "A1"."MTYPE", "A1"."CYCODE1", "A1"."CYCODE2", "A1"."BUYSELL", "A1"."AMOUNT1", "A1"."AMOUNT2", "A1"."SPOTRATE", "A1"."CLIENTRATE", "A1"."OPENRATE", "A1"."ACCOUNTTYPE", "A1"."ORDERTYPE", "A1"."TERMINATION", "A1"."TRADERID", "A1"."IMPORTTYPE", "A1"."FLAG1", "A1"."APPID", "A1"."QUANTITY", "A1"."SUMTYPE", "A1"."CKADJUST_ID", "A1"."CLOSERESULT", "A1"."ACCOUNT", "A1"."MCYCODE", "A1"."OPENAMOUNT1", "A1"."OPENAMOUNT2", "A1"."PLFLAG", "A1"."PLORIGCYCODE", "A1"."PLORIGAMOUNT", "A1"."PLCYCODE", "A1"."PLAMOUNT", "A1"."PLRATE", "A1"."SUMDEAL_ID", "A1"."COSTRATESRC", "A1"."STRATEGY_ID", "A1 "."USDAMOUNT", "A1"."LOCAMOUNT", "A1"."RELGENSYS_DEALID", "A1"."IMPORTFLAG", "A1"."PLRELDEAL_ID", "A1"."ORIGCLOSERATE", "A1"."PTS", "A1"."SENDCLOSE_STATUS", "A1"."ISCLOSE" FROM (SELECT DISTINCT "A3"."DEAL_ID" "DEAL_ID" FROM "QE_P"."TQE_PS_DEALS" "A5", "QE_P"."TQE_PS_CLOSESTRATEGY" "A4", "QE_P"."TQE_PS_CLOSESTRATEGY" "A3" WHERE "A3"."DTYPE" = '1' AND "A3"."STRATEGY_ID" = "A4"."STRATEGY_ID" AND "A4"."DEAL_ID" = "A5"."DEAL_ID" AND "A5"."GENSYS" = :1 AND "A5"."DEALDATE_CP" <= :2 AND "A5"."CPTY_BANK" = :3) "A2", "QE_P"."TQE_PS_C LOSEDEALS" "A1" WHERE "A1"."DEAL_ID" = "A2"."DEAL_ID" Plan hash value: 2270864230 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 93120 (100)| | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 81 | 15471 | 93120 (1)| 00:18:38 | | 3 | VIEW | | 81 | 648 | 93038 (1)| 00:18:37 | | 4 | HASH UNIQUE | | 81 | 4860 | 93038 (1)| 00:18:37 | | 5 | NESTED LOOPS | | | | | | | 6 | NESTED LOOPS | | 81 | 4860 | 93037 (1)| 00:18:37 | |* 7 | HASH JOIN | | 8 | 312 | 93013 (1)| 00:18:37 | |* 8 | TABLE ACCESS BY INDEX ROWID| TQE_PS_DEALS | 6 | 120 | 91771 (1)| 00:18:22 | |* 9 | INDEX RANGE SCAN | TQE_PS_DEALS_IDX_INITDEAL2 | 682 | | 91572 (1)| 00:18:19 | | 10 | INDEX FAST FULL SCAN | TQE_PS_CLOSESTRATEGY_UIDX | 898K| 16M| 1237 (1)| 00:00:15 | |* 11 | INDEX RANGE SCAN | TQE_PS_CLOSESTRATEGY_UIDX | 11 | | 2 (0)| 00:00:01 | |* 12 | TABLE ACCESS BY INDEX ROWID | TQE_PS_CLOSESTRATEGY | 10 | 210 | 3 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | TQE_PS_CLOSEDEALS_IDX | 1 | | 1 (0)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID | TQE_PS_CLOSEDEALS | 1 | 183 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$2 / A2@SEL$1 4 - SEL$2 8 - SEL$2 / A5@SEL$2 9 - SEL$2 / A5@SEL$2 10 - SEL$2 / A4@SEL$2 11 - SEL$2 / A3@SEL$2 12 - SEL$2 / A3@SEL$2 13 - SEL$1 / A1@SEL$1 14 - SEL$1 / A1@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none') OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "A2"@"SEL$1") INDEX(@"SEL$1" "A1"@"SEL$1" ("TQE_PS_CLOSEDEALS"."DEAL_ID")) LEADING(@"SEL$1" "A2"@"SEL$1" "A1"@"SEL$1") USE_NL(@"SEL$1" "A1"@"SEL$1") NLJ_BATCHING(@"SEL$1" "A1"@"SEL$1") INDEX_RS_ASC(@"SEL$2" "A5"@"SEL$2" ("TQE_PS_DEALS"."DEALDATE_CP" "TQE_PS_DEALS"."CPTY_BANK" "TQE_PS_DEALS"."ORG_ID")) INDEX_FFS(@"SEL$2" "A4"@"SEL$2" ("TQE_PS_CLOSESTRATEGY"."STRATEGY_ID" "TQE_PS_CLOSESTRATEGY"."DEAL_ID" "TQE_PS_CLOSESTRATEGY"."CYCODE")) INDEX(@"SEL$2" "A3"@"SEL$2" ("TQE_PS_CLOSESTRATEGY"."STRATEGY_ID" "TQE_PS_CLOSESTRATEGY"."DEAL_ID" "TQE_PS_CLOSESTRATEGY"."CYCODE")) LEADING(@"SEL$2" "A5"@"SEL$2" "A4"@"SEL$2" "A3"@"SEL$2") USE_HASH(@"SEL$2" "A4"@"SEL$2") USE_NL(@"SEL$2" "A3"@"SEL$2") NLJ_BATCHING(@"SEL$2" "A3"@"SEL$2") USE_HASH_AGGREGATION(@"SEL$2") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - :1 (VARCHAR2(30), CSID=873): 'GTS' 2 - :2 (NUMBER): 20180916 3 - :3 (VARCHAR2(30), CSID=873): '223' --gensys为gts、id为223的值在表中占比很少，因此在访问表的时候采用索引是合适的。 Predicate Information (identified by operation id): --------------------------------------------------- 7 - access("A4"."DEAL_ID"="A5"."DEAL_ID") 8 - filter("A5"."GENSYS"=:1) 9 - access("A5"."CPTY_BANK"=:3 AND "A5"."DEALDATE_CP"<=:2) filter("A5"."CPTY_BANK"=:3) 11 - access("A3"."STRATEGY_ID"="A4"."STRATEGY_ID") 12 - filter("A3"."DTYPE"='1') 13 - access("A1"."DEAL_ID"="A2"."DEAL_ID") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A1"."DEAL_ID"[VARCHAR2,12], "A1"."DTYPE"[CHARACTER,1], "A1"."DFLAG"[CHARACTER,1], "A1"."BANK_ID"[VARCHAR2,3], "A1"."ORG_ID"[VARCHAR2,9], "A1"."ORG2_ID"[VARCHAR2,9], "A1"."CPTY"[VARCHAR2,22], "A1"."CPTY_BANK"[VARCHAR2,3], "A1"."GENSYS"[VARCHAR2,10], "A1"."GENSYS_DEALID"[VARCHAR2,20], "A1"."SYSTEMDATE"[NUMBER,22], "A1"."SYSTEMTIME"[NUMBER,22], "A1"."DEALDATE"[NUMBER,22], "A1"."DEALTIME"[NUMBER,22], "A1"."DEALDATE_CP"[NUMBER,22], "A1"."DEALTIME_CP"[NUMBER,22], "A1"."ACCOUNTDATE"[NUMBER,22], "A1"."VALUEDATE"[NUMBER,22], "A1"."VALUEDATE_CP"[NUMBER,22], "A1"."CTYPE"[CHARACTER,1], "A1"."MTYPE"[CHARACTER,1], "A1"."CYCODE1"[VARCHAR2,3], "A1"."CYCODE2"[VARCHAR2,3], "A1"."BUYSELL"[CHARACTER,1], "A1"."AMOUNT1"[NUMBER,22], "A1"."AMOUNT2"[NUMBER,22], "A1"."SPOTRATE"[NUMBER,22], "A1"."CLIENTRATE"[NUMBER,22], "A1"."OPENRATE"[NUMBER,22], "A1"."ACCOUNTTYPE"[CHARACTER,2], "A1"."ORDERTYPE"[CHARACTER,2], "A1"."TERMINATION"[CHARACTER,1], "A1"."TRADERID"[VARCHAR2,20], "A1"."IMPORTTYPE"[CHARACTER,1], "A1"."FLAG1"[CHARACTER,1], "A1"."APPID"[CHARACTER,1], "A1"."QUANTITY"[NUMBER,22], "A1"."SUMTYPE"[CHARACTER,1], "A1"."CKADJUST_ID"[VARCHAR2,12], "A1"."CLOSERESULT"[VARCHAR2,2], "A1"."ACCOUNT"[VARCHAR2,17], "A1"."MCYCODE"[VARCHAR2,3], "A1"."OPENAMOUNT1"[NUMBER,22], "A1"."OPENAMOUNT2"[NUMBER,22], "A1"."PLFLAG"[CHARACTER,1], "A1"."PLORIGCYCODE"[VARCHAR2,3], "A1"."PLORIGAMOUNT"[NUMBER,22], "A1"."PLCYCODE"[VARCHAR2,3], "A1"."PLAMOUNT"[NUMBER,22], "A1"."PLRATE"[NUMBER,22], "A1"."SUMDEAL_ID"[VARCHAR2,12], "A1"."COSTRATESRC"[CHARACTER,1], "A1"."STRATEGY_ID"[VARCHAR2,12], "A1"."USDAMOUNT"[NUMBER,22], "A1"."LOCAMOUNT"[NUMBER,22], "A1"."RELGENSYS_DEALID"[VARCHAR2,20], "A1"."IMPORTFLAG"[NUMBER,22], "A1"."PLRELDEAL_ID"[VARCHAR2,12], "A1"."ORIGCLOSERATE"[NUMBER,22], "A1"."PTS"[NUMBER,22], "A1"."SENDCLOSE_STATUS"[CHARACTER,1], "A1"."ISCLOSE"[NUMBER,22] 2 - "A1".ROWID[ROWID,10], "A1"."DEAL_ID"[VARCHAR2,12] 3 - "A2"."DEAL_ID"[VARCHAR2,12] 4 - "A3"."DEAL_ID"[VARCHAR2,12] 5 - "A3"."DEAL_ID"[VARCHAR2,12] 6 - "A3".ROWID[ROWID,10], "A3"."DEAL_ID"[VARCHAR2,12] 7 - (#keys=1) "A4"."STRATEGY_ID"[VARCHAR2,12] 8 - "A5"."DEAL_ID"[VARCHAR2,12] 9 - "A5".ROWID[ROWID,10] 10 - "A4"."STRATEGY_ID"[VARCHAR2,12], "A4"."DEAL_ID"[VARCHAR2,12] 11 - "A3".ROWID[ROWID,10], "A3"."DEAL_ID"[VARCHAR2,12] 13 - "A1".ROWID[ROWID,10], "A1"."DEAL_ID"[VARCHAR2,12] 14 - "A1"."DTYPE"[CHARACTER,1], "A1"."DFLAG"[CHARACTER,1], "A1"."BANK_ID"[VARCHAR2,3], "A1"."ORG_ID"[VARCHAR2,9], "A1"."ORG2_ID"[VARCHAR2,9], "A1"."CPTY"[VARCHAR2,22], "A1"."CPTY_BANK"[VARCHAR2,3], "A1"."GENSYS"[VARCHAR2,10], "A1"."GENSYS_DEALID"[VARCHAR2,20], "A1"."SYSTEMDATE"[NUMBER,22], "A1"."SYSTEMTIME"[NUMBER,22], "A1"."DEALDATE"[NUMBER,22], "A1"."DEALTIME"[NUMBER,22], "A1"."DEALDATE_CP"[NUMBER,22], "A1"."DEALTIME_CP"[NUMBER,22], "A1"."ACCOUNTDATE"[NUMBER,22], "A1"."VALUEDATE"[NUMBER,22], "A1"."VALUEDATE_CP"[NUMBER,22], "A1"."CTYPE"[CHARACTER,1], "A1"."MTYPE"[CHARACTER,1], "A1"."CYCODE1"[VARCHAR2,3], "A1"."CYCODE2"[VARCHAR2,3], "A1"."BUYSELL"[CHARACTER,1], "A1"."AMOUNT1"[NUMBER,22], "A1"."AMOUNT2"[NUMBER,22], "A1"."SPOTRATE"[NUMBER,22], "A1"."CLIENTRATE"[NUMBER,22], "A1"."OPENRATE"[NUMBER,22], "A1"."ACCOUNTTYPE"[CHARACTER,2], "A1"."ORDERTYPE"[CHARACTER,2], "A1"."TERMINATION"[CHARACTER,1], "A1"."TRADERID"[VARCHAR2,20], "A1"."IMPORTTYPE"[CHARACTER,1], "A1"."FLAG1"[CHARACTER,1], "A1"."APPID"[CHARACTER,1], "A1"."QUANTITY"[NUMBER,22], "A1"."SUMTYPE"[CHARACTER,1], "A1"."CKADJUST_ID"[VARCHAR2,12], "A1"."CLOSERESULT"[VARCHAR2,2], "A1"."ACCOUNT"[VARCHAR2,17], "A1"."MCYCODE"[VARCHAR2,3], "A1"."OPENAMOUNT1"[NUMBER,22], "A1"."OPENAMOUNT2"[NUMBER,22], "A1"."PLFLAG"[CHARACTER,1], "A1"."PLORIGCYCODE"[VARCHAR2,3], "A1"."PLORIGAMOUNT"[NUMBER,22], "A1"."PLCYCODE"[VARCHAR2,3], "A1"."PLAMOUNT"[NUMBER,22], "A1"."PLRATE"[NUMBER,22], "A1"."SUMDEAL_ID"[VARCHAR2,12], "A1"."COSTRATESRC"[CHARACTER,1], "A1"."STRATEGY_ID"[VARCHAR2,12], "A1"."USDAMOUNT"[NUMBER,22], "A1"."LOCAMOUNT"[NUMBER,22], "A1"."RELGENSYS_DEALID"[VARCHAR2,20], "A1"."IMPORTFLAG"[NUMBER,22], "A1"."PLRELDEAL_ID"[VARCHAR2,12], "A1"."ORIGCLOSERATE"[NUMBER,22], "A1"."PTS"[NUMBER,22], "A1"."SENDCLOSE_STATUS"[CHARACTER,1], "A1"."ISCLOSE"[NUMBER,22] 160 rows selected.
2.
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : QE_P (2933:9149)
SQL ID : 5vxuubbx3hqqv
SQL Execution ID : 16791839
Execution Started : 09/17/2018 08:34:37
First Refresh Time : 09/17/2018 08:34:41
Last Refresh Time : 09/17/2018 10:53:06
Duration : 8309s
Module/Action : oracle@QEDB2 (TNS V1-V3)/-
Service : oraQEB
Program : oracle@QEDB2 (TNS V1-V3)
Fetch Calls : 1
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :1 | 1 | VARCHAR2(32) | XPADG |
| :2 | 2 | NUMBER | 20180916 |
| :3 | 3 | VARCHAR2(32) | 003 |
========================================================================================================================
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 8352 | 723 | 6184 | 1445 | 1 | 17M | 9M | 69GB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=2270864230)
=======================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=======================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | |
| 1 | NESTED LOOPS | | | | | | 1 | | | | | | |
| 2 | NESTED LOOPS | | 102 | 93346 | | | 1 | | | | | | |
| 3 | VIEW | | 102 | 93243 | | | 1 | | | | | | |
| 4 | HASH UNIQUE | | 102 | 93243 | | | 1 | | | | | | |
| 5 | NESTED LOOPS | | | | | | 1 | | | | | | |
| 6 | NESTED LOOPS | | 102 | 93242 | | | 1 | | | | | | |
| 7 | HASH JOIN | | 10 | 93212 | 8306 | +4 | 1 | 0 | | | 34M | 0.01 | Cpu (1) |
| 8 | TABLE ACCESS BY INDEX ROWID | TQE_PS_DEALS | 8 | 91970 | 8309 | +1 | 1 | 658K | 9M | 68GB | | 99.14 | gc cr grant 2-way (1167) |
| | | | | | | | | | | | | | gc cr request (8) |
| | | | | | | | | | | | | | gc current block 2-way (231) |
| | | | | | | | | | | | | | Cpu (750) |
| | | | | | | | | | | | | | db file sequential read (6071) |
| 9 | INDEX RANGE SCAN | TQE_PS_DEALS_IDX_INITDEAL2 | 1363 | 91572 | 8306 | +4 | 1 | 21M | 1964 | 15MB | | 0.83 | gc current block 2-way (37) |
| | | | | | | | | | | | | | Cpu (32) |
| 10 | INDEX FAST FULL SCAN | TQE_PS_CLOSESTRATEGY_UIDX | 898K | 1237 | 1 | +8309 | 1 | 985K | | | | | |
| 11 | INDEX RANGE SCAN | TQE_PS_CLOSESTRATEGY_UIDX | 11 | 2 | | | | | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | TQE_PS_CLOSESTRATEGY | 10 | 3 | | | | | | | | | |
| 13 | INDEX UNIQUE SCAN | TQE_PS_CLOSEDEALS_IDX | 1 | 1 | | | | | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | TQE_PS_CLOSEDEALS | 1 | 2 | | | | | | | | | |
=======================================================================================================================================================================================================
3.数据量分布
SQL> select /*+ parallel(q 10) */ GENSYS,CPTY_BANK,count(*) from QE_P.TQE_PS_DEALS t group by GENSYS,CPTY_BANK order by
2 2,3;
GENSYS CPTY_B COUNT(*)
-------------------- ------ ----------
PCTS 003 30
SCFS 003 1997
BOCS 003 9788
QE 003 51832
ITGS 003 179466
GTS 003 233311
XPADG 003 658509
NFXS 003 8534313
BANCS 003 11578700
NFXS 101 193
GTS 101 571
BOCS 101 42647
GTS 102 1437
ITGS 102 1447
BOCS 102 26166
ITGS 103 1913
BOCS 103 199229
ITGS 104 774
GTS 104 1255
BOCS 104 116115
GTS 105 3511
BOCS 105 63066
ITGS 106 269
GTS 106 320
BOCS 106 14555
ITGS 107 5
GTS 107 97
BOCS 107 22795
ITGS 108 6
GTS 108 1104
BOCS 108 12719
GTS 109 58
ITGS 109 1212
BOCS 109 77285
ITGS 110 378
BOCS 110 31603
BOCS 111 377
ITGS 111 738
ITGS 112 929
BOCS 112 67772
GTS 114 1
BOCS 114 147
GTS 115 19
BOCS 115 2129
GTS 118 63
ITGS 118 108
BOCS 118 476
BOCS 123 10762
BOCS 125 105
BOCS 126 89
NFXS 138 396
GTS 138 858
MGS 138 8817
NHG 138 10648
BOCS 138 472779
GTS 201 4
BOCS 201 1084
ITGS 202 45
BOCS 202 25336
ITGS 203 4
GTS 203 79
BOCS 203 9567
NFXS 204 3
BOCS 204 10962
ITGS 205 24
BOCS 205 18020
ITGS 206 669
BOCS 206 9419
GTS 207 62
ITGS 207 1498
BOCS 207 5479
GTS 208 10
ITGS 208 521
BOCS 208 13072
ITGS 209 361
BOCS 209 2050
BOCS 210 3
ITGS 210 9
BOCS 211 1511
GTS 212 1
ITGS 212 632
BOCS 212 2375
ITGS 213 446
BOCS 213 1851
ITGS 214 36
BOCS 214 538
ITGS 215 37
BOCS 215 170
BOCS 217 279
BOCS 218 58
BOCS 219 18
BOCS 220 23
BOCS 221 21
ITGS 222 2
BOCS 222 78
BOCS 224 30
GTS 302 16
BOCS 302 21038
BOCS 303 34
BOCS 304 651
BOCS 307 1
BOCS 308 111
GTS 999 4
ITGS 999 573
BOCS 999 4019
QE 999 33288
PCTS 30
SCFS 2781
NHG 11819
MGS 36516
GTS 355363
ITGS 1048212
XPADG 1294811
BOCS 5133340
NFXS 13256614
BANCS 87685132
源文件见：E:\TS\201809\dayu0918\qc
