ARLPLB - Performance Issue With Lockbox Validation In arp_lockbox_hook_pvt.proc_after_validation (Doc ID 799973.1)

Last updated on NOVEMBER 11, 2013

Applies to:

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

Executable:ARLPLB - Process Lockboxes


Symptoms

Lockbox validation is having performance issues. It runs for almost 2 hours for 8900 records. Custom
hook is enabled at the lockbox to process line level cash applications. However if the custom hook is disabled it takes less than 5 min.

Top SQL in tkprof file is:

begin arp_lockbox_hook_pvt . proc_after_validation (
:custom_errbuf:i_custom_errbuf , :custom_retcode:i_custom_retcode ,
:trans_request_id , :insert_records:i_insert_records ) ; EXCEPTION when
others then null ; END ;


call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   1      0.01     0.00       0          0          0          0
Execute 1      5949.76  5840.61    940        3708255    428338     1
Fetch   0      0.00     0.00       0          0          0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   2      5949.77  5840.61    940        3708255    428338     1

 No other SQL Statements are shown in tkprof as performing bad.

Running PL/SQl Profiler per <Note:243755.1> Implementing and Using the PL/SQL Profiler, shows the following as the top PL/SQL code:

Name                 Total Time
ARP_LOCKBOX_HOOK_PVT 18446740674.39
ARP_DEBUG            36893487461.54

The lines taking most of the times in the above packages are:

l_msg_module := SUBSTRB(RTRIM(l_line), 1,
procedure debug( line in varchar2,
IF l_error_flag <> 'T' THEN

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