Slow Response from Valuation Stored Procedures (Doc ID 1940745.1)

Last updated on AUGUST 02, 2016

Applies to:

Oracle Insurance Policy Administration J2EE - Version 9.6.0.0 and later
Information in this document applies to any platform.

Symptoms

On : 9.6.1.10 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.

Cause

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