My Oracle Support Banner

DERG1600 Performance Degradation Post USFED 80970 Upgrade (Doc ID 2760432.1)

Last updated on MARCH 21, 2021

Applies to:

Oracle Financial Services - Regulatory Reporting for US Federal Reserve - Lombard Risk Integration Pack - Version 8.0.9 and later
Information in this document applies to any platform.

Symptoms


REG REP USFED 80970

DE DERG1600 refresh is running longer post upgrade to 80970. The change in 80970 is the count query added in BPRG2203(as shown below)

This query is evaluated for each row fetched in the DE join. 

If we can add this to the data set and prevent this from being evaluated for every row the performance will improve

CASE
WHEN (
SELECT
COUNT(1)
FROM
FCT_REG_CREDIT_LINE_DETAILS
A_FCT_REG_CREDIT_LINE_PARENT
WHERE
FCT_REG_CREDIT_LINE_DETAILS.N_CREDIT_LINE_SKEY =
A_FCT_REG_CREDIT_LINE_PARENT.N_PARENT_CREDIT_LINE_SKEY AND
FCT_REG_CREDIT_LINE_DETAILS.N_RUN_SKEY =
A_FCT_REG_CREDIT_LINE_PARENT.N_RUN_SKEY AND
FCT_REG_CREDIT_LINE_DETAILS.N_MIS_DATE_SKEY =
A_FCT_REG_CREDIT_LINE_PARENT.N_MIS_DATE_SKEY
and FCT_REG_CREDIT_LINE_DETAILS.N_RUN_SKEY =
dim_run.N_RUN_SKEY
) > 0
THEN 'Y'
ELSE 'N'
END AS BPRG2203,

This BP is evaluated for every row and scans the 24 M rows in FCT_REG_CREDIT_LINE_DETAILS 



Changes

In 80970 the count query added in BPRG2203

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
Changes
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.