Re: library cache load lock
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Sanjay Mishra <smishra_97@xxxxxxxxx>
- Date: Wed, 6 Jul 2005 20:27:12 +0100
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@xxxxxxxxx> 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@xxxxxxxxx>* 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@xxxxxxxxx> 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
Other related posts:
