gv$ges_blocking_enqueue unreliable on 10.2.0.4 ?

we had an issue recently on our 2-node 10.2.0.4 RAC cluster with sessions waiting on 'library cache pin'. Our monitoring scripts did not pick it up and I've been able to reproduce as follows ("test2" is a stored procedure that just calls dbms_lock.sleep(600)) :

· On node 1 :

SQL> select sys_context('USERENV', 'SID') from dual;

SYS_CONTEXT('USERENV','SID')

585

SQL> exec test2;

· On node 2 :

SQL> select sys_context('USERENV', 'SID') from dual;

SYS_CONTEXT('USERENV','SID')

601

SQL> alter procedure test2 compile;

So now on node1 we’re executing and on node 2 we’re waiting to compile;

· select inst_id, sid, blocking_session, blocking_instance, blocking_session_status, event from gv$session where event like 'libr%' :

2     601                                      UNKNOWN        library cache

So shows we’re waiting but with no info as to the blocker

· gv$ges_blocking_enqueue shows no info related to these sessions

If I add a third session trying to execute waiting behind the first 2 (1 blocking 2 blocking 3), then I still see nothing in gv$ges_blocking_enqueue.

It gets really strange If I wait for the first session to finish sleeping/executing. The compile then goes through, and the third session is then allowed to execute.

If I then try to compile again in the second session and execute in the first, I basically have the same situation in reverse order (3 blocking 2 blocking 1) and now I DO see this in gv$ges_blocking_enqueue ... but only session 2 blocking 1, not 3 blocking 2.

Has anyone else encountered this ? Am I misinterpreting what should be in gv$ges_blocking_enqueue ? Or is there a more reliable way to find these blockers ?

Thanks & regards

Jo

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

--
http://www.freelists.org/webpage/oracle-l