Query against V$SESSION hangs on 10g RAC systems 2007-11-02 - By M Rafiq
Back
I ran this query on a non rac 10.2.0.3 database and it returned the rows very
quickly. It may be some RAC related issue.
Regards
Rafiq
Date: Fri, 2 Nov 2007 08:35:40 +0100From: exriscer@(protected): snowman327
@(protected): Re: Query against V$SESSION hangs on 10g RAC systemsCC:
oracle-l@(protected)
hi
querying blocking_session is the problem, if you dont query that columnd then
it should be fine
I have a customer who requested a backport to fix the bug but the one-off patch
has no effect so might have to wait until 10.2.0.4
thanks
--
LSC
On 11/1/07, Dean <snowman327@(protected)> wrote:
We recently had a locking/blocking problem on two different 10.2.0.3 RAC
systems. I ran a query similar to below to get more information:
select sid, serial#, program, module, action, username, osuser,
sql_hash_value, machine, event, blocking_session
from v$session
where status='ACTIVE';
About 1 out of 5 times this query hangs. When it hangs, it waits for exactly
60 seconds on "DIAG idle wait" and then returns the result set. If I remove
the blocking_session column, this query runs fine and never hangs. Has anyone
run into this or have any ideas why this would happen?
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ __
Climb to the top of the charts!? Play Star Shuffle:? the word scramble
challenge with star power.
http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_oct
<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class='hmmessage'>
<BR>I ran this query on a non rac 10.2.0.3 database and it returned the rows
very quickly. It may be some RAC related issue.<BR>
<BR>
Regards<BR>
Rafiq<BR>
<BR> <BR>
<BLOCKQUOTE>
<HR>
Date: Fri, 2 Nov 2007 08:35:40 +0100<BR>From: exriscer@(protected)<BR>To:
snowman327@(protected)<BR>Subject: Re: Query against V$SESSION hangs on 10g RAC
systems<BR>CC: oracle-l@(protected)<BR><BR>
<DIV>hi</DIV>
<DIV> </DIV>
<DIV>querying blocking_session is the problem, if you dont query that columnd
then it should be fine</DIV>
<DIV> </DIV>
<DIV>I have a customer who requested a backport to fix the bug but the one-off
patch has no effect so might have to wait until <A href="http://10.2.0.4/"
target=_blank>10.2.0.4</A></DIV>
<DIV> </DIV>
<DIV>thanks</DIV>
<DIV> </DIV>
<DIV>--</DIV>
<DIV>LSC</DIV>
<DIV><BR><BR> </DIV>
<DIV><SPAN class=EC_gmail_quote>On 11/1/07, <B class=EC_gmail_sendername>Dean<
/B> <<A href="mailto:snowman327@(protected)">snowman327@(protected)</A>> wrote
:</SPAN>
<BLOCKQUOTE class=EC_gmail_quote style="PADDING-LEFT: 1ex; BORDER-LEFT: #ccc
1px solid">
<DIV lang=EN-US>
<DIV>
<FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">We
recently had a locking/blocking problem on two different <A href="http://10.2.0
.3/" target=_blank>10.2.0.3</A> RAC systems. I ran a query similar to
below to get more information:</SPAN></FONT><BR>
<FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
;</SPAN></FONT><BR>
<FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">select sid,
serial#, program, module, action, username, osuser,</SPAN></FONT><BR>
<FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">sql_hash_value,
machine, event, blocking_session </SPAN></FONT><BR>
<FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">from v$session <
/SPAN></FONT><BR>
<FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">where status=
'ACTIVE';</SPAN></FONT><BR>
<FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt"> </SPAN><
/FONT><BR>
<FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">About
1 out of 5 times this query hangs. When it hangs, it waits for exactly 60
seconds on "DIAG idle wait" and then returns the result set. If I remove
the blocking_session column, this query runs fine and never hangs. Has
anyone run into this or have any ideas why this would happen? </SPAN></FONT><BR
></DIV></DIV></BLOCKQUOTE></DIV><BR></BLOCKQUOTE><br /><hr />Climb to the top of
the charts!? Play Star Shuffle:? the word scramble challenge with star power.
<a href='http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink
_oct' target='_new'>Play Now!</a></body>
</html>
|
|
