Custom Alert - Locking Problems

Description

This alert will run against the monitored Oracle databases and retrieve the time a session has been waiting ("Seconds Blocked" column) on a blocking session.  It will also give some details about the blocking session in the "Message" column and this can be customized with anything else from v$session.

ALERT DEFINITION

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

Alert Name: Locking Problems

Execution Interval: 10 Minutes

Notification Text: The following sessions are being blocked for more than 30 seconds

SQL Statement:

select 'SID ' || blocker.sid || ' running ' || blocker.program ||

       ' from machine ' || blocker.machine ||

       ' logged in as ' || blocker.username ||

       ' has been blocking SID ' || blocked.sid ||

       ' for ' || blocked.last_call_et || ' seconds.' "Message", blocked.last_call_et "Seconds Blocked"

from v$session blocked

inner join v$session blocker on blocker.sid = blocked.blocking_session

order by blocked.blocking_session

Execute Against: Monitored Instance

Units: Seconds Blocked

High Threshold: Min 30, Max empty

Medium Threshold: Min 20, Max 30

  • I tried this custom alert on our DPA server and it returns lines like this in our alert.

    Parameter: SID 376 running ORACLE.EXE (QMNC) from machine 47 logged in as  has been blocking SID 11 for 511 seconds.
         Value: 511
     
    The local OS user or blocker.username looks like it is missing in our alert.  I can look at the GUI and see the client info of local OS user, source machine, and Oracle login.
     
    Is it possible to get the blocker and blocked SID client info in the alert with the SQL commands - including source machine, local OS user, and Oracle login? 
     
    Thanks