Hi, Even we also faced performance and High CPU utilization issue in DB system. We have identified that Normalization job has configured with Continuous mode and Just we modified that configuration to Scheduled. Now we are not facing the issue.
Try to find the reason, and if you Identify any long running query, then you can Identify the fix. Since In out of the box functionality that Reconciliation Id validation may mandatory. Regards, Pandi. C On Thu, Feb 2, 2017 at 6:31 PM, Murnane, Phil <[email protected]> wrote: > ** > > Hello ARSListers, > > > I have a customer with a 7.6.04 CMDB reconciliation job that is generating > a poor-performing SQL query (roughly 1,000 ms to process). I can't find > any filters that generate the query, so I'm guessing it's resulting from a > plug-in. There's not much to do about how the plug-in generates its > queries unless some of the WHERE clause parameters are stored as data. > Does anyone have any insight into whether the *ReconciliationIdentity *phrase > of the WHERE clause below is stored as data somewhere, and if so then > where? I'd sure like to replace that *...!= '0' *comparison to avoid a > full table scan. > > > Here's the raw query right out of the log: > > > 317978 SQL 0001446061 List 390696 Remedy Application Service Thu Feb 02 > 2017 08:19:04.3394 SELECT * FROM ( SELECT T505.C1,C400129200,C179,C400127400 > FROM T505 WHERE ((1 = 1) AND (T505.C400129200 != '0') AND (T505.C400079600 > = 'BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS') AND ((T505.C530041601 = 30) OR > (T505.C530041601 = 60) OR (T505.C530041601 = 50)) AND (T505.C400127400 = > 'BMC.ADDM') AND (T505.C530060100 = 40) AND (T505.C400079600 = > 'BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS')) > ORDER BY C400129200 ASC, 1 ASC ) WHERE ROWNUM <= 100001 > 319025 SQL 0001446061 List 390696 Remedy Application Service Thu Feb 02 > 2017 08:20:26.6110 OK > > > ...and here's a friendly version: > > > SELECT BMC.CORE:BMC_Component.RequestId, ReconciliationIdentity, > InstanceId, DatasetId > FROM BMC.CORE:BMC_Component > WHERE > ( > (1 = 1) AND > (BMC.CORE:BMC_Component.ReconciliationIdentity != '0') AND > (BMC.CORE:BMC_Component.ClassId = 'BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS') > AND > ( > (BMC.CORE:BMC_Component.NormalizationStatus = 30) > OR (BMC.CORE:BMC_Component.NormalizationStatus = 60) > OR (BMC.CORE:BMC_Component.NormalizationStatus = 50) > ) AND > (BMC.CORE:BMC_Component.DatasetId = 'BMC.ADDM') AND > (BMC.CORE:BMC_Component.ReconciliationMergeStatus = 40) AND > (BMC.CORE:BMC_Component.ClassId = 'BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS') > ) > ORDER BY ReconciliationIdentity ASC, 1 ASC > > > Thanks for any thoughts, > > --Phil > _ARSlist: "Where the Answers Are" and have been for 20 years_ -- C.Pandi _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
