IEXLSGEN - Quick Search on Transaction Number Slow In Collections (Doc ID 1163864.1)

Last updated on MARCH 15, 2017

Applies to:

Oracle Advanced Collections - Version 12.1.2 and later
Information in this document applies to any platform.

Symptoms

Performance issue with Quick Search by Transaction number. It is taking approximately 30 minutes for the search to complete.

Tkprof shows the following query as the one causing the performance problem:

SELECT ACCOUNT_NUMBER COL1, ROLE_TYPE COL2, FIRST_NAME COL3, MEANING COL4,
LAST_NAME COL5, ADDRESS COL6, CITY COL7, COUNTRY COL8, COUNTRY_NAME COL9,
CUST_ACCOUNT_ID COL10, EMAIL_ADDRESS COL11, PARTY_ID COL12, PARTY_NAME
COL13, POSTAL_CODE COL14, PROVINCE COL15, STATE COL16, STATUS_MEAN COL17,
URL COL18, ORIG_SYSTEM_REFERENCE COL19
FROM
IEX_LS_ACC_TRX_QUICK_V WHERE 'JTFBIND' = :vJTFBIND and cust_account_id in (
select unique a.Bill_to_customer_id from ra_customer_trx a where
a.trx_number like :INVOICE) AND STATUS = 'A'


call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   1      0.00     0.00       0          0          0          0
Execute 1      0.00     0.00       0          0          0          0
Fetch   3      50056.03 49078.85   347337     619548817  0          2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   5      50056.03 49078.85   347337     619548817  0          2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
2 HASH JOIN RIGHT SEMI (cr=395150 pr=286544 pw=0 time=384486044 us)
1 TABLE ACCESS BY INDEX ROWID RA_CUSTOMER_TRX_ALL (cr=4 pr=3 pw=0 time=14399 us)
1 INDEX RANGE SCAN RA_CUSTOMER_TRX_N1 (cr=3 pr=3 pw=0 time=14378 us)(object id 394267)
1551281 VIEW IEX_LS_ACC_TRX_QUICK_V (cr=395146 pr=286541 pw=0 time=383095462 us)
1551281 UNION-ALL (cr=395146 pr=286541 pw=0 time=383095457 us)
1551281 HASH GROUP BY (cr=395146 pr=286541 pw=0 time=379999472 us)
56671707 FILTER (cr=395146 pr=286541 pw=0 time=310376131 us)
56671707 HASH JOIN RIGHT OUTER (cr=395146 pr=286541 pw=0 time=253704422 us)
252 TABLE ACCESS FULL FND_TERRITORIES_TL (cr=15 pr=11 pw=0 time=11373 us)
56671707 HASH JOIN (cr=395131 pr=286530 pw=0 time=197018903 us)
1551430 TABLE ACCESS FULL RA_CUSTOMER_TRX_ALL (cr=92724 pr=43089 pw=0 time=49645845 us)
3679605 HASH JOIN (cr=302407 pr=243441 pw=0 time=135562386 us)
3681374 TABLE ACCESS FULL HZ_CUST_SITE_USES_ALL (cr=108666 pr=75992 pw=0 time=36824116 us)
1851256 HASH JOIN (cr=193741 pr=167449 pw=0 time=90694039 us)
1852234 TABLE ACCESS FULL HZ_CUST_ACCT_SITES_ALL (cr=43328 pr=22828 pw=0 time=16685113 us)
1272958 HASH JOIN (cr=150413 pr=144621 pw=0 time=67652302 us)
1272958 NESTED LOOPS (cr=44824 pr=44530 pw=0 time=21654158 us)
1 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=4 pr=2 pw=0 time=7882 us)
1 INDEX UNIQUE SCAN FND_LOOKUP_VALUES_U1 (cr=3 pr=1 pw=0 time=3968 us)(object id 34010)
1272958 TABLE ACCESS FULL HZ_CUST_ACCOUNTS (cr=44820 pr=44528 pw=0 time=21646269 us)
1630722 TABLE ACCESS FULL HZ_PARTIES (cr=105589 pr=100091 pw=0 time=40774971 us)
0 HASH GROUP BY (cr=0 pr=0 pw=0 time=94 us)
1833187878 FILTER (cr=13504055156 pr=60793 pw=0 time=45854483969 us)
1833187878 TABLE ACCESS BY INDEX ROWID HZ_CUST_SITE_USES_ALL (cr=13504055156 pr=60793 pw=0 time=44021296086 us)
3351940504 NESTED LOOPS (cr=11613491776 pr=60587 pw=0 time=31828935383 us)
1518752626 NESTED LOOPS (cr=7046995937 pr=60459 pw=0 time=18249818443 us)
1518752626 NESTED LOOPS (cr=944069644 pr=60339 pw=0 time=6099791625 us)
321985 NESTED LOOPS (cr=143705 pr=60115 pw=0 time=30254827 us)
249 NESTED LOOPS (cr=61135 pr=59719 pw=0 time=27044812 us)
249 HASH JOIN (cr=60139 pr=59719 pw=0 time=27039818 us)
16869 HASH JOIN (cr=45227 pr=44906 pw=0 time=20939296 us)
16881 TABLE ACCESS FULL HZ_CUST_ACCOUNT_ROLES (cr=407 pr=403 pw=0 time=296372 us)
1272958 TABLE ACCESS FULL HZ_CUST_ACCOUNTS (cr=44820 pr=44503 pw=0 time=19101391 us)
81028 TABLE ACCESS FULL HZ_RELATIONSHIPS (cr=14912 pr=14813 pw=0 time=5926084 us)
249 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=996 pr=0 pw=0 time=4451 us)
249 INDEX UNIQUE SCAN FND_LOOKUP_VALUES_U1 (cr=747 pr=0 pw=0 time=2965 us)(object id 34010)
321985 TABLE ACCESS BY INDEX ROWID RA_CUSTOMER_TRX_ALL (cr=82570 pr=396 pw=0 time=2933013 us)
321985 INDEX RANGE SCAN RA_CUSTOMER_TRX_N11 (cr=1584 pr=38 pw=0 time=667691 us)(object id 323261)
1518752626 TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCT_SITES_ALL (cr=943925939 pr=224 pw=0 time=4560438905 us)
1518752626 INDEX RANGE SCAN HZ_CUST_ACCT_SITES_N2 (cr=6473860 pr=86 pw=0 time=3486732 us)(object id 148120)
1518752626 TABLE ACCESS BY INDEX ROWID HZ_PARTIES (cr=6102926293 pr=120 pw=0 time=11611551303 us)
1518752626 INDEX UNIQUE SCAN HZ_PARTIES_U1 (cr=4556257878 pr=25 pw=0 time=5731860715 us)(object id 172682)
1833187878 INDEX RANGE SCAN HZ_CUST_SITE_USES_N1 (cr=4566495839 pr=128 pw=0 time=8958375295 us)(object id 148083)

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms