My Oracle Support Banner

SQL Queries on Instrument Tables Have Slow Performance After Running FTP Processes (Doc ID 2771287.1)

Last updated on MAY 03, 2021

Applies to:

Oracle Financial Services Funds Transfer Pricing - Version 8.0.0 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Funds Transfer Pricing (FTP)

Symptoms

After running a batch of FTP Processes, SQL queries on instrument tables (ex. FSI_D_CASA, FSI_D_CREDIT_CARDS, FSI_D_LOAN_CONTRACTS, etc.) have very slow performance.  A SQL query that was 10 seconds now takes 10 minutes.  Reviewing the table statistics, the tables are fragmented and rows are spread across multiple blocks.

The FTP Processes update multiple columns the instrument tables.  For example:

TRANSFER_RATE
CHARGE_CREDIT_TRATE
OTHER_ADJUSTMENTS_RATE
OTHER_ADJ_CHARGE_CREDIT
BASIS_RISK_COST_RATE
BASIS_RISK_CHARGE_CREDIT
LIQUIDITY_PREMIUM_RATE
LIQUIDITY_PREM_CHARGE_CREDIT
PRICING_INCENTIVE_RATE
PRICING_INC_CHARGE_CREDIT
ALL_IN_TP_RATE
TOTAL_CHARGE_CREDIT_TRATE

You already implemented partitioning on the As of Date column.

You do not expect SQL query performance to decline after running the FTP Processes.

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!


In this Document
Symptoms
Cause
Solution
References


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