Hi @pthonus ,

Could you please post the query which is showing the performance issue?

Thanks,

Priyanjala
 

It is the standard CRM query to search by customer number but here it is. 

select * from

  ( select /*+ FIRST_ROWS(100) */ a.*, rownum r

  from ( SELECT  vmo_company.idCUS col0, 'vmo_company' idTbl, vmo_company.CustNo col2, vmo_company.PhSwitch col3, TRIM(NVL(USR_59.FIRSTNAME,'') || ' ' || USR_59.LASTNAME) col4, TRIM(NVL(USR_58.FIRSTNAME,'') || ' ' || USR_58.LASTNAME) col5, vmo_company.Name col6, vmo_company.Address1 col7, vmo_company.Address2 col8, vmo_company.City col9, OPT_53.CODEDESC col10, vmo_company.ZipCode col11, vmo_company.County col12, OPT_50.CODEDESC col13, vmo_company.VisitAddress1 col14, vmo_company.VisitAddress2 col15, vmo_company.VisitCity col16, OPT_46.CODEDESC col17, vmo_company.VisitZipCode col18, vmo_company.VisitCounty col19, OPT_43.CODEDESC col20, vmo_company.HomePhone col21, vmo_company.CellPhone col22, vmo_company.PhFax col23, vmo_company.www col24, vmo_company.EMail col25, OPT_37.CODEDESC col26, OPT_36.CODEDESC col27, OPT_35.CODEDESC col28, OPT_34.CODEDESC col29, OPT_33.CODEDESC col30, vmo_company.PlakLeadInfo col31, OPT_31.CODEDESC col32, vmo_company.YoungDentalLeadInfo col33, OPT_29.CODEDESC col34, DECODE( vmo_company.idCODBCO ,'bco.005' ,OPT_28.CODEDESC ,'bco.009' ,OPT_28.CODEDESC ,'bco.008' ,OPT_28.CODEDESC ,'bco.012' ,OPT_28.CODEDESC ,'bco.011' ,OPT_28.CODEDESC ,'bco.013' ,OPT_28.CODEDESC ,'bco.015' ,OPT_28.CODEDESC ,'bco.014' ,OPT_28.CODEDESC ,'bco.004' ,OPT_28.CODEDESC ,'bco.003' ,OPT_28.CODEDESC ,'bco.001' ,OPT_28.CODEDESC ,'bco.002' ,OPT_28.CODEDESC , '') col35, DECODE( vmo_company.idCODBCO ,'bco.010' ,vmo_company.ClassificationOther , '') col36, vmo_company.RootCanalsWeek col37, vmo_company.NbrDentists col38, vmo_company.NbrHygienists col39, vmo_company.NbrOperatories col40, vmo_company.NbrAssistants col41, OPT_21.CODEDESC col42, DECODE(vmo_company.DaysOfOperation, NULL,VMO_BASE_CLIENT_UTIL_API.Get_Multiple_Choice_Values( 'vmo_company.DaysOfOperation', vmo_company.idCUS, 'en' )) col43, DECODE(vmo_company.PlakComp01, NULL,VMO_BASE_CLIENT_UTIL_API.Get_Multiple_Choice_Values( 'vmo_company.PlakComp01', vmo_company.idCUS, 'en' )) col44, DECODE(vmo_company.PlakComp02, NULL,VMO_BASE_CLIENT_UTIL_API.Get_Multiple_Choice_Values( 'vmo_company.PlakComp02', vmo_company.idCUS, 'en' )) col45, OPT_17.CODEDESC col46, DECODE(vmo_company.ObtuComp01, NULL,VMO_BASE_CLIENT_UTIL_API.Get_Multiple_Choice_Values( 'vmo_company.ObtuComp01', vmo_company.idCUS, 'en' )) col47, DECODE(vmo_company.ObtuComp02, NULL,VMO_BASE_CLIENT_UTIL_API.Get_Multiple_Choice_Values( 'vmo_company.ObtuComp02', vmo_company.idCUS, 'en' )) col48, DECODE(vmo_company.ObruComp04, NULL,VMO_BASE_CLIENT_UTIL_API.Get_Multiple_Choice_Values( 'vmo_company.ObruComp04', vmo_company.idCUS, 'en' )) col49, DECODE(vmo_company.FulfillComp01, NULL,VMO_BASE_CLIENT_UTIL_API.Get_Multiple_Choice_Values( 'vmo_company.FulfillComp01', vmo_company.idCUS, 'en' )) col50, TRIM(NVL(USR_12.FIRSTNAME,'') || ' ' || USR_12.LASTNAME) col51, TRIM(NVL(USR_11.FIRSTNAME,'') || ' ' || USR_11.LASTNAME) col52, TRIM(NVL(USR_10.FIRSTNAME,'') || ' ' || USR_10.LASTNAME) col53, TRIM(NVL(USR_9.FIRSTNAME,'') || ' ' || USR_9.LASTNAME) col54, vmo_company.Created col55, vmo_company.Updated col56, vmo_company.exText1 col57, vmo_company.exText2 col58, vmo_company.NaczoLookup col59, DECODE( vmo_company.idCODCCA ,'cca.002' ,OPT_3.CODEDESC , '') col60, DECODE( vmo_company.idCODCCA ,'cca.002' ,OPT_2.CODEDESC , '') col61, OPT_1.CODEDESC col62

  FROM vmo_company, VMO_BASE_USER USR_59, VMO_BASE_USER USR_58, VMO_BASE_CODEDESC OPT_53, VMO_BASE_CODEDESC OPT_50, VMO_BASE_CODEDESC OPT_46, VMO_BASE_CODEDESC OPT_43, VMO_BASE_CODEDESC OPT_37, VMO_BASE_CODEDESC OPT_36, VMO_BASE_CODEDESC OPT_35, VMO_BASE_CODEDESC OPT_34, VMO_BASE_CODEDESC OPT_33, VMO_BASE_CODEDESC OPT_31, VMO_BASE_CODEDESC OPT_29, VMO_BASE_CODEDESC OPT_28, VMO_BASE_CODEDESC OPT_21, VMO_BASE_CODEDESC OPT_17, VMO_BASE_USER USR_12, VMO_BASE_USER USR_11, VMO_BASE_USER USR_10, VMO_BASE_USER USR_9, VMO_BASE_CODEDESC OPT_3, VMO_BASE_CODEDESC OPT_2, VMO_BASE_CODEDESC OPT_1 WHERE (USR_59.IDUSER (+)= vmo_company.idUSER2) AND (USR_58.IDUSER (+)= vmo_company.idUser) AND (OPT_53.idCOD (+)= vmo_company.IdCODSTA AND OPT_53.idLNG (+)= 'en')

  AND (OPT_50.idCOD (+)= vmo_company.idCODICC AND OPT_50.idLNG (+)= 'en') AND (OPT_46.idCOD (+)= vmo_company.idCODVST AND OPT_46.idLNG (+)= 'en') AND (OPT_43.idCOD (+)= vmo_company.idCODVIC AND OPT_43.idLNG (+)= 'en') AND (OPT_37.idCOD (+)= vmo_company.CategoryPanoramic AND OPT_37.idLNG (+)= 'en') AND (OPT_36.idCOD (+)= vmo_company.CategoryPlakSmacker AND OPT_36.idLNG (+)= 'en') AND (OPT_35.idCOD (+)= vmo_company.CategoryMicrobrush AND OPT_35.idLNG (+)= 'en') AND (OPT_34.idCOD (+)= vmo_company.CategoryYoungDental AND OPT_34.idLNG (+)= 'en') AND (OPT_33.idCOD (+)= vmo_company.PlakLeadType AND OPT_33.idLNG (+)= 'en') AND (OPT_31.idCOD (+)= vmo_company.YoungDentalLeadType AND OPT_31.idLNG (+)= 'en') AND (OPT_29.idCOD (+)= vmo_company.idCODBCO AND OPT_29.idLNG (+)= 'en') AND (OPT_28.idCOD (+)= vmo_company.SubClassification AND OPT_28.idLNG (+)= 'en') AND (OPT_21.idCOD (+)= vmo_company.PrimarySupplier AND OPT_21.idLNG (+)= 'en') AND (OPT_17.idCOD (+)= vmo_company.PlakImprintedDental AND OPT_17.idLNG (+)= 'en') AND (USR_12.IDUSER (+)= vmo_company.idUSER4) AND (USR_11.IDUSER (+)= vmo_company.idUser5) AND (USR_10.IDUSER (+)= vmo_company.idUserCRE) AND (USR_9.IDUSER (+)= vmo_company.idUserUPD) AND (OPT_3.idCOD (+)= vmo_company.idCODDEL AND OPT_3.idLNG (+)= 'en')

  AND (OPT_2.idCOD (+)= vmo_company.idCODSHI AND OPT_2.idLNG (+)= 'en') AND (OPT_1.idCOD (+)= vmo_company.idCODCCA AND OPT_1.idLNG (+)= 'en') AND

  ((UPPER(vmo_company.CustNo) LIKE UPPER('%747452%'))) ORDER BY 3, 1) a

  where rownum <= '100' ) where r >= '1'