Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query taking long time even though no rows

Rajesh123Jul 5 2018 — edited Jul 5 2018

Hi All,

I am having 4 union clauses and looks like below query is taking 20 sec (it is returning no rows).

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL_ID 9qq54aatpp7p7, child number 0

-------------------------------------

SELECT /*+ gather_plan_statistics */

cust.party_name customer_Party_name,

ca.account_number,

ca.attribute1,

rtt.name,

(SELECT address1

|| ' '

|| loc.city

|| ' '

|| loc.state

|| '-'

|| loc.postal_code

FROM apps.hz_locations loc,

apps.hz_party_sites hps

WHERE loc.location_id = hps.location_id

AND hps.party_site_id = cas.party_site_id

AND hps.end_date_active IS NULL

AND loc.country = 'GB') addr,

RM.name receipt_method_name,

Cust_Rec_M.primary_flag receipt_method_primary,

bank.party_name bank_name,

piu.start_date,

piu.end_date,

piu.order_of_preference,

eba.bank_account_num,

eba.bank_account_name,

NULL,

Nvl2(nvl(piu.end_date, SYSDATE), 'Inactive', 'Active'),

NULL,

NULL ,

cas.org_id

FROM apps.hz_cust_site_uses_all csu,

apps.iby_external_payers_all epa,

apps.iby_pmt_instr_uses_all piu,

apps.iby_ext_bank_accounts eba,

apps.hz_cust_acct_sites_all cas,

apps.hz_cust_accounts ca,

apps.ra_cust_receipt_methods Cust_Rec_M,

apps.ar_receipt_methods RM,

apps.hz_parties bank,

apps.hz_parties cust,

apps.ra_terms_tl rtt

WHERE 1 = 1

AND cas.cust_account_id = ca.cust_account_id

AND csu.cust_acct_site_id = cas.cust_acct_site_id

AND epa.acct_site_use_id = csu.site_use_id

AND piu.ext_pmt_party_id = epa.ext_payer_id

AND piu.payment_function = 'CUSTOMER_PAYMENT'

AND eba.ext_bank_account_id = piu.instrument_id

AND bank.party_id = eba.bank_id

AND Cust_Rec_M.customer_id = ca.cust_account_id

AND RM.receipt_method_id = Cust_Rec_M.receipt_method_id

AND csu.site_use_id = Cust_Rec_M.site_use_id

AND cas.org_id IN ( 941, 935, 934 )

AND Cust_Rec_M.primary_flag = 'Y'

AND cust.party_id = ca.party_id

AND csu.site_use_code = 'BILL_TO'

AND cust.status = 'A'

AND cas.status = 'A'

AND csu.status = 'A'

AND ca.payment_term_id = rtt.term_id

AND rtt.name IN( 'XXXXXXXXXX' ,'XXXXXXXXXX')

AND trunc(Nvl(piu.end_date, SYSDATE)) >= Trunc(SYSDATE) --Active only

AND rm.name = 'XXXXXXXXXX';

Plan hash value: 2852533631

--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:27.97 | 211K| 210K|

| 1 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 2 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 3 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 4 | TABLE ACCESS BY INDEX ROWID | HZ_LOCATIONS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 5 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

| 6 | NESTED LOOPS | | 1 | | 0 |00:00:27.97 | 211K| 210K|

| 7 | NESTED LOOPS | | 1 | 1 | 0 |00:00:27.97 | 211K| 210K|

| 8 | NESTED LOOPS | | 1 | 1 | 0 |00:00:27.97 | 211K| 210K|

| 9 | NESTED LOOPS | | 1 | 1 | 0 |00:00:27.97 | 211K| 210K|

|* 10 | HASH JOIN | | 1 | 1 | 0 |00:00:27.97 | 211K| 210K|

| 11 | NESTED LOOPS | | 1 | | 0 |00:00:27.97 | 211K| 210K|

| 12 | NESTED LOOPS | | 1 | 1 | 0 |00:00:27.97 | 211K| 210K|

| 13 | NESTED LOOPS | | 1 | 1 | 0 |00:00:27.97 | 211K| 210K|

| 14 | NESTED LOOPS | | 1 | 9452 | 0 |00:00:27.97 | 211K| 210K|

|* 15 | HASH JOIN | | 1 | 16583 | 12 |00:00:09.35 | 211K| 210K|

|* 16 | TABLE ACCESS FULL | RA_TERMS_TL | 1 | 10 | 9 |00:00:00.01 | 15 | 0 |

|* 17 | HASH JOIN | | 1 | 21559 | 102 |00:00:07.98 | 211K| 210K|

| 18 | NESTED LOOPS | | 1 | 37686 | 284 |00:00:02.81 | 39269 | 39209 |

| 19 | TABLE ACCESS BY INDEX ROWID| AR_RECEIPT_METHODS | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |

|* 20 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U2 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |

|* 21 | TABLE ACCESS FULL | RA_CUST_RECEIPT_METHODS | 1 | 37686 | 284 |00:00:02.81 | 39266 | 39209 |

|* 22 | TABLE ACCESS FULL | HZ_CUST_ACCOUNTS | 1 | 1027K| 1028K|00:00:15.35 | 172K| 171K|

|* 23 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 12 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 24 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 12 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 25 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 26 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 27 | INDEX RANGE SCAN | IBY_EXTERNAL_PAYERS_ALL_SU_N3 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

| 28 | TABLE ACCESS BY INDEX ROWID | IBY_EXTERNAL_PAYERS_ALL | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 29 | TABLE ACCESS FULL | IBY_PMT_INSTR_USES_ALL | 0 | 122K| 0 |00:00:00.01 | 0 | 0 |

| 30 | TABLE ACCESS BY INDEX ROWID | IBY_EXT_BANK_ACCOUNTS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 31 | INDEX UNIQUE SCAN | IBY_EXT_BANK_ACCOUNTS_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 32 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 33 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

|* 34 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

| 35 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("HPS"."END_DATE_ACTIVE" IS NULL)

3 - access("HPS"."PARTY_SITE_ID"=:B1)

4 - filter("LOC"."COUNTRY"='GB')

5 - access("LOC"."LOCATION_ID"="HPS"."LOCATION_ID")

10 - access("PIU"."EXT_PMT_PARTY_ID"="EPA"."EXT_PAYER_ID")

15 - access("CA"."PAYMENT_TERM_ID"="TERM_ID")

16 - filter((INTERNAL_FUNCTION("NAME") AND NVL("ZD_EDITION_NAME",'ORA$BASE')='ORA$BASE'))

17 - access("CUST_REC_M"."CUSTOMER_ID"="CA"."CUST_ACCOUNT_ID")

20 - access("NAME"='Direct Debit UK GBP BofA' AND "ZD_EDITION_NAME"='ORA$BASE')

21 - filter(("RECEIPT_METHOD_ID"="CUST_REC_M"."RECEIPT_METHOD_ID" AND "CUST_REC_M"."PRIMARY_FLAG"='Y'))

22 - filter("CA"."PAYMENT_TERM_ID" IS NOT NULL)

23 - filter(("CSU"."SITE_USE_CODE"='BILL_TO' AND "CSU"."STATUS"='A'))

24 - access("CSU"."SITE_USE_ID"="CUST_REC_M"."SITE_USE_ID")

25 - filter(("CAS"."CUST_ACCOUNT_ID"="CA"."CUST_ACCOUNT_ID" AND "CAS"."STATUS"='A' AND INTERNAL_FUNCTION("CAS"."ORG_ID")))

26 - access("CSU"."CUST_ACCT_SITE_ID"="CAS"."CUST_ACCT_SITE_ID")

27 - access("EPA"."ACCT_SITE_USE_ID"="CSU"."SITE_USE_ID")

filter("EPA"."ACCT_SITE_USE_ID" IS NOT NULL)

29 - filter(("PIU"."PAYMENT_FUNCTION"='CUSTOMER_PAYMENT' AND TRUNC(NVL("PIU"."END_DATE",SYSDATE@!))>=TRUNC(SYSDATE@!)))

31 - access("EBA"."EXT_BANK_ACCOUNT_ID"="PIU"."INSTRUMENT_ID")

32 - filter("CUST"."STATUS"='A')

33 - access("CUST"."PARTY_ID"="CA"."PARTY_ID")

34 - access("BANK"."PARTY_ID"="EBA"."BANK_ID")

Note

-----

- this is an adaptive plan

====================================================================================================

---Plan using SQL_ID

---------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | 132K(100)| |

| 1 | NESTED LOOPS | | 1 | 62 | | 5 (0)| 00:00:01 |

|* 2 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 1 | 16 | | 3 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 1 | | | 2 (0)| 00:00:01 |

|* 4 | TABLE ACCESS BY INDEX ROWID | HZ_LOCATIONS | 1 | 46 | | 2 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 1 | | | 1 (0)| 00:00:01 |

| 6 | NESTED LOOPS | | | | | | |

| 7 | NESTED LOOPS | | 1 | 335 | | 132K (1)| 00:00:37 |

| 8 | NESTED LOOPS | | 1 | 300 | | 132K (1)| 00:00:37 |

| 9 | NESTED LOOPS | | 1 | 263 | | 132K (1)| 00:00:37 |

|* 10 | HASH JOIN | | 1 | 219 | | 132K (1)| 00:00:37 |

| 11 | NESTED LOOPS | | | | | | |

| 12 | NESTED LOOPS | | 1 | 176 | | 111K (1)| 00:00:32 |

| 13 | NESTED LOOPS | | 1 | 164 | | 111K (1)| 00:00:32 |

| 14 | NESTED LOOPS | | 9452 | 1273K| | 92949 (1)| 00:00:26 |

|* 15 | HASH JOIN | | 16583 | 1846K| | 59765 (2)| 00:00:17 |

|* 16 | TABLE ACCESS FULL | RA_TERMS_TL | 10 | 270 | | 5 (0)| 00:00:01 |

|* 17 | HASH JOIN | | 21559 | 1831K| 2616K| 59760 (2)| 00:00:17 |

| 18 | NESTED LOOPS | | 37686 | 2171K| | 10427 (2)| 00:00:03 |

| 19 | TABLE ACCESS BY INDEX ROWID| AR_RECEIPT_METHODS | 1 | 39 | | 2 (0)| 00:00:01 |

|* 20 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U2 | 1 | | | 1 (0)| 00:00:01 |

|* 21 | TABLE ACCESS FULL | RA_CUST_RECEIPT_METHODS | 37686 | 736K| | 10425 (2)| 00:00:03 |

|* 22 | TABLE ACCESS FULL | HZ_CUST_ACCOUNTS | 1027K| 27M| | 47250 (2)| 00:00:14 |

|* 23 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 1 | 24 | | 2 (0)| 00:00:01 |

|* 24 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 1 | | | 1 (0)| 00:00:01 |

|* 25 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 1 | 26 | | 2 (0)| 00:00:01 |

|* 26 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 | 1 | | | 1 (0)| 00:00:01 |

|* 27 | INDEX RANGE SCAN | IBY_EXTERNAL_PAYERS_ALL_SU_N3 | 1 | | | 2 (0)| 00:00:01 |

| 28 | TABLE ACCESS BY INDEX ROWID | IBY_EXTERNAL_PAYERS_ALL | 1 | 12 | | 3 (0)| 00:00:01 |

|* 29 | TABLE ACCESS FULL | IBY_PMT_INSTR_USES_ALL | 122K| 5150K| | 20538 (3)| 00:00:06 |

| 30 | TABLE ACCESS BY INDEX ROWID | IBY_EXT_BANK_ACCOUNTS | 1 | 44 | | 1 (0)| 00:00:01 |

|* 31 | INDEX UNIQUE SCAN | IBY_EXT_BANK_ACCOUNTS_U1 | 1 | | | 0 (0)| |

|* 32 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 37 | | 2 (0)| 00:00:01 |

|* 33 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | | 1 (0)| 00:00:01 |

|* 34 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | | 1 (0)| 00:00:01 |

| 35 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 35 | | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("HPS"."END_DATE_ACTIVE" IS NULL)

3 - access("HPS"."PARTY_SITE_ID"=:B1)

4 - filter("LOC"."COUNTRY"='GB')

5 - access("LOC"."LOCATION_ID"="HPS"."LOCATION_ID")

10 - access("PIU"."EXT_PMT_PARTY_ID"="EPA"."EXT_PAYER_ID")

15 - access("CA"."PAYMENT_TERM_ID"="TERM_ID")

16 - filter((INTERNAL_FUNCTION("NAME") AND NVL("ZD_EDITION_NAME",'ORA$BASE')='ORA$BASE'))

17 - access("CUST_REC_M"."CUSTOMER_ID"="CA"."CUST_ACCOUNT_ID")

20 - access("NAME"='Direct Debit UK GBP BofA' AND "ZD_EDITION_NAME"='ORA$BASE')

21 - filter(("RECEIPT_METHOD_ID"="CUST_REC_M"."RECEIPT_METHOD_ID" AND "CUST_REC_M"."PRIMARY_FLAG"='Y'))

22 - filter("CA"."PAYMENT_TERM_ID" IS NOT NULL)

23 - filter(("CSU"."SITE_USE_CODE"='BILL_TO' AND "CSU"."STATUS"='A'))

24 - access("CSU"."SITE_USE_ID"="CUST_REC_M"."SITE_USE_ID")

25 - filter(("CAS"."CUST_ACCOUNT_ID"="CA"."CUST_ACCOUNT_ID" AND "CAS"."STATUS"='A' AND

INTERNAL_FUNCTION("CAS"."ORG_ID")))

26 - access("CSU"."CUST_ACCT_SITE_ID"="CAS"."CUST_ACCT_SITE_ID")

27 - access("EPA"."ACCT_SITE_USE_ID"="CSU"."SITE_USE_ID")

filter("EPA"."ACCT_SITE_USE_ID" IS NOT NULL)

29 - filter(("PIU"."PAYMENT_FUNCTION"='CUSTOMER_PAYMENT' AND TRUNC(NVL("PIU"."END_DATE",SYSDATE@!))>=TRUNC(SYSDATE@!)))

31 - access("EBA"."EXT_BANK_ACCOUNT_ID"="PIU"."INSTRUMENT_ID")

32 - filter("CUST"."STATUS"='A')

33 - access("CUST"."PARTY_ID"="CA"."PARTY_ID")

34 - access("BANK"."PARTY_ID"="EBA"."BANK_ID")

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "ADDRESS1"[VARCHAR2,240], "LOC"."CITY"[VARCHAR2,60], "LOC"."POSTAL_CODE"[VARCHAR2,60], "LOC"."STATE"[VARCHAR2,60]

2 - "HPS"."LOCATION_ID"[NUMBER,22]

3 - "HPS".ROWID[ROWID,10]

4 - "ADDRESS1"[VARCHAR2,240], "LOC"."CITY"[VARCHAR2,60], "LOC"."POSTAL_CODE"[VARCHAR2,60], "LOC"."STATE"[VARCHAR2,60]

5 - "LOC".ROWID[ROWID,10]

6 - "NAME"[VARCHAR2,15], "NAME"[VARCHAR2,30], "CUST_REC_M"."PRIMARY_FLAG"[VARCHAR2,1],

"CA"."ATTRIBUTE1"[VARCHAR2,150], "CA"."ACCOUNT_NUMBER"[VARCHAR2,30], "CAS"."PARTY_SITE_ID"[NUMBER,22],

"CAS"."ORG_ID"[NUMBER,22], "PIU"."END_DATE"[DATE,7], "PIU"."ORDER_OF_PREFERENCE"[NUMBER,22], "PIU"."START_DATE"[DATE,7],

"EBA"."BANK_ACCOUNT_NUM"[VARCHAR2,100], "EBA"."BANK_ACCOUNT_NAME"[VARCHAR2,80], "CUST"."PARTY_NAME"[VARCHAR2,360],

"BANK"."PARTY_NAME"[VARCHAR2,360]

7 - "NAME"[VARCHAR2,15], "NAME"[VARCHAR2,30], "CUST_REC_M"."PRIMARY_FLAG"[VARCHAR2,1],

"CA"."ATTRIBUTE1"[VARCHAR2,150], "CA"."ACCOUNT_NUMBER"[VARCHAR2,30], "CAS"."PARTY_SITE_ID"[NUMBER,22],

"CAS"."ORG_ID"[NUMBER,22], "PIU"."END_DATE"[DATE,7], "PIU"."ORDER_OF_PREFERENCE"[NUMBER,22], "PIU"."START_DATE"[DATE,7],

"EBA"."BANK_ACCOUNT_NUM"[VARCHAR2,100], "EBA"."BANK_ACCOUNT_NAME"[VARCHAR2,80], "CUST"."PARTY_NAME"[VARCHAR2,360],

"BANK".ROWID[ROWID,10]

8 - "NAME"[VARCHAR2,15], "NAME"[VARCHAR2,30], "CUST_REC_M"."PRIMARY_FLAG"[VARCHAR2,1],

"CA"."ATTRIBUTE1"[VARCHAR2,150], "CA"."ACCOUNT_NUMBER"[VARCHAR2,30], "CAS"."PARTY_SITE_ID"[NUMBER,22],

"CAS"."ORG_ID"[NUMBER,22], "PIU"."END_DATE"[DATE,7], "PIU"."ORDER_OF_PREFERENCE"[NUMBER,22], "PIU"."START_DATE"[DATE,7],

"EBA"."BANK_ID"[NUMBER,22], "EBA"."BANK_ACCOUNT_NUM"[VARCHAR2,100], "EBA"."BANK_ACCOUNT_NAME"[VARCHAR2,80],

"CUST"."PARTY_NAME"[VARCHAR2,360]

9 - "NAME"[VARCHAR2,15], "NAME"[VARCHAR2,30], "CUST_REC_M"."PRIMARY_FLAG"[VARCHAR2,1],