From: Teehan, Mark <mark.teehan_at_credit-suisse.com>
Date: Mon, 2 Mar 2009 15:14:09 +0800
Message-ID: <C9A9CAFAD768BB46A215D83CFA13BDAD0450188D_at_ESNG17P32001B.csfb.cs-group.com>

Hi -
I am trying to figure out when
gv$session.blocking_Session_Status=UNKNOWN on a RAC database, This is for an inhouse monitoring tool that needs to pass back the session information whenever sessions are waiting on other sessions over a time threshold.

I thought it was caused when the querying session is on a different instance to one, or both of the locked sessions; but this doesn't seem to be the case.

Tests show a few scenarios on a 2 node RAC ( with 2 sessions issuing a simple update statement, and two monitoring sessions; one on each node.

  • Status=VALID and a blocking_session is returned for any combination of locking sessions/monitoring sessions. I.e. it doesn't seem to matter if the locking sessions are on the same node or different nodes; or if the monitoring sessions are on the same node or a different node.
  • Status=UNKNOWN, and no blocking_session is returned, for one of the monitoring sessions, briefly. After a few seconds, all queries return VALID; this appears to be instance sync and normal behaviour.
  • Status= UNKNOWN for both monitoring sessions indefinitely. This one I cannot figure out. It is rare, and I am unable to diagnose why it happens.

I am aware of bug 5010879, which adds one to the Blocking_Session when the sessions are on the same instance.
The fix for this triggered 5884519, which returns no data after a 1 minute (or so) query time. Fixed in

A metalink note confirms that UNKNOWN will be returned "when the blocking session cannot be determined". There appears to be a design reason for this: does anyone know what it is?


Please access the attached hyperlink for an important electronic communications disclaimer:


Received on Mon Mar 02 2009 - 01:14:09 CST

Original text of this message