This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Alert - Average Blocking Time per Session

Description

This alert will calculate the average blocking time per session and helps you understand the severity of the issue. It is similar to the Total Wait Time for Blocking - Repository alert in that it uses the data already in the repository, but instead of returning a total amount of blocking time, it divides that by the number of sessions being blocked. For example, there could be 200 sessions being blocked for 2-3 seconds, and you would get 600 seconds of blocking and be notified with the other alert mentioned. However, the problem is not too bad because each individual session was not blocked very long. However, if the scenario was instead 20 sessions blocked for 600 seconds, this alert would give you 30 seconds on average which may be a different severity.

Also see the Alternative section below if you want to retrieve the session with the maximum blocking time, i.e. the worst case scenario.

Alert Definition

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:

Alert Name: Blocking Time per Session

Execution Interval: <= 10 Minutes down to 1 Minute

Notification Text: This instance just had a blocking problem where the average blocking time per session is below.

Active Database Instances: Add the Ignite Repository DB only

SQL Statement (Pick the proper statement below depending on if your Repository is on SQL Server or Oracle)


SQL Server Repository Statement:

SELECT COALESCE(AVG(timesecs), 0) avg_blocking_timesecs

FROM (

   SELECT sw.vdsi, ISNULL(SUM(sw.qp)/100,0) timesecs

   FROM consw_#DBID# sw

   INNER JOIN conev_#DBID# ev ON sw.keeq = ev.id

   WHERE sw.d >= DATEADD(n, -#FREQUENCY#, CURRENT_TIMESTAMP)

   AND ev.name LIKE 'LCK%'

   GROUP BY sw.vdsi) sw

Oracle Repository Statement:

SELECT NVL(AVG(timesecs), 0) avg_blocking_timesecs

FROM (

   SELECT sw.vdsi, NVL(SUM(sw.qp)/100,0) timesecs

   FROM consw_#DBID# sw

   INNER JOIN conev_#DBID# ev ON sw.keeq = ev.id

   WHERE sw.d >= CURRENT_TIMESTAMP - (#FREQUENCY# / 1440)

   AND ev.name LIKE 'enq%'

   GROUP BY sw.vdsi) sw


Execute Against: Repository

Units: Average Blocking Time (secs)

High Threshold: Min 30, Max empty

Medium Threshold: Min 15, Max 30

Alternative

Instead of summing the time with:

SELECT COALESCE(SUM(timesecs) / COUNT(1), 0) avg_blocking_timesecs

you could also use a MAX function to get the worst case blocking scenario with:

SELECT COALESCE(MAX(timesecs),0) max_blocking_timesecs

Parents
  • The database info is stored in the CONSW_XX.IXOY column, and it joins to the CONO_XX table. Adding that join gives us something like this where you can replace 'master' with whatever you need:

    SELECT COALESCE(AVG(timesecs), 0) avg_blocking_timesecs

    FROM (

       SELECT sw.VDSI, ISNULL(SUM(sw.QP)/100,0) timesecs

       FROM ignite.CONSW_#DBID# sw

       INNER JOIN ignite.CONEV_#DBID# ev ON sw.KEEQ = ev.ID

       INNER JOIN ignite.CONO_#DBID# db ON sw.IXOY = db.ID

       WHERE sw.D >= DATEADD(n, -#FREQUENCY#, CURRENT_TIMESTAMP)

       AND ev.NAME LIKE 'LCK%'

       AND db.NAME = 'master'

       GROUP BY sw.VDSI) sw

Reply
  • The database info is stored in the CONSW_XX.IXOY column, and it joins to the CONO_XX table. Adding that join gives us something like this where you can replace 'master' with whatever you need:

    SELECT COALESCE(AVG(timesecs), 0) avg_blocking_timesecs

    FROM (

       SELECT sw.VDSI, ISNULL(SUM(sw.QP)/100,0) timesecs

       FROM ignite.CONSW_#DBID# sw

       INNER JOIN ignite.CONEV_#DBID# ev ON sw.KEEQ = ev.ID

       INNER JOIN ignite.CONO_#DBID# db ON sw.IXOY = db.ID

       WHERE sw.D >= DATEADD(n, -#FREQUENCY#, CURRENT_TIMESTAMP)

       AND ev.NAME LIKE 'LCK%'

       AND db.NAME = 'master'

       GROUP BY sw.VDSI) sw

Children
No Data