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

Last updated on JULY 31, 2017

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

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