ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

BSI Metrics Profiler

book

Article ID: 212727

calendar_today

Updated On:

Products

CA Business Service Insight

Issue/Introduction

This is a database query designed to highlight how "heavy" each metric in a Business Service Insight environment is, by measuring different features.

By that it also gives a general idea of how ‘heavy’ the entire system is. The ‘heaviness’ of the metrics can eventually have an effect on the system overall performance and specifically on the calculation time required by the ACE engine.

If the measurement value is low then the result for this measurement is shown as ‘Low’, otherwise the measurement value itself is returned.

The features, which are measured on every metric, are:

IsClustered (‘Yes’/‘No’) – Is the metric clustered
cluster# (‘Low’/Value) – Value refers to the amount of cluster item events. Value is given if more than 999
changes# (‘Low’/Value) – Value refers to the amount of metric changes. Value is given if more than 100
MAXstateLines# (‘Low’/Value) – Value refers to the maximum state lines. Value is given if more than 10
AVGstateLines# (‘Low’/Value) – Value refers to the average state lines. Value is given if the maximum states lines is more than 10
SLOutputs# (‘Low’/Value) – Value refers to the amount of entries in Slalom Outputs table. Value is given if more than 10000
SendEvents# (‘Low’/Value) – Value refers to the amount of sent events (in the intermediate data table). Value is given if more than 10000
Events# (‘Low’/Value) – Value refers to the amount of events (in raw data table). Value is given if more than 10000 within the last 60 days
MAXGAPDAYS# (‘Low’/Value) – Value refers to the maximum gap of days between the create date and last change date of the events (in raw data table). Value is given if more than 30
AVGGAPDAYS# (‘Low’/Value) – Value refers to the average gap of days between the create date and last change date of the events (in raw data table). Value is given if MAXGAPDAYS# value is given.

When tuning an environment, we recommend grouping metrics together on different PSL writers roughly by overall weight - if a few "heavy" metrics share a writer with many "light" ones, then it is both more difficult to tune the configuration of that writer to be suitable for both, and the time spent processing the more intensive ones will impact the performance of the others as they wait their turn.

 

Environment

Release : 8.35

Component : CA OBLICORE GUARANTEE AP FOR MSPS

Resolution

<<<<MetricsProfiler.sql<<<<
select rules.sla_name contract,
       rules.global_rule_name metric,
       decode(rules.cluster_id, 0, 'No', 'Yes') IsClusterd,
       decode(a.cluster#, null, 'Low', a.cluster#) cluster#,
       decode(b.changes#, null, 'Low', b.changes#) changes#,
       decode(c.MAXstateLines#, null, 'Low', c.MAXstateLines#) MAXstateLines#,
       decode(c.AVGstateLines#, null, 'Low', c.AVGstateLines#) AVGstateLines#,
       decode(d.SLOutput#, null, 'Low', d.SLOutput#) SLOutputs#,
       decode(e.SendEvent#, null, 'Low', e.SendEvent#) SendEvents#,
       decode(f.Event#, null, 'Low', f.Event#) Events#,
       decode(f.MAXGAPDAYS#, null, 'Low', f.MAXGAPDAYS#) MAXGAPDAYS#,
       decode(f.AVGGAPDAYS#, null, 'Low', f.MAXGAPDAYS#) AVGGAPDAYS#
  from (select unique s.sla_id * gr.global_rule_id id,
               sla_name,
               gr.global_rule_name,
               r.cluster_id
          from t_rules r, t_global_rules gr, t_slas s
         where gr.global_rule_id = r.global_rule_id
           and s.sla_id = gr.sla_id
           and r.rule_id > 999) rules,
       (select s.sla_id * gr.global_rule_id id,
               sla_name,
               gr.global_rule_name,
               count(*) cluster#
          from t_rules                       r,
               t_global_rules                gr,
               t_slas                        s,
               t_formula_cluster_item_events fce
         where gr.global_rule_id = r.global_rule_id
           and fce.formula_id = r.formula_id
           and s.sla_id = gr.sla_id
         group by s.sla_id * gr.global_rule_id, sla_name, global_rule_name
        having count(*) > 1000) A,
       (select s.sla_id * gr.global_rule_id id,
               sla_name,
               gr.global_rule_name,
               count(*) changes#
          from t_rules                        r,
               t_global_rules                 gr,
               t_slas                         s,
               t_formula_infrastruct_versions fev
         where gr.global_rule_id = r.global_rule_id
           and fev.formula_id = r.formula_id
           and s.sla_id = gr.sla_id
         group by s.sla_id * gr.global_rule_id, sla_name, global_rule_name
        having count(*) > 100) B,
       (select s.sla_id * gr.global_rule_id id,
               sla_name,
               gr.global_rule_name,
               max(line_number) MAXstateLines#,
               round(avg(line_number)) AVGstateLines#
          from t_rules            r,
               t_global_rules     gr,
               t_slas             s,
               t_rule_state_lines rsl,
               t_rule_states      rs
         where gr.global_rule_id = r.global_rule_id
           and s.sla_id = gr.sla_id
           and rs.rule_id = r.psl_rule_id
           and rs.level_id <> -2
           and rs.state_id = rsl.state_id
         group by s.sla_id * gr.global_rule_id, sla_name, global_rule_name
        having max(line_number) > 10) C,
       (select s.sla_id * gr.global_rule_id id,
               sla_name,
               gr.global_rule_name,
               count(*) SLOutput#
          from t_rules r, t_global_rules gr, t_slas s, t_slalom_outputs sl
         where gr.global_rule_id = r.global_rule_id
           and s.sla_id = gr.sla_id
           and sl.rule_id = r.psl_rule_id
         group by s.sla_id * gr.global_rule_id, sla_name, global_rule_name
        having count(*) > 10000) D,
       (select s.sla_id * gr.global_rule_id id,
               sla_name,
               gr.global_rule_name,
               count(*) SendEvent#
          from t_global_rules gr, t_slas s, t_intermediate_data idata
         where gr.global_rule_id = idata.metric_global_id
           and s.sla_id = gr.sla_id
         group by s.sla_id * gr.global_rule_id, sla_name, global_rule_name
        having count(*) > 10000) E,
       (select s.sla_id * gr.global_rule_id id,
               sla_name,
               gr.global_rule_name,
               count(*) Event#,
               round(max(rd.CREATE_DATE - rd.TIME_STAMP)) MAXGAPDAYS#,
               round(avg(rd.CREATE_DATE - rd.TIME_STAMP)) AVGGAPDAYS#
          from t_global_rules   gr,
               t_slas           s,
               t_rules          r,
               t_formula_events fe,
               t_raw_data       rd
         where gr.global_rule_id = r.global_rule_id
           and s.sla_id = gr.sla_id
           and r.formula_id = fe.formula_id
           and fe.event_type_id = rd.event_type_id
           and fe.resource_id = rd.resource_id
           and fe.create_version_date < rd.time_stamp
           and fe.delete_version_date > rd.time_stamp
           and rd.create_date > SYSDATE - 60
         group by s.sla_id * gr.global_rule_id, sla_name, global_rule_name
        having count(*) > 10000 or max(rd.CREATE_DATE - rd.TIME_STAMP) > 30) F
 where rules.id = a.id(+)
   and rules.id = b.id(+)
   and rules.id = c.id(+)
   and rules.id = d.id(+)
   and rules.id = e.id(+)
   and rules.id = f.id(+)
 order by rules.sla_name, rules.global_rule_name

>>>>MetricsProfiler.sql>>>>

Note: Depending on the size and complexity of the system, this query may take some time to be completed.