oracle

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:

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:

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

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.