FTP Standard Process with Audit Enabled Taking Too Long to Complete
Last updated on JUNE 11, 2018
Applies to:Oracle Financial Services Funds Transfer Pricing - Version 8.0.4 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Funds Transfer Pricing (FTP)
On FTP 8.0.4 version, the FTP Standard process with Audit enabled is taking too long to complete.
FTP_CASA Batch under Funds transfer Pricing application takes long time to complete. On reviewing the issue with AWR , Issue points the the Merge statement.
MERGE INTO FSI_O_FTP_AUDIT_TRAIL a using dual on ( INSTRUMENT_TYPE_CD = 0 and ID_NUMBER = 12345 and IDENTITY_CODE = 20170430 and BATCH_ID = 'OFSAA_FTP_CASA_20170430_17' and AS_OF_DATE = '4/30/2017' and TP_PROCESS_SYS_ID = 220304 ) when matched then update set TRANSFER_PRICE_SYS_ID = 220746 , TP_METHOD = '3 - Moving Averages' , TP_IRC = 501 , TP_ACCRUAL_FREQ_CD = 'M' , ACCRUAL_BASIS_CD = '' when not matched then insert ( INSTRUMENT_TYPE_CD , ID_NUMBER , IDENTITY_CODE , BATCH_ID , AS_OF_DATE , TP_PROCESS_SYS_ID , TRANSFER_PRICE_SYS_ID , TP_METHOD , TP_IRC , TP_ACCRUAL_FREQ_CD , ACCRUAL_BASIS_CD ) values (0 , 12345, 20170430 , 'OFSAA_FTP_CASA_20170430_17' , '4/30/2017' , 220304 , 220746 , '3 - Moving Averages' , 501 , 'M' , '' )
FSI_O_FTP_AUDIT_TRAIL – Table with issue which has no index. Volume of data 1.5 million records. It performs full table scan. Could you please suggest the next course of action since this table is out of box from oracle.
Expect faster processing time for FTP_CASA batch.
The issue can be reproduced at will with the following steps:
1. Run FTP Process with Audit on.
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