RE: How to determine sessions with invalid package states
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: "'Riyaj Shamsudeen'" <riyaj.shamsudeen@xxxxxxxxx>
- Date: Fri, 8 May 2009 12:39:35 +0300
Riyaj,
My original scripts purpose was to show library cache locks by a hash value,
so it just selected from x$kgllk where kglnahsh = ( &1 ). Note that I'm not
exactly joining to x$kglob but was just looking up the hash value(s) from
there. So I just manually passed the hash value of an object as a parameter.
Before posting the script I edited it to make it a bit more convenient so
one could pass owner and object name in, instead of having to figure out the
hash value.
Otherwise there's no reason to use hash here. If I was to write a join I
would also join by (hash and) object address to avoid cartesian product in
case of child cursors. Note that there seems to be an indexed access path
available when using x$kgllk.kglnahsh (as seen from v$indexed_fixed_column)
but in mu test lookups by hash value weren't faster than by address.
Tanel.
_____
From: Riyaj Shamsudeen [mailto:riyaj.shamsudeen@xxxxxxxxx]
Sent: 07 May 2009 23:23
To: tanel@xxxxxxxxxx
Cc: rgravens@xxxxxxxxx; oracle-l-freelists
Subject: Re: How to determine sessions with invalid package states
Tanel
It is interesting you are using kglnahsh as a join key between x$kgllk and
x$kglob. Is there any reason for that? I usually use x$kglob.kglhdadr =
x$kgllk.kgllkhdl..
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
Other related posts: