On a customer call recently and the customer was asking if our Foglight for Oracle agent included an alert for blocking locks.
This is a rare question for me, since Oracle database customers don’t tend to see issues with blocking locks compared to SQL Server. This is because Oracle, by default uses MVCC (Multi-Version Concurrency Control), meaning readers do not block writers and writers do not block readers. While SQL Server defaults to blocking selects on uncommitted updates, this behaviour can be changed to MVCC by enabling the Read Committed Snapshot database option.
You can see the blocking sessions by running a query on dba_blockers
In Foglight there is an alarm, and it depends if the configuration uses the SQL PI feature or not.
If you don’t have SQL PI configured, you will see an alarm called DBO - Long Lock Running and the appropriate dashboard is shown below, but there isn’t any history, only details of the blocking if the issue is still current.
The alarm will fire after the default setting of 30 secs. This threshold can be modified.
Here is the alarm dialogue:
If you have SQLPI configured, then the appropriate alarm is DBO - SPI Long Lock Running and you would review the blocking dashboard in SQLPI at the time of the alarm.
Here is the alarm:
The duration threshold is 90 seconds.
The key benefit is that the alarm can be investigated in real time or historically. You can click om Current time, or Alarm time as shown in the alarm dialogue.
Here is the SQL PI Blocking History dashboard:
If you have problems with some sessions locking objects, you could pre-empt an issue by looking into locked objects in the SQL PI dimension tree.
Here you can see the objects involved in blocking as opposed to the queries.
For more information: Foglight for Oracle
