Allocation Rule on Management Ledger Performance Issue at Select Distinct ISO_CURRENCY_CD
(Doc ID 2886121.1)
Last updated on OCTOBER 12, 2022
Applies to:Oracle Financial Services Profitability Management - Version 188.8.131.52.0 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Profitability Management (PFT)
On PFT 184.108.40.206.2 version, user has about 80 million records in fsi_d_management_ledger for all the dates. When running any allocation rule, found query below that takes time to complete:
SELECT DISTINCT ISO_CURRENCY_CD
WHERE ISO_CURRENCY_CD <> '002'
AND ISO_CURRENCY_CD <> 'USD';
The query does not have as_of_date as filter due to which no partition pruning happening.
Steps To Replicate:
1. User has over 1000 allocation rules to run on management ledger monthly. This takes 14 hours to complete.
2. During 14 hour, the above SQL was executed approximately 676 times, it takes 15.17 seconds each with total time of 2.8 hour.
User is looking for ways to reduce the monthly processing time.
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