5 Replies Latest reply on Nov 20, 2020 1:12 PM by Anuvinda Kulkarni

    MDM SQL running in the backend causing performance issues

    Vijay Samptoor Guru

      We have the following sql being triggered in mdm backend (db) through out. We have many realtime sources putting/updating data into MDM and we are constantly being bombarded by this query throughout. We assume this might be some internal process within mdm as a result of an external source/api invoking this.

       

      Query

      *******

       

      SELECT ROWID_OBJECT, CONSOLIDATION_IND, DELETED_IND, DELETED_BY, DELETED_DATE, CREATE_DATE, UPDATED_BY, LAST_UPDATE_DATE, CREATOR, HUB_STATE_IND, INTERACTION_ID, DIRTY_IND, LAST_ROWID_SYSTEM, PRTY_CUST_ROLE_FL, PRTY_PERS_ROLE_FL, PRTY_FST_NM, PRTY_MID_NM, PRTY_LAST_NM, PRTY_FULL_NM, PRTY_CUST_INFRML_NM, PRTY_PERS_PREF_NM, PRTY_NM_FMT_EXCPTN_FL, PRTY_TITL_SFX_CD, PRTY_TKN_SSN_NUM, PRTY_BIRTH_DT, PRTY_GNDR_CD, PRTY_GNDR_DSC, PRTY_PERS_ORG_HIRE_DT, PRTY_PERS_RCNT_HIRE_DT, PRTY_PERS_TERM_DT, PRTY_SRC_HR_PERS_ID, PRTY_DCSD_FL, PRTY_CUST_INFO_SHRNG_FL, PRTY_CUST_CMPY_EBR_FL, PRTY_LDRSHP_LVL_CD, PRTY_LDRSHP_LVL_DSC, PRTY_TRUVUE_TYP_CD, PRTY_TRUVUE_PIN_ID, PRTY_ORIG_FST_NM, PRTY_ORIG_MID_NM, PRTY_ORIG_LST_NM, PRTY_ORIG_TITL_SFX_CD, PRTY_ORIG_GEND_IND, PRTY_NA_SKEY, PRTY_PERS_BEG_DT, PRTY_PERS_END_DT, PRTY_PB_UPDT_FL, NAME_PFX_CD_FK, NM_UPDT_RSN_ID, NM_SFX_CD_FK, PRTY_SRC_CRE_DT, PRTY_SRC_CRE_BY_TYP, PRTY_SRC_UPDT_DT, PRTY_SRC_UPDT_BY_TYP, PRTY_HIDE_SRC_TXT, PRTY_CNTCT_METH_TYP_CD, ROWID_BO_CLA SS, PRTY_RLNSHP_OKTOEMAIL_FL, PRTY_PERS_CNTCT_LST_ELG_FL, PRTY_CUST_LAST_UPD_CNTRY, EXPRSS_CPTR_FL, EXPRSS_CPTR_SRC_PRVD_FL, DATA_SUBSOURCE_TYP_ID, NO_MERGE_TOKEN, PRTY_PSTL_CNTCT_PREF FROM (SELECT *FROM (SELECT ROW_NUMBER() OVER (ORDER BY ROWID_OBJECT) AS ROW_NUMBER, C_BO_PRTY.* FROM (SELECT C_BO_PRTY.ROWID_OBJECT, C_BO_PRTY.CONSOLIDATION_IND, C_BO_PRTY.DELETED_IND, C_BO_PRTY.DELETED_BY, C_BO_PRTY.DELETED_DATE, C_BO_PRTY.CREATE_DATE, C_BO_PRTY.UPDATED_BY, C_BO_PRTY.LAST_UPDATE_DATE, C_BO_PRTY.CREATOR, C_BO_PRTY.HUB_STATE_IND, C_BO_PRTY.INTERACTION_ID, C_BO_PRTY.DIRTY_IND, C_BO_PRTY.LAST_ROWID_SYSTEM, C_BO_PRTY.PRTY_CUST_ROLE_FL, C_BO_PRTY.PRTY_PERS_ROLE_FL, C_BO_PRTY.PRTY_FST_NM, C_BO_PRTY.PRTY_MID_NM, C_BO_PRTY.PRTY_LAST_NM, C_BO_PRTY.PRTY_FULL_NM, C_BO_PRTY.PRTY_CUST_INFRML_NM, C_BO_PRTY.PRTY_PERS_PREF_NM, C_BO_PRTY.PRTY_NM_FMT_EXCPTN_FL, C_BO_PRTY.PRTY_TITL_SFX_CD, C_BO_PRTY.PRTY_TKN_SSN_NUM, C_BO_PRTY.PRTY_BIRTH_DT, C_BO_PRTY.PRTY_GNDR_CD, C_BO_PRTY.PRTY _GNDR_DSC, C_BO_PRTY.PRTY_PERS_ORG_HIRE_DT, C_BO_PRTY.PRTY_PERS_RCNT_HIRE_DT, C_BO_PRTY.PRTY_PERS_TERM_DT, C_BO_PRTY.PRTY_SRC_HR_PERS_ID, C_BO_PRTY.PRTY_DCSD_FL, C_BO_PRTY.PRTY_CUST_INFO_SHRNG_FL, C_BO_PRTY.PRTY_CUST_CMPY_EBR_FL, C_BO_PRTY.PRTY_LDRSHP_LVL_CD, C_BO_PRTY.PRTY_LDRSHP_LVL_DSC, C_BO_PRTY.PRTY_TRUVUE_TYP_CD, C_BO_PRTY.PRTY_TRUVUE_PIN_ID, C_BO_PRTY.PRTY_ORIG_FST_NM, C_BO_PRTY.PRTY_ORIG_MID_NM, C_BO_PRTY.PRTY_ORIG_LST_NM, C_BO_PRTY.PRTY_ORIG_TITL_SFX_CD, C_BO_PRTY.PRTY_ORIG_GEND_IND, C_BO_PRTY.PRTY_NA_SKEY, C_BO_PRTY.PRTY_PERS_BEG_DT, C_BO_PRTY.PRTY_PERS_END_DT, C_BO_PRTY.PRTY_PB_UPDT_FL, C_BO_PRTY.NAME_PFX_CD_FK, C_BO_PRTY.NM_UPDT_RSN_ID, C_BO_PRTY.NM_SFX_CD_FK, C_BO_PRTY.PRTY_SRC_CRE_DT, C_BO_PRTY.PRTY_SRC_CRE_BY_TYP, C_BO_PRTY.PRTY_SRC_UPDT_DT, C_BO_PRTY.PRTY_SRC_UPDT_BY_TYP, C_BO_PRTY.PRTY_HIDE_SRC_TXT, C_BO_PRTY.PRTY_CNTCT_METH_TYP_CD, C_BO_PRTY.ROWID_BO_CLASS, C_BO_PRTY.PRTY_RLNSHP_OKTOEMAIL_FL, C_BO_PRTY.PRTY_PERS_CNTCT_LST_ELG_FL, C_BO_ PRTY.PRTY_CUST_LAST_UPD_CNTRY, C_BO_PRTY.EXPRSS_CPTR_FL, C_BO_PRTY.EXPRSS_CPTR_SRC_PRVD_FL, C_BO_PRTY.DATA_SUBSOURCE_TYP_ID, C_BO_PRTY.NO_MERGE_TOKEN, C_BO_PRTY.PRTY_PSTL_CNTCT_PREF FROM C_BO_PRTY WHERE C_BO_PRTY.HUB_STATE_IND IN (:1 )) C_BO_PRTY)T_OUTER WHERE ROW_NUMBER BETWEEN 1 AND 2001) T_OUTER_OUTER

       

      Interesting thing is, it is trying to fetch the entire data from the main customer table (which in our case is huge - to the tune of millions) and ordering them by oracle ROWNUMBER () analytical function and selecting the top 2000.

      Any help in identifying the source of this would be highly appreciated.