Re: library cache load lock

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 6 Jul 2005 20:27:12 +0100
Message-ID: <bf463805070612276218262d@mail.gmail.com>

Sorry, I was shooting from the hip there.

You may want to examine the dba_kgllock view.

The following bit of SQL was taken from article 169139.1 on MetaLink, and may
be useful in pinning down the problem.

select /*+ ordered */ w1.sid waiting_session, h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,

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 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
/

On 7/6/05, Sanjay Mishra <smishra_97_at_yahoo.com> wrote:
>
> Jared
> Thanks for quick reply. I had no record in both blockers and Waiters
> view. I had killed the session appeared in DBa_jobs_running and even killed
> the snp_process and now I am not getting any snp process started even the
> Job_queue_process are 8.
> ANy suggestions. Lock are still same for all sid appeared in
> Dba_jobs_runing and appeared in v$session_wait
> Thanks
> Sanjay
>
> *Jared Still <jkstill_at_gmail.com>* wrote:
>
> Have you examined dba_blockers/dba_waiters to see which session is causing
> the contention?
>
> Maybe it needs to be killed?
>
> We have a similar issue at times with a misbehaving app. The processes on
> the app server
> dies, leaving a lock on a table.
>
> TCP being what it is, the database never knows that the client process has
> died, and we
> ( meaning me) must go kill the process.
>
> Yes, I have a potential work-around for that, still waiting for user
> testing.
>
> Jared
>
>
> On 7/6/05, Sanjay Mishra <smishra_97_at_yahoo.com> wrote:
> >
> > I have several of these shown in the v$session_wait. Any idea as what
> > need to be done. SOme of the them are linked to mine Job QUeue process. So
> > all 7 Job processa re hanged and each have is showing this lock
> > TIA
> > Sanjay
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> >
>
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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

Received on Wed Jul 06 2005 - 14:31:13 CDT