OUA 2.8 CF_FT_GL Performance Issue While Inserting Records Into Key Table (Doc ID 3058917.1)

Last updated on NOVEMBER 21, 2024

Applies to:

Goal

On: 2.8.0.2.0 version, Install / Upgrade

ACTUAL BEHAVIOR
---------------
OUA 2.8 CF_FT_GL performance issue while inserting records into the Key table

We observed the performance issue of CF_FT_GL ETL out-of-the-box mapping (B1_F_FT_GL_KEY) during daily incremental data loads while inserting records into the Key table.

We are using the OUA 2.8 ETL version code.

CI_FT1.JRN_SLICING_TS >= B1_JOB_EXEC1.SLICE_START_DTTM
AND CI_FT1.JRN_SLICING_TS < B1_JOB_EXEC1.SLICE_END_DTTM
 AND ( TRIM(CI_FT1.PARENT_ID) NOT IN (SELECT PARM_VAL FROM <%=odiRef.getSchemaName( "Metadata" , "D" )%>.B1_JOB_PARAM_VW
WHERE ENTITY_NAME='CF_FT_GL'
AND CONTEXT_CD='<%=odiRef.getContext( "CTX_CODE" )%>'
AND PARM_CD='ADJUSTMENT_TYPE')
 OR TRIM(CI_FT1.PARENT_ID) IS NULL)

EXPECTED BEHAVIOR
-----------------------
We have observed a join between CI_FT and CI_FT_GL tables and this join is doing a full scan of these tables, however, there is a filter after this join to consider incremental data. This is happening in two places in the same mapping. Please check and refine the logic to improve the performance of this ETL.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Open the ODI Studio
2. Click in the CF_FT_GL ETL out-of-the-box mapping
3. We observed the performance issue of CF_FT_GL ETL out-of-the-box mapping (B1_F_FT_GL_KEY) during daily incremental data loads while inserting records into the Key table.
 

Solution

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