Here is the Gather Plan.
Is there any wrong ? why FULL TABLE SCAN showing on iby_pmt_instr_uses_all ipiua table ? is this because of wrong handle filter condition (AND ipiua.end_date IS NULL)
37 - filter(("OH"."CREATION_DATE">=TO_DATE(' 2018-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "OH"."ORG_ID"=TO_NUMBER(:P_ORG_ID) AND "OH"."CREATION_DATE"<=TO_DATE(' 2018-07-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 501 |00:04:19.92 | 18M| 793K| | | |
|* 1 | FILTER | | 1 | | 501 |00:04:19.92 | 18M| 793K| | | |
| 2 | SORT GROUP BY | | 1 | 1 | 2531 |00:04:19.92 | 18M| 793K| 72M| 7372K| 64M (0)|
|* 3 | FILTER | | 1 | | 912K|00:04:17.27 | 18M| 793K| | | |
| 4 | NESTED LOOPS | | 1 | 1 | 8375K|00:04:14.92 | 18M| 793K| | | |
| 5 | NESTED LOOPS | | 1 | 1 | 8376K|00:04:05.04 | 17M| 793K| | | |
|* 6 | FILTER | | 1 | | 8376K|00:03:53.44 | 17M| 793K| | | |
| 7 | NESTED LOOPS OUTER | | 1 | 1 | 8376K|00:03:49.88 | 17M| 793K| | | |
| 8 | NESTED LOOPS | | 1 | 1 | 8376K|00:03:12.10 | 17M| 793K| | | |
| 9 | NESTED LOOPS | | 1 | 1 | 1063K|00:02:56.17 | 8707K| 793K| | | |
| 10 | NESTED LOOPS OUTER | | 1 | 1 | 1063K|00:02:52.21 | 8413K| 793K| | | |
| 11 | NESTED LOOPS | | 1 | 1 | 1063K|00:02:47.26 | 5223K| 793K| | | |
| 12 | NESTED LOOPS | | 1 | 1 | 131K|00:02:44.16 | 3962K| 793K| | | |
| 13 | NESTED LOOPS | | 1 | 1 | 27141 |00:02:43.72 | 3776K| 793K| | | |
| 14 | NESTED LOOPS OUTER | | 1 | 1 | 27155 |00:02:43.46 | 3691K| 793K| | | |
| 15 | NESTED LOOPS | | 1 | 1 | 27155 |00:02:43.34 | 3635K| 793K| | | |
| 16 | NESTED LOOPS | | 1 | 1 | 32167 |00:02:43.15 | 3537K| 793K| | | |
| 17 | NESTED LOOPS | | 1 | 1 | 224K|00:02:42.15 | 3125K| 793K| | | |
| 18 | NESTED LOOPS | | 1 | 1 | 224K|00:02:41.29 | 2510K| 793K| | | |
| 19 | NESTED LOOPS | | 1 | 5 | 224K|00:02:40.36 | 1927K| 793K| | | |
|* 20 | HASH JOIN | | 1 | 5 | 224K|00:02:39.49 | 1409K| 793K| 47M| 4109K| 48M (0)|
| 21 | NESTED LOOPS | | 1 | 13 | 203K|00:02:21.20 | 1323K| 707K| | | |
| 22 | NESTED LOOPS | | 1 | 13 | 203K|00:02:20.84 | 1169K| 707K| | | |
|* 23 | HASH JOIN | | 1 | 13 | 254K|00:02:19.85 | 708K| 707K| 840K| 840K| 430K (0)|
| 24 | MERGE JOIN CARTESIAN | | 1 | 1 | 1 |00:00:00.01 | 13 | 0 | | | |
| 25 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 11 | 0 | | | |
| 26 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 | 0 | | | |
| 27 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 | 0 | | | |
| 28 | TABLE ACCESS BY INDEX ROWID| HR_ALL_ORGANIZATION_UNITS_TL | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | | | |
|* 29 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
|* 30 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | OE_HOLD_DEFINITIONS | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
|* 32 | INDEX UNIQUE SCAN | OE_HOLDS_U1 | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | | | |
|* 33 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 1 | 1 |00:00:00.01 | 4 | 0 | | | |
|* 34 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 1 | 3 |00:00:00.01 | 2 | 0 | | | |
| 35 | BUFFER SORT | | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 73728 | 73728 | |
|* 36 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
|* 37 | TABLE ACCESS FULL | OE_ORDER_HEADERS_ALL | 1 | 37914 | 254K|00:02:19.70 | 708K| 707K| | | |
|* 38 | INDEX RANGE SCAN | IBY_EXTERNAL_PAYERS_ALL_SU_N3 | 254K| 1 | 203K|00:00:00.90 | 461K| 0 | | | |
| 39 | TABLE ACCESS BY INDEX ROWID | IBY_EXTERNAL_PAYERS_ALL | 203K| 1 | 203K|00:00:00.28 | 154K| 0 | | | |
|* 40 | TABLE ACCESS FULL | IBY_PMT_INSTR_USES_ALL | 1 | 5503K| 5517K|00:00:16.14 | 85981 | 85965 | | | |
| 41 | TABLE ACCESS BY INDEX ROWID | IBY_CREDITCARD | 224K| 1 | 224K|00:00:00.77 | 517K| 0 | | | |
|* 42 | INDEX UNIQUE SCAN | IBY_CREDITCARD_INSTRID_U1 | 224K| 1 | 224K|00:00:00.47 | 292K| 0 | | | |
| 43 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 224K| 1 | 224K|00:00:00.85 | 583K| 0 | | | |
|* 44 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 224K| 1 | 224K|00:00:00.57 | 358K| 0 | | | |
| 45 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 224K| 1 | 224K|00:00:00.76 | 615K| 0 | | | |
|* 46 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 224K| 1 | 224K|00:00:00.47 | 390K| 0 | | | |
|* 47 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HOLDS_ALL | 224K| 1 | 32167 |00:00:00.94 | 411K| 0 | | | |
|* 48 | INDEX RANGE SCAN | OE_ORDER_HOLDS_ALL_N1 | 224K| 1 | 32167 |00:00:00.62 | 383K| 0 | | | |
|* 49 | TABLE ACCESS BY INDEX ROWID | OE_HOLD_SOURCES_ALL | 32167 | 1 | 27155 |00:00:00.18 | 97986 | 0 | | | |
|* 50 | INDEX UNIQUE SCAN | OE_HOLD_SOURCES_U1 | 32167 | 1 | 32167 |00:00:00.11 | 63340 | 0 | | | |
| 51 | TABLE ACCESS BY INDEX ROWID | OE_HOLD_RELEASES | 27155 | 1 | 19531 |00:00:00.10 | 55333 | 0 | | | |
|* 52 | INDEX UNIQUE SCAN | OE_HOLD_RELEASES_U1 | 27155 | 1 | 19531 |00:00:00.07 | 35802 | 0 | | | |
| 53 | TABLE ACCESS BY INDEX ROWID | FLT_PAYMENTS | 27155 | 2 | 27141 |00:00:00.25 | 85131 | 0 | | | |
|* 54 | INDEX RANGE SCAN | FLT_PAYMENTS_IDX_N4 | 27155 | 2 | 27141 |00:00:00.16 | 54484 | 0 | | | |
| 55 | TABLE ACCESS BY INDEX ROWID | FLT_PAYMENT_ATTEMPTS | 27141 | 7 | 131K|00:00:00.40 | 185K| 0 | | | |
|* 56 | INDEX RANGE SCAN | FLT_PAY_ATTPT_IDX1 | 27141 | 7 | 131K|00:00:00.20 | 54750 | 0 | | | |
|* 57 | TABLE ACCESS BY INDEX ROWID | IBY_TRXN_SUMMARIES_ALL | 131K| 1 | 1063K|00:00:02.87 | 1261K| 0 | | | |
|* 58 | INDEX RANGE SCAN | IBY_TRXN_SUMM_TANGIBLEID_N2 | 131K| 2 | 1063K|00:00:00.84 | 198K| 0 | | | |
| 59 | TABLE ACCESS BY INDEX ROWID | IBY_TRXN_CORE | 1063K| 1 | 1063K|00:00:04.37 | 3189K| 0 | | | |
|* 60 | INDEX UNIQUE SCAN | IBY_TRXN_CORE_TRXNMID_U1 | 1063K| 1 | 1063K|00:00:03.07 | 2126K| 0 | | | |
|* 61 | TABLE ACCESS BY INDEX ROWID | IBY_FNDCPT_TX_OPERATIONS | 1063K| 1 | 1063K|00:00:03.51 | 293K| 0 | | | |
|* 62 | INDEX RANGE SCAN | IBY_FNDCPT_TX_OPERATIONS_N1 | 1063K| 1 | 1063K|00:00:01.72 | 268K| 0 | | | |
| 63 | TABLE ACCESS BY INDEX ROWID | FLT_PAYMENT_ATTEMPTS | 1063K| 4 | 8376K|00:00:14.11 | 8656K| 0 | | | |
|* 64 | INDEX RANGE SCAN | FLT_TANGIBLEID | 1063K| 7 | 8376K|00:00:03.56 | 283K| 0 | | | |
|* 65 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 8376K| 1 | 8159K|00:00:34.35 | 3500 | 0 | | | |
|* 66 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U2 | 8376K| 1 | 8159K|00:00:22.40 | 1469 | 0 | | | |
|* 67 | INDEX RANGE SCAN | OE_PAYMENTS_U1 | 8376K| 1 | 8376K|00:00:08.20 | 355K| 0 | | | |
|* 68 | TABLE ACCESS BY INDEX ROWID | OE_PAYMENTS | 8376K| 1 | 8375K|00:00:06.76 | 377K| 0 | | | |
| 69 | SORT AGGREGATE | | 25459 | 1 | 25459 |00:00:00.50 | 209K| 0 | | | |
| 70 | TABLE ACCESS BY INDEX ROWID | IBY_TRXN_SUMMARIES_ALL | 25459 | 2 | 158K|00:00:00.44 | 209K| 0 | | | |
|* 71 | INDEX RANGE SCAN | IBY_TRXN_SUMM_TRANS_ID_N1 | 25459 | 2 | 158K|00:00:00.20 | 51414 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FPA"."ATTEMPT"=MAX("FPA1"."ATTEMPT"))
3 - filter("ITS"."TRXNMID"=)
6 - filter((NVL("VL"."START_DATE_ACTIVE",SYSDATE@!)<=SYSDATE@! AND NVL("VL"."END_DATE_ACTIVE",SYSDATE@!)>=SYSDATE@!))
20 - access("IPIUA"."EXT_PMT_PARTY_ID"="IEPA"."EXT_PAYER_ID")
23 - access("O"."ORGANIZATION_ID"="OH"."ORG_ID")
29 - access("OTL"."ORGANIZATION_ID"=TO_NUMBER(:P_ORG_ID) AND "OTL"."LANGUAGE"=USERENV('LANG'))
30 - access("O"."ORGANIZATION_ID"=TO_NUMBER(:P_ORG_ID))
32 - access("OHD"."HOLD_ID"=11)
33 - filter(("O2"."ORG_INFORMATION1"='OPERATING_UNIT' AND "O2"."ORG_INFORMATION2"='Y'))
34 - access("O2"."ORGANIZATION_ID"=TO_NUMBER(:P_ORG_ID))
filter("O2"."ORG_INFORMATION_CONTEXT"||''='CLASS')
36 - access("O3"."ORGANIZATION_ID"=TO_NUMBER(:P_ORG_ID) AND "O3"."ORG_INFORMATION_CONTEXT"='Operating Unit Information')
37 - filter(("OH"."CREATION_DATE">=TO_DATE(' 2018-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "OH"."ORG_ID"=TO_NUMBER(:P_ORG_ID) AND "OH"."CREATION_DATE"<=TO_DATE('
2018-07-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
38 - access("IEPA"."ACCT_SITE_USE_ID"="OH"."INVOICE_TO_ORG_ID")
filter("IEPA"."ACCT_SITE_USE_ID" IS NOT NULL)
40 - filter("IPIUA"."END_DATE" IS NULL)
42 - access("IPIUA"."INSTRUMENT_ID"="IC"."INSTRID")
44 - access("IEPA"."CUST_ACCOUNT_ID"="HCA"."CUST_ACCOUNT_ID")
filter("OH"."SOLD_TO_ORG_ID"="HCA"."CUST_ACCOUNT_ID")
46 - access("HCA"."PARTY_ID"="HP"."PARTY_ID")
47 - filter("OOH"."RELEASED_FLAG"=NVL(DECODE(:P_HOLD,'Y','N','N','Y',NULL),"OOH"."RELEASED_FLAG"))
48 - access("OOH"."HEADER_ID"="OH"."HEADER_ID")
49 - filter("OHS"."HOLD_ID"=11)
50 - access("OOH"."HOLD_SOURCE_ID"="OHS"."HOLD_SOURCE_ID")
52 - access("OOH"."HOLD_RELEASE_ID"="HR"."HOLD_RELEASE_ID")
54 - access("OH"."HEADER_ID"="FP"."HEADER_ID")
56 - access("OH"."HEADER_ID"="FPA"."HEADER_ID")
57 - filter("ITS"."INITIATOR_EXTENSION_ID" IS NOT NULL)
58 - access("ITS"."TANGIBLEID"="FPA"."TANGIBLEID")
60 - access("ITS"."TRXNMID"="ITC"."TRXNMID")
61 - filter("IFTO"."TRANSACTIONID"="ITS"."TRANSACTIONID")
62 - access("IFTO"."TRXN_EXTENSION_ID"="ITS"."INITIATOR_EXTENSION_ID")
64 - access("FPA1"."TANGIBLEID"="FPA"."TANGIBLEID")
65 - filter("VL"."ENABLED_FLAG"='Y')
66 - access("VL"."LOOKUP_TYPE"='FLT_GC_ERRORS' AND "VL"."MEANING"="ITS"."BEPCODE")
filter("VL"."MEANING"="ITS"."BEPCODE")
67 - access("OP"."HEADER_ID"="OH"."HEADER_ID")
68 - filter("OP"."TRXN_EXTENSION_ID"="IFTO"."TRXN_EXTENSION_ID")
71 - access("ITS1"."TRANSACTIONID"=:B1)