Why 2 rows in x$kgllk for package .

From: GG <grzegorzof_at_interia.pl>
Date: Thu, 16 Feb 2012 19:27:21 +0100
Message-ID: <4F3D4A89.6080904_at_interia.pl>

Hi,
  while playing with Tanel query for reporting sessions which suffer from global variables state change in 9.2.0.8 DB . I've found something strange (for me) :

select t.* , (select sid from v$session where saddr = t.KGLLKUSE) sid from x$kgllk t
where

     kglnahsh in (select /*+ no_unnest */ kglnahsh
                  from x$kglob
                  where
                       upper(kglnaown) like upper('TEST')
                  and  upper(kglnaobj) like upper('DEMO_PKG')
                 )

Lets focus on KGLLKFLG column.
So looks like after getting breakable parse look KGLLKFLG = 0 then after package is recompiled there is 0 and 3 reported for 2 rows related to one session which executed package procedure .

After recompilation the KGLLKFLG is 2 and 3 and looks like when we ask for value 3 we get what we want
so sessions which after running recompiled package reports ORA-04068 .

Question is why we have 2 rows in x$kgllk for 1 session (is that because separate lock for package header and package body )? Next one, Im not sure what is the meaning of columns: KGLHDNSP
KGLLKSPN Any ideas ?
Thanks
GregG

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

Received on Thu Feb 16 2012 - 12:27:21 CST