MDM Processing SQL Delay Investigation (Doc ID 2697563.1)

Last updated on JULY 05, 2021

Applies to:

Goal

On : 2.2.0.3.0 version, System Wide

MDM processing SQL delay investigation

Customer have some SQL's that is taking a long time to process and they don't know what function that SQL is using, so they are asking to identify the feature.


SQL 1 :- SELECT DISTINCT REL.PK_VALUE1 AS D1_DEVICE_ID FROM D1_DVC_EVT EVT, D1_DVC_EVT_REL_OBJ REL, F1_BUS_OBJ BO, F1_BUS_OBJ_STATUS BS WHERE REL.PK_VALUE1 BETWEEN :1 AND :2 AND REL.MAINT_OBJ_CD = :3 AND REL.DVC_EVT_REL_OBJ_TYPE_FLG = :4 AND EVT.DVC_EVT_ID = REL.DVC_EVT_ID AND EVT.BUS_OBJ_CD = BO.BUS_OBJ_CD AND BO.LIFE_CYCLE_BO_CD = :5 AND BO.LIFE_CYCLE_BO_CD = BS.BUS_OBJ_CD AND BS.BO_STATUS_CD = EVT.BO_STATUS_CD AND BS.BO_STATUS_COND_FLG <> :6 ORDER BY D1_DEVICE_ID

SQL 2 :- SELECT REL.DVC_EVT_ID FROM D1_DVC_EVT EVT, D1_DVC_EVT_REL_OBJ REL, F1_BUS_OBJ BO, F1_BUS_OBJ_STATUS BS WHERE REL.PK_VALUE1 = :1 AND REL.MAINT_OBJ_CD = :2 AND REL.DVC_EVT_REL_OBJ_TYPE_FLG = :3 AND EVT.DVC_EVT_ID = REL.DVC_EVT_ID AND EVT.BUS_OBJ_CD = BO.BUS_OBJ_CD AND BO.LIFE_CYCLE_BO_CD = :4 AND BO.LIFE_CYCLE_BO_CD = BS.BUS_OBJ_CD AND BS.BO_STATUS_CD = EVT.BO_STATUS_CD AND BS.BO_STATUS_COND_FLG <> :5 ORDER BY EVT.DVC_EVT_DTTM, EVT.CRE_DTTM

SQL 3 :- UPDATE D1_MSRMT SET MSRMT_VAL =:1 , ORIG_INIT_MSRMT_ID =:2 , MSRMT_COND_FLG=:3 , MSRMT_USE_FLG =:4 , PREV_MSRMT_DTTM =:5 , MSRMT_VAL1 =:6 , MSRMT_VAL2 =:7 , MSRMT_VAL3 =:8 , MSRMT_VAL4 =:9 , MSRMT_VAL5=:10 , MSRMT_VAL6=:11 , MSRMT_VAL7=:12 , MSRMT_VAL8=:13 , MSRMT_VAL9=:14 , MSRMT_VAL10=:15 , USER_EDITED_FLG=:16 , BO_STATUS_CD=:17 , STATUS_UPD_DTTM=:18 , LAST_UPDATE_DTTM=:19 WHERE MEASR_COMP_ID=:20 AND MSRMT_DTTM=:21

Solution

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