Slow Response from Valuation Stored Procedures
Last updated on AUGUST 02, 2016
Applies to:Oracle Insurance Policy Administration J2EE - Version 126.96.36.199 and later
Information in this document applies to any platform.
On : 188.8.131.52 version, Cycle Issues
Users report performance degradation in valuation as a policy ages. Consulting team analysis identified two long running stored procedures:
ASC_GETDEPOSITVALUELIST and ASC_GETFUNDVALUELIST
Running an explain plan against the SQL suggests a problem with the subselect - narrowed the issue to the inclusion of the following for Deposit -
AND INNERASDEPOSITVALUE.DEPOSITVALUATIONEFFECTGUID = ASDEPOSITVALUE.DEPOSITVALUATIONEFFECTGUID
and for Fund -
AND INNERASFUNDVALUE.FUNDGUID = ASFUNDVALUE.FUNDGUID
Steps to Reproduce
The issue can be reproduced at will with the following steps:
1. Age a UL policy with deposit level tracking for funds out to a few years.
2. Process Valuation - timeouts will start to occur on policies older than 3 years.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms