Gv$session.blocking_session_status=UNKNOWN
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, 10.2.0.4. 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 (10.2.0.4) 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 10.2.0.4.
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?
Thanks!
Mark
Singapore
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 02 2009 - 01:14:09 CST