Re: Library cache pin waits

From: srivenu <srivenu_at_hotmail.com>
Date: 12 Apr 2004 00:27:50 -0700
Message-ID: <1a68177.0404112327.638ba108@posting.google.com>

> select ename
> from emp
> where emp_no = :EMPNO;

Do you use a lot of dynamic SQL ?

What is the mode of wait ?
Run this to find out.

col wsid Head "Waiting|Session" form 9999 col hsid head "Holding|Session" form 9999 col lock_or_pin head "Lock|Or|Pin" form a5 col object head "Object Locked Or Pinned" form a30 col mode_held head "Mode|Held" form a12
col mode_requested head "Mode|Requested" form a12

select /*+ ordered */ w1.sid wsid,
h1.sid hsid,
w.kgllktype lock_or_pin,
o.kglnaobj object,

decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1,x$kglob o
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
and o.kglhdadr = w.kgllkhdl

/

A session_cache_cursors value of 1000 seems to high.

> If that is the case then would this make a difference?
> select /*some session identifier string*/ ename
> from emp
> where emp_no = :EMPNO;

This would cause hard parsing and waits on SHARED POOL latch & LIBRARY CACHE latches.

regards
Srivenu Received on Mon Apr 12 2004 - 02:27:50 CDT