Performance Issue While Running ARXAGE - Aging - 4 Buckets Report
(Doc ID 3006135.1)
Last updated on MARCH 02, 2024
Applies to:
Oracle Receivables - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
Performance issue while running the Aging - 4 Buckets Report (ARXAGE).
The tkprof file shows multiple Full Table Scans (FTS). The SQL ID identified is:
SQL ID: 8262p62td91ja Plan Hash: 3000435441
select ps . org_id invoice_org_id , decode ( UPPER ( : p_order_by ) ,
'CUSTOMER' , NULL , types . cust_trx_type_id ) dummy_id_inv , decode (
UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . name ) dummy_type_inv ,
decode ( party . party_name , null , '2' , rtrim ( rpad ( substrb ( '1' ||
...
Unit Information' AND org_info.org_information3 = TO_CHAR ( 2023 ) ) ) )
AND ( :qi_org_id = ps.org_id) ORDER BY 30 ASC,3 ASC,4 ASC,5 ASC,6 ASC,10
ASC , 30 , 3 , 4 , 6 , 17 , 14 , 15 desc
...
6657 6657 6657 TABLE ACCESS FULL GL_CODE_COMBINATIONS (cr=406 pr=0 pw=0 time=3508 us cost=111 size=2980 card=298)
0 0 0 NESTED LOOPS SEMI (cr=13634992 pr=13617022 pw=0 time=1345646668 us cost=3791618 size=2648336 card=12856)
0 0 0 NESTED LOOPS (cr=13634992 pr=13617022 pw=0 time=1345646656 us cost=3791617 size=2481208 card=12856)
...
0 0 0 TABLE ACCESS FULL AR_PAYMENT_SCHEDULES_ALL (cr=13634977 pr=13617022 pw=0 time=1345645494 us cost=3710499 size=349979784 card=2651362)
...
265011689 265011689 265011689 TABLE ACCESS FULL AR_RECEIVABLE_APPLICATIONS_ALL (cr=17395485 pr=17373681 pw=0 time=1589479111 us cost=4716265 size=854090976 card=17793562)
...
129715831 129715831 129715831 TABLE ACCESS FULL HZ_PARTIES (cr=6003032 pr=5995243 pw=0 time=792518966 us cost=1625964 size=3372516186 card=129712161)
...
0 0 0 TABLE ACCESS FULL AR_PAYMENT_SCHEDULES_ALL (cr=13657905 pr=13619193 pw=0 time=1510977854 us cost=3711632 size=8935211725 card=73844725)
0 0 0 TABLE ACCESS FULL HZ_CUST_ACCOUNTS (cr=0 pr=0 pw=0 time=0 us cost=1163257 size=2663624136 card=110984339)
select ps . org_id invoice_org_id , decode ( UPPER ( : p_order_by ) ,
'CUSTOMER' , NULL , types . cust_trx_type_id ) dummy_id_inv , decode (
UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . name ) dummy_type_inv ,
decode ( party . party_name , null , '2' , rtrim ( rpad ( substrb ( '1' ||
...
Unit Information' AND org_info.org_information3 = TO_CHAR ( 2023 ) ) ) )
AND ( :qi_org_id = ps.org_id) ORDER BY 30 ASC,3 ASC,4 ASC,5 ASC,6 ASC,10
ASC , 30 , 3 , 4 , 6 , 17 , 14 , 15 desc
...
6657 6657 6657 TABLE ACCESS FULL GL_CODE_COMBINATIONS (cr=406 pr=0 pw=0 time=3508 us cost=111 size=2980 card=298)
0 0 0 NESTED LOOPS SEMI (cr=13634992 pr=13617022 pw=0 time=1345646668 us cost=3791618 size=2648336 card=12856)
0 0 0 NESTED LOOPS (cr=13634992 pr=13617022 pw=0 time=1345646656 us cost=3791617 size=2481208 card=12856)
...
0 0 0 TABLE ACCESS FULL AR_PAYMENT_SCHEDULES_ALL (cr=13634977 pr=13617022 pw=0 time=1345645494 us cost=3710499 size=349979784 card=2651362)
...
265011689 265011689 265011689 TABLE ACCESS FULL AR_RECEIVABLE_APPLICATIONS_ALL (cr=17395485 pr=17373681 pw=0 time=1589479111 us cost=4716265 size=854090976 card=17793562)
...
129715831 129715831 129715831 TABLE ACCESS FULL HZ_PARTIES (cr=6003032 pr=5995243 pw=0 time=792518966 us cost=1625964 size=3372516186 card=129712161)
...
0 0 0 TABLE ACCESS FULL AR_PAYMENT_SCHEDULES_ALL (cr=13657905 pr=13619193 pw=0 time=1510977854 us cost=3711632 size=8935211725 card=73844725)
0 0 0 TABLE ACCESS FULL HZ_CUST_ACCOUNTS (cr=0 pr=0 pw=0 time=0 us cost=1163257 size=2663624136 card=110984339)
Steps to Reproduce:
Responsibility: Receivables Manager
Navigation: Control > Requests > Run
Name: Aging - 4 Buckets Report
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| 1. Bug Summary |
| 2. Fixed Files |
| 3. Recommended Patches |
| 4. Solution Steps |
| References |