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
