Oracle 7.3 - Oracle 23ai 20+ years of experience Version (Released: )

Oracle blocking session analysis

26 Apr 2019

Oracle blocking session analysis is a most common task in many database systems having a high concurrency between Oracle sessions and update contention. If a database session blocks a record for an extended period of time, this can have highly negative consequences – even affecting a productive system in the worst case. It is therefore very important to quickly identify the cause behind such blocks.

Using KeepTool Hora, our most powerful Oracle database tool, it has always been possible to make analyzes through the session monitor, such as:

  • Which sessions are currently connected to the database?
  • Which sessions have a currently transaction?
  • Which session currently holds a lock
  • Which session is waiting for a ressouce?
  • Which SQL statement is currently executed by the session?
  • Which client user and terminal belongs the session to?

In the case of blocking locks, the “Lock Wait Graph” page shows you a dependency tree of blocking and waiting sessions. In the root node, the tree view shows the blocking session and properties as follows:

  • SID (session ID of blocking session)
  • Terminal (client computer name)
  • Program (client application)
  • User (Oracle user the client connection is using)

Below that tree node the sessions are listed that are waiting on the blocking session. Each child node shows the following information:

  • SID (sesssion ID of waiting ession)
  • Lock mode

Below that node you may see further sessions, if the waiting session is a blocker itself and other sessions are waiting for it.

New in Version 14.0.6 is the following feature: If a session holds a lock, you can retrieve now the information which data record exactly is locked. This makes it possible to analyze blocking problems in more depth.

In the case of an emergency, any user having DBA privileges can kill the blocking session using either a button on the toolbar or the context menu. This will release the blocking lock and the waiting sessions can continue their work.

Using KeepTool’s session monitor, Oracle blocking session analysis is now much more easier.

See how it works!

Explore possibilities of KeepTool.