RE: sql statement "hanging" and unable to query v$lock

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 10 Jan 2001 18:05:42 -0800
Message-Id: <10737.126360@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C07B73.002A94E0
Content-Type: text/plain;

charset="iso-8859-1"

> -----Original Message-----
> From: Reardon, Bruce (CALBBAY)
> [mailto:Bruce.Reardon_at_comalco.riotinto.com.au]
> Sent: mercredi, 10. janvier 2001 15:36
> 
> The reply below was assuming the problem is a lock.
> 
> I'd check v$session_event to see / confirm what is really 
> being waited upon.
> 
> See Steve Adam's useful session wait script at
> http://www.ixora.com.au/scripts/waits.htm

That was very helpful. Thanks to Steve Adam's queries, I found that the offending session is doing a lot of buffer busy waits (time waited = 1370297). I dug out my Oracle Performance Tuning book by Richard Niemiec and it advises that you can query v$session_wait on that session: column p1 will be the file id of a segment and column p2 will be the block id causing the contention. The book then recommends querying dba_extents to find out the name of the segment. Unfortunately, when I query dba_extents the query hangs! I looked at the view behind dba_extents and was able to determine that the segment is one in the system tablespace, but when I try to query sys.uet$ to find out about the block_id (44 in my case) the query hangs.

At this point, even if I found out which segment in the system tablespace is causing the problem, I'm not sure what I could do with the information. In any event, thank you for your help. If anyone has any further suggestions I would be glad to hear them.

------_=_NextPart_001_01C07B73.002A94E0
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2652.35">
<TITLE>RE: sql statement "hanging" and unable to query v$lock =
- Oracle 8</TITLE>
</HEAD>
<BODY>

> -----Original Message-----
 > From: Reardon, Bruce (CALBBAY)
 > [<A =

HREF=3D"mailto:Bruce.Reardon_at_comalco.riotinto.com.au">mailto:Bruce.Reard= on_at_comalco.riotinto.com.au</A>]
 > Sent: mercredi, 10. janvier 2001 15:36
 > 
 > The reply below was assuming the problem is a =
lock.
 > 
 > I'd check v$session_event to see / confirm what =
is really 
 > being waited upon.
 > 
 > See Steve Adam's useful session wait script =
at
 > <A =

HREF=3D"http://www.ixora.com.au/scripts/waits.htm" = TARGET=3D"_blank">http://www.ixora.com.au/scripts/waits.htm</A>

 

That was very helpful. Thanks to Steve Adam's =
queries, I found that the offending session is doing a lot of buffer = busy waits (time waited =3D 1370297). I dug out my Oracle Performance = Tuning book by Richard Niemiec and it advises that you can query = v$session_wait on that session: column p1 will be the file id of a = segment and column p2 will be the block id causing the contention. The = book then recommends querying dba_extents to find out the name of the = segment. Unfortunately, when I query dba_extents the query hangs! I = looked at the view behind dba_extents and was able to determine that = the segment is one in the system tablespace, but when I try to query = sys.uet$ to find out about the block_id (44 in my case) the query = hangs.

At this point, even if I found out which segment in =
the system tablespace is causing the problem, I'm not sure what I could = do with the information. In any event, thank you for your help. If = anyone has any further suggestions I would be glad to hear = Received on Wed Jan 10 2001 - 20:05:42 CST

RE: sql statement "hanging" and unable to query v$lock - Oracle 8