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

Last updated on MARCH 21, 2021

Applies to:

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

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