Get-well dashboard CIs missing SN job has slow DB view sn_getwell_hardware_ci_class_viewDescriptionQuerying too many data in hardware table which caused the performance issue Result of CSTASK1119447CIs missing SN jobgetHardwareCiClasses: function() { var gr = new GlideAggregate('sn_getwell_hardware_ci_class_view'); gr.groupBy('db_sys_id'); gr.query(); var list = []; while (gr.next()) list.push(gr.db_sys_id.toString()); var lists = list.join(','); gr = new GlideRecord('sys_db_object'); gr.addEncodedQuery('sys_idIN' + lists, true); gr.query(); while (gr.next()) this.addNewHardwareClass(gr); } instance bttsm sys_db_object: 13,149cmdb_ci_hardware: 34,194,257sn_getwell_hardware_ci_class: 42Steps to Reproduce On an instance with a large CMDB (e.g. a large telecoms customer), run "CMDB Get Well Metric Collection", from the CMDB/CSDM Data Foundations Dashboard app.Expected behaviour:This job shjould be quick, and not impct the Instance as a wholeActual behaviour:This query has been seen to take over 51 minutes using CMDB Data Foundations Dashboard, and cause high HLL on the database, impacting user experience.The slow query will be seen in the sys_query_pattern table with hash/pattern "1635478265":e.g.hash: 1635478265count: 297average: 1252881.54total: 1970-01-05 07:21:45last: 2025-06-16 09:15:00first: 2024-08-16 07:03:35url: CMDB Get Well Metric Collectionexample: SELECT `db_sys_metadata`.`sys_id` AS `db_sys_id` FROM ((sys_db_object `db` INNER JOIN sys_metadata `db_sys_metadata` ON `db`.`sys_id` = `db_sys_metadata`.`sys_id` ) INNER JOIN (cmdb `h` INNER JOIN cmdb$par1 `h_cmdb_par1` ON `h`.`sys_id` = `h_cmdb_par1`.`sys_id`) ON `h`.`sys_class_name` = `db`.`name` AND `h`.`install_status` = 1.0 AND `h`.`operational_status` = 1.0 AND `h`.`sys_class_path` LIKE '/!!/!2%' ) GROUP BY `db_sys_metadata`.`sys_id` ORDER BY `db_sys_metadata`.`sys_id`Explain Plain:+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | type |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 1 | SIMPLE | db | index | PRIMARY,name | name | 243 | None | 11236 | Using where; Using index; Using temporary; Using filesort | index || 1 | SIMPLE | db_sys_metadata | eq_ref | PRIMARY | PRIMARY | 96 | bttsm_1.db.sys_id | 1 | Using index | eq_ref || 1 | SIMPLE | h | ref | PRIMARY,sys_class_name,install_status,sys_class_path_2,CHG52785583_idx2,CHG53722677_1,sys_class_path,sys_class_path_3 | sys_class_name | 243 | bttsm_1.db.name | 339 | Using where | ref || 1 | SIMPLE | h_cmdb_par1 | eq_ref | PRIMARY | PRIMARY | 96 | bttsm_1.h.sys_id | 1 | Using index | eq_ref |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+stack_trace: glide.scheduler.worker.3com.glide.db.stats.AbstractMetricStats.presentSampleToListeners(AbstractMetricStats.java:26)com.glide.db.stats.query.QueryStats.recordSample(QueryStats.java:49)com.glide.db.stats.query.QueryStats.lambda$recordQuery$0(QueryStats.java:36)java.base/java.util.Optional.ifPresent(Optional.java:178)com.glide.db.stats.query.QueryStats.recordQuery(QueryStats.java:26)com.glide.db.DBI.saveStats(DBI.java:2109)com.glide.db.DBI.sqlVerbosity(DBI.java:2100)com.glide.db.DBI.executeStatement(DBI.java:1543)com.glide.db.DBQueryExecutor.executeStatement(DBQueryExecutor.java:382)com.glide.db.DBQueryExecutor.executeStatement(DBQueryExecutor.java:369)com.glide.db.DBQueryExecutor.executeWithCategoryRouting(DBQueryExecutor.java:135)com.glide.db.DBQueryExecutor.execute(DBQueryExecutor.java:116)com.glide.db.DBQuery.executeAsResultSet0(DBQuery.java:838)com.glide.db.DBQuery.executeAndReturnTable(DBQuery.java:714)com.glide.db.DBAction.executeNormal(DBAction.java:414)com.glide.db.DBAction.executeAndReturnException(DBAction.java:360)com.glide.db.DBAction.execute(DBAction.java:276)com.glide.db.DBQuery.execute(DBQuery.java:3889)com.glide.db.meta.Table.queryAggregate(Table.java:508)com.glide.db.meta.Table.query(Table.java:233)com.glide.script.GlideRecordITable.query(GlideRecordITable.java:132)com.glide.script.GlideRecord.query0(GlideRecord.java:3603)com.glide.script.GlideRecord.query(GlideRecord.java:3301)com.glide.script.GlideAggregate.query(GlideAggregate.java:726)com.glide.script.GlideRecord.jsFunction_query(GlideRecord.java:3183)com.glide.script.fencing.ScopedGlideAggregate.jsFunction_query(ScopedGlideAggregate.java:64)jdk.internal.reflect.GeneratedMethodAccessor272.invoke(Unknown Source)java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)java.base/java.lang.reflect.Method.invoke(Method.java:569)org.mozilla.javascript.MemberBox.invoke(MemberBox.java:236)org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:674)org.mozilla.javascript.FunctionObject.call(FunctionObject.java:603)org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2988)org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1928)org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1112)org.mozilla.javascript.InterpretedFunction.lambda$call$0(InterpretedFunction.java:161)com.glide.caller.gen.sys_script_include_d458a81273a110107073d3c72bf6a739_script.call(Unknown Source)com.glide.script.ScriptCaller.call(ScriptCaller.java:22)org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:160)org.mozilla.javascript.ScriptRuntime.doCall2(ScriptRuntime.java:3089)org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2998)org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1928)org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1112)org.mozilla.javascript.InterpretedFunction.lambda$call$0(InterpretedFunction.java:161)com.glide.caller.gen.sys_script_include_a5540eb2732110107073d3c72bf6a775_script.call(Unknown Source)com.glide.script.ScriptCaller.call(ScriptCaller.java:22)org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:160)org.mozilla.javascript.ScriptRuntime.doCall2(ScriptRuntime.java:3089)org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2998)org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1928)org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1112)org.mozilla.javascript.InterpretedFunction.lambda$call$0(InterpretedFunction.java:161)com.glide.caller.gen._refname_.call(Unknown Source)com.glide.script.ScriptCaller.call(ScriptCaller.java:22)org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:160)org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:719)org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:4412)org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:174)com.glide.script.ScriptCompiler.executeAndPublishMetric(ScriptCompiler.java:77)com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:439)com.glide.script.ScriptEvaluator.evaluate(ScriptEvaluator.java:219)com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:361)com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:149)com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:140)com.glide.script.Evaluator.evaluateString(Evaluator.java:104)com.snc.automation.ScriptJob.executeInSingleDomain(ScriptJob.java:77)com.snc.automation.ScriptJob.execute(ScriptJob.java:47)com.glide.schedule.JobExecutor.lambda$executeJob$1(JobExecutor.java:195)com.snc.db.data_replicate.replicator.DataReplicationAdvisors.runInOriginatorContext(DataReplicationAdvisors.java:74)com.glide.schedule.JobExecutor.lambda$inDataReplicationContext$3(JobExecutor.java:225)com.glide.schedule.JobExecutor.executeJob(JobExecutor.java:198)com.glide.schedule.JobExecutor.execute(JobExecutor.java:178)com.glide.schedule.JobExecutor.execute(JobExecutor.java:168)com.glide.schedule_v2.SchedulerWorkerThread.executeJob(SchedulerWorkerThread.java:609)com.glide.schedule_v2.SchedulerWorkerThread.lambda$process$2(SchedulerWorkerThread.java:402)com.glide.worker.TransactionalWorkerThread.executeInTransaction(TransactionalWorkerThread.java:35)com.glide.schedule_v2.SchedulerWorkerThread.process(SchedulerWorkerThread.java:402)com.glide.schedule_v2.SchedulerWorkerThread.run(SchedulerWorkerThread.java:178)WorkaroundThis issue has been fixed in 4.2 or higher versions. 1. app-cmdb-get-well-dashboard version below v2.2 No recommended workaround available for this version. Please upgrade to latest version 4.1.2 or higher version. 2. app-cmdb-get-well-dashboard version on v2.2 The metric can be deactivated on sn_getwell_metric Go to sn_getwell_metric.listFind the slow-running metricSet active=false This will deactivate the metric, and prevent it from being picked up by any subsequent jobs. 3. app-cmdb-get-well-dashboard version on v2.3+ The metric can be deactivated on sn_getwell_metric Go to sn_getwell_metric.listFind the slow-running metricSet active=false This will deactivate the metric, and prevent it from being picked up by any subsequent jobs.Additionally, this version supports auto deactivation of the slow-running job if it exceeds our runtime threshold for 3 times. Some metrics may need higher version to support this. For each version's supported metric, please refer to KB0558230Related Problem: PRB1906065