Wednesday Aug 31, 2011

How to create a consolidated backlog indicator report for EM11

While working with customers, and looking through some of the setups and configurations of Enterprise Manager, one question typically comes up all the time:

Q) How do I know my Enterprise Manager site is running healthy?

And the one thing, that is by far the most important for Enterprise Manager, is to keep an eye on the backlog indicators Enterprise Manager itself is collecting.
By looking at those numbers, you can more or less predict performance 'hiccups', or some bad behavior starting to form.

To help Enterprise Administrators keeping an eye on these important infrastructure metrics, a report can be created in Enterprise Manager to show all the relevant data in one single page.
To create this report, follow these steps:

  • In the Console, go to the 'Reports' tab, and use the 'Create' button to create a new report
  • On the report creation screen, enter this information
General Tab
 Title      : Backlog Indicators
 Category   : Enterprise Manager Setup
 Subcategory: Enterprise Manager Health
 Target     : Specify a 'specific' one:
              Name: Management Services and Repository
              Type: OMS and Repository
 Time Period: Check the 'Allow the report viewer to customize the time period' checkbox

Elements Tab
Add 8 elements, 4 charts, 4 tables, and use the layout button to represent then on 4 rows like this:

     Row 1   
       Chart from SQL
       Table from SQL
     Row 2
       Chart from SQL
       Table from SQL
     Row 3
       Chart from SQL
       Table from SQL
     Row 4
       Chart from SQL
       Table from SQL

  • Details for Row 1 (XML Loader backlog)
    Chart:
    Header    : XML Loader Backlog - Historical
    Type      : Timeseries chart
    Legend    : Bottom
    SQL to use:

          SELECT SUBSTR(key_value,0,INSTR(key_value,':')-1), rollup_timestamp, average
          FROM   sysman.mgmt$metric_hourly
          WHERE  target_guid = ??EMIP_BIND_TARGET_GUID??
            AND  metric_guid = HEXTORAW('B72713257822A65853FDF0C77554F660')
            AND  rollup_timestamp BETWEEN ??EMIP_BIND_START_DATE?? AND ??EMIP_BIND_END_DATE??

          ORDER BY rollup_timestamp, key_value

Table:
Header: XML Loader Backlog - Current
SQL to use:
      SELECT SUBSTR(key_value,0,INSTR(key_value,':')-1), value
      FROM   sysman.mgmt$metric_current
      WHERE  target_guid = ??EMIP_BIND_TARGET_GUID??
        AND  metric_guid = HEXTORAW('B72713257822A65853FDF0C77554F660')
      ORDER BY key_value

  • Details for Row 2 (EM Job backlog)
    Chart:
    Header: Job Backlog - Historical
    Type  : Timeseries chart
    Legend: Bottom
    SQL to use:
          SELECT 'System', rollup_timestamp, average
          FROM   sysman.mgmt$metric_hourly
          WHERE  target_guid = ??EMIP_BIND_TARGET_GUID??
            AND  metric_guid = HEXTORAW('CA4FF4BB045B18ADD7CA465C47A696F5')
            AND  rollup_timestamp BETWEEN ??EMIP_BIND_START_DATE?? AND ??EMIP_BIND_END_DATE??
          ORDER BY rollup_timestamp
Table:
Header: Job Backlog - Current
SQL to use:
      SELECT 'System', value
      FROM   sysman.mgmt$metric_current
      WHERE  target_guid = ??EMIP_BIND_TARGET_GUID??
        AND  metric_guid = HEXTORAW('CA4FF4BB045B18ADD7CA465C47A696F5')
  • Details for Row 3 (Notification backlog)
    Chart:
    Header: Notification Delivery Backlog - Historical
    Type  : Timeseries chart
    Legend: Bottom
    SQL to use:
          SELECT key_value, rollup_timestamp, average
          FROM   sysman.mgmt$metric_hourly
          WHERE  target_guid = ??EMIP_BIND_TARGET_GUID??
            AND  metric_guid = HEXTORAW('F4450F5AD8E95174CBFA21A261D5993C')
            AND  rollup_timestamp BETWEEN ??EMIP_BIND_START_DATE?? AND ??EMIP_BIND_END_DATE??
            AND  key_value != 'RCA'
          ORDER BY rollup_timestamp, key_value
Table:
Header: Notification Delivery Backlog - Current
SQL to use:
      SELECT key_value, value
      FROM   sysman.mgmt$metric_current
      WHERE  target_guid = ??EMIP_BIND_TARGET_GUID??
        AND  metric_guid = HEXTORAW('F4450F5AD8E95174CBFA21A261D5993C')
        AND  key_value != 'RCA'
      ORDER BY key_value

  • Details for Row 4 (Repository Metrics backlog)
    Chart::
    Header: Repository Metrics Backlog - Historical
    Type  : Timeseries chart
    Legend: Bottom
    SQL to use:
          SELECT key_value, rollup_timestamp, average
          FROM   sysman.mgmt$metric_hourly
          WHERE  target_guid = ??EMIP_BIND_TARGET_GUID??
            AND  metric_guid = HEXTORAW('5175E215E86BCCD6A55CF3D883B6AF2D')
            AND  rollup_timestamp BETWEEN ??EMIP_BIND_START_DATE?? AND ??EMIP_BIND_END_DATE??
          ORDER BY rollup_timestamp, key_value
Table:
Header: Repository Metrics Backlog - Current
SQL to use:
      SELECT key_value, value
      FROM   sysman.mgmt$metric_current
      WHERE  target_guid = ??EMIP_BIND_TARGET_GUID??
        AND  metric_guid = HEXTORAW('5175E215E86BCCD6A55CF3D883B6AF2D')
      ORDER BY key_value
  • Now save the report, and click on it, so you can see the consolidated backlog report of the site.
    On the top of the screen, you will have the option to set a time period, to report on backlog of a given period.
    The default is the last 24 hours, but you can change this to any period containing hourly rollup date (which typically will be the last 31 days, unless the system has been tweaked to change this default retention time)
About

Latest information and perspectives on Oracle Enterprise Manager.

Related Blogs




Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
3
5
6
7
9
10
11
12
13
14
15
17
18
19
20
23
24
25
26
27
28
29
30
   
       
Today