Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Library cache pin waits
> 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
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
![]() |
![]() |