Re: Gv$session.blocking_session_status=UNKNOWN
Date: Mon, 2 Mar 2009 11:03:52 -0600
Message-ID: <203315c10903020903n506073b0icdf303b717b31c9c_at_mail.gmail.com>
Mark
Looks like, last few bits of x$ksuse.ksuseblocker seems to indicate this blocking_session_status. I don't know the answer to your question directly, other than that, commit_propagation_delay might play some role in delayed updates to these statuses and further there is no consistent read mechanisms to these views.
gv$session.blocking_session_status is defined as (from v$fixed_view_definition):
decode(s.ksuseblocker,4294967295,'UNKNOWN', 4294967294,'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER', 4294967291,'NOT IN WAIT','VALID'),
11111111111111111111111111111111 - unknown 11111111111111111111111111111110 - unknown 11111111111111111111111111111101 - unknown 11111111111111111111111111111100 - no holder 11111111111111111111111111111011 - not in wait
But, if you are looking at enqueues in RAC environment, then you might want to access gv$ges_blocking_enqueue and gv$ges_enqueue views.
BTW, these views (gv$ges_enqueue and gv$ges_blocking_enqueue) also encompasses x$kjbl which is for buffer cache locks (old PCM stuff). So, if SGA is bigger, then access to this views can be quite costly. So, if the intention is to simply check for non-buffer cache (BL) related enqueues, then you might want to use following code accessing directly x$kjilkft
with locked_pids as (
select inst_id, kjilkftlkp, kjilkftgl, kjilkftrl, kjilkftrn1, kjilkftrn2,
kjilkftpid, kjilkftxid0,
kjilkftxid1, kjilkftgid, kjilkftoodd, kjilkftoopt, kjilkftoopo,
kjilkftoonxid, kjilkftcogv,
kjilkftcopv, kjilkftconv, kjilkftcodv, kjilkftconq, kjilkftcoep,
kjilkftconddw, kjilkftconddb,
kjilkftwq, kjilkftls, kjilkftaste0, kjilkfton, kjilkftblked, kjilkftblker
from x$kjilkft
where kjilkftrl != 'KJUSENL' and ( kjilkftblked=1 or kjilkftblker=1)
)
select /*+ ORDERED */ lp.*,b.sid , b.username, b.status, b.sql_address,
b.sql_hash_value, b.PREV_SQL_ADDR, b.PREV_HASH_VALUE, b.module, b.module, b.ROW_WAIT_OBJ#,b.ROW_WAIT_FILE#, b.ROW_WAIT_BLOCK#, ROW_WAIT_ROW#from locked_pids lp, gv$process a, gv$session b where a.addr=b.paddr
and a.inst_id =b.inst_id
and lp.kjilkftpid=a.spid
and lp.inst_id=a.inst_id
;
Sorry , my 10g RAC instance is not accessible at this point to test your scenario.
-- Cheers Riyaj Shamsudeen Principal DBA, Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com On Mon, Mar 2, 2009 at 1:14 AM, Teehan, Mark <mark.teehan_at_credit-suisse.com>wrote:Received on Mon Mar 02 2009 - 11:03:52 CST
> 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-l