Gv$session.blocking_session_status=UNKNOWN
- From: "Teehan, Mark" <mark.teehan@xxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 2 Mar 2009 15:14:09 +0800
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
==============================================================================
Other related posts:
