My Oracle Support Banner

ARGLRECR module: AR to GL Reconciliation Report Is Having Performance Issues (Doc ID 1109133.1)

Last updated on FEBRUARY 13, 2019

Applies to:

Oracle Receivables - Version 12.1.1 and later
Information in this document applies to any platform.

Symptoms

ARGLRECR module: AR to GL Reconciliation Report is having performance issues

The following SQL statements appear in tkprof as the ones causing the performance problem:

BEGIN ARP_RECON_REP.arglrecon_load_xml( P_REPORTING_LEVEL => '1000',
P_REPORTING_ENTITY_ID => '1001',
P_SOB_ID => '',
P_COA_ID => '101',
P_OUT_OF_BALANCE_ONLY => 'N',
P_CO_SEG_LOW => '',
P_CO_SEG_HIGH => '',
P_PERIOD_NAME => 'MAR-10',
P_GL_ACCOUNT_LOW => '',
P_GL_ACCOUNT_HIGH => '',
P_SUMMARY_ACCOUNT => '',
p_result => :1); end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 53132.90 53275.09 46616 18915233 3234423 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 53132.90 53275.09 46616 18915233 3234423 1

 

UPDATE AR_GL_RECON_GT SET ACCOUNT = AR_CALC_AGING.GET_VALUE(101,'GL#',
ARP_RECON_REP.GET_CHART_OF_ACCOUNTS_ID(),'ALL',CODE_COMBINATION_ID),
COMPANY = AR_CALC_AGING.GET_VALUE(101,'GL#',
ARP_RECON_REP.GET_CHART_OF_ACCOUNTS_ID(),'GL_BALANCING',CODE_COMBINATION_ID)
, ACCOUNT_DESC = AR_CALC_AGING.GET_DESCRIPTION(101,'GL#',
ARP_RECON_REP.GET_CHART_OF_ACCOUNTS_ID(),'GL_ACCOUNT',CODE_COMBINATION_ID)



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 1 0 0
Execute 1 39293.39 39166.41 0 49780 424474 82859
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 39293.40 39166.43 0 49781 424474 82859

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

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE AR_GL_RECON_GT (cr=5769971 pr=164 pw=0 time=1040749070 us)
82859 TABLE ACCESS FULL AR_GL_RECON_GT (cr=1012 pr=0 pw=0 time=1077790 us)

 

select code_combination_id,
account_type,
account,
account_desc,
company,
decode(account_type_code,'A',1,'L',2,'R',3,'E',4) account_type_code,
nvl(opening_balance_dr,0) begin_gl_bal_debit,
nvl(opening_balance_cr,0) begin_gl_bal_credit,
nvl(opening_balance_dr,0)+nvl(period_activity_dr,0) end_gl_bal_debit,
nvl(opening_balance_cr,0)+nvl(period_activity_cr,0) end_gl_bal_credit,
nvl(subledger_not_ar_dr,0) subledger_not_ar_debit,
nvl(subledger_not_ar_cr,0) subledger_not_ar_credit,
nvl(subledger_manual_dr,0) subledger_manual_debit,
nvl(subledger_manual_cr,0) subledger_manual_credit,
nvl(subledger_rec_dr,0) subledger_receivables_debit,
nvl(subledger_rec_cr,0) subledger_receivables_credit,
nvl(gl_unposted_dr,0) gl_unposted_debit,
nvl(gl_unposted_cr,0) gl_unposted_credit,
nvl(gl_interface_dr,0) gl_interface_debit,
nvl(gl_interface_cr,0) gl_interface_credit,
nvl(receivables_dr,0) receivables_debit,
nvl(receivables_cr,0) receivables_credit
from ar_gl_recon_gt
where 'N' = arp_recon_rep.get_out_of_balance_only()
or nvl(receivables_dr,0)- nvl(subledger_rec_dr,0) <> 0
or nvl(receivables_cr,0)- nvl(subledger_rec_cr,0) <> 0
order by account_type_code,
company,
ar_calc_aging.get_value(101,'GL#',
arp_recon_rep.get_chart_of_accounts_id(),
'GL_ACCOUNT',code_combination_id)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.83 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 82860 13166.75 13085.58 0 1988 0 82859
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 82862 13166.76 13086.42 0 1989 0 82859

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

Rows Row Source Operation
------- ---------------------------------------------------
82859 SORT ORDER BY (cr=1988 pr=0 pw=0 time=199765961 us)
82859 TABLE ACCESS FULL AR_GL_RECON_GT (cr=1988 pr=0 pw=0 time=4557439 us)

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 File(s)
 3. Recommended Patch(es)
 4. Solution Steps
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.