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

Anonymous
  • Step into the future of visual technology with KL Multimedia’s groundbreaking fog projection system. This innovative display uses a fine mist to create holographic visuals that seem to float in mid-air—captivating viewers with a magical, dreamlike experience. Ideal for events, retail, and exhibitions, fog projection delivers unforgettable moments that elevate brand storytelling and audience engagement. Discover how your space can be transformed into a futuristic visual playground. Learn more and explore the possibilities at https://www.klmultimedia.com/fog-projection.html.

Related Content