Bank Statement Loader Concurrent Request Is Taking A Long Time To Run

 

Bank Statement Loader Concurrent Request Is Taking A Long Time To Run

ACTUAL BEHAVIOR
---------------
We run a number of Bank Statement Loader concurrent requests on a daily basis (one for each bank account currency). Typically these have completed in approximately 2 minutes. They are now taking 30 minutes to complete slowing down the whole process of bank reconciliation. We have not made any system changes that would affect this.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Submit CESLRPROSWIFT940

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users experience performance issue

ISSUE

After upgrade to 12.2.10 with 19C database program is taking longer time as compare to 12.1.3 with 12C.

In old environment it was taking approx. 1 minute where as in new environment taking approx. 5 minute

SOLUTION

1- Connect to CDB Instance and take backup of "SYS"."_ALL_SYNONYMS_TREE"

2-  Recreate the view "SYS"."_ALL_SYNONYMS_TREE" with a new SELECT that includes a "hint".

CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."_ALL_SYNONYMS_TREE"

("SYN_OWNER", "SYN_SYNONYM_NAME", "SYN_TABLE_OWNER", "SYN_TABLE_NAME", "SYN_DB_LINK", "ORIGIN_CON_ID") AS

select /*+ no_connect_by_filtering no_connect_by_combine_sw */

s.syn_owner, s.syn_synonym_name, s.syn_table_owner,

s.syn_table_name, s.syn_db_link, s.origin_con_id

from sys."_ALL_SYNONYMS_FOR_SYNONYMS" s

/* user has any privs on ultimate base object */

start with exists (

select /*+ NO_PUSH_SUBQ */ null

from sys."_ALL_SYNONYMS_FOR_AUTH_OBJECTS" sa

where s.syn_table_owner = sa.owner

and s.syn_table_name = sa.synonym_name

)

connect by nocycle prior s.syn_owner = s.syn_table_owner and

prior s.syn_synonym_name = s.syn_table_name;

3- Backup file $CE_TOP/bin/SWIFT940.ctl . Use the following OS Command

$ cp $CE_TOP/bin/SWIFT940.ctl SWIFT940_CoE000.ctl

4- Edit file $CE_TOP/bin/SWIFT940.ctl

5- Update every occurrence of "ce_stmt_int_tmp" to "CE.ce_stmt_int_tmp". Notice we prefix the schema CE.

6- Run the SWIFT940 Loader program again