My Oracle Support Banner

FTP Standard Process with Audit Enabled Taking Too Long to Complete (Doc ID 2309599.1)

Last updated on JULY 12, 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)
Slow Performance

Symptoms

On FTP 8.0.4 version, the FTP Standard process with Audit enabled is taking too long to complete.

ACTUAL BEHAVIOR
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.

EXPECTED BEHAVIOR
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.

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!


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