Why 2 rows in x$kgllk for package .
Date: Thu, 16 Feb 2012 19:27:21 +0100
while playing with Tanel query for reporting sessions which suffer from global variables state change in 188.8.131.52 DB . I've found something strange (for me) :
select t.* , (select sid from v$session where saddr = t.KGLLKUSE) sid
from x$kgllk t
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:
KGLLKSPN Any ideas ?