Query against V$SESSION hangs on 10g RAC systems 2007-11-02 - By Finn Jorgensen
Back
I'm just taking a wild guess here, but to get blocking sessions it's
possible the query of v$session would have to go to the other node(s) of the
RAC cluster for session information, since the blocking session may be on a
different node. 60 seconds is the time RHEL RAC takes to time out if a node
cannot be reached, which could explain that exact timeout.
Why the query works sometimes and not others, I don't know. Bug?
Finn
On 11/2/07, M Rafiq <rafiq9857@(protected)> wrote:
>
>
> 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 +0100
> From: exriscer@(protected)
> To: snowman327@(protected)
> Subject: Re: Query against V$SESSION hangs on 10g RAC systems
> CC: 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. Play Now!<http://club.live.com/star_shuffle.aspx
?icid=starshuffle_wlmailtextlink_oct>
>
<div>I'm just taking a wild guess here, but to get blocking sessions it'
;s possible the query of v$session would have to go to the other node(s) of the
RAC cluster for session information, since the blocking session may be on a
different node. 60 seconds is the time RHEL RAC takes to time out if a node
cannot be reached, which could explain that exact timeout.
</div>
<div> </div>
<div>Why the query works sometimes and not others, I don't know. Bug?</div>
<div> </div>
<div>Finn<br><br> </div>
<div><span class="gmail_quote">On 11/2/07, <b class="gmail_sendername">M Rafiq<
/b> <<a href="mailto:rafiq9857@(protected)">rafiq9857@(protected)</a>>
wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">
<div><br>I ran this query on a non rac <a onclick="return top.js.OpenExtLink
(window,event,this)" href="http://10.2.0.3/" target="_blank">10.2.0.3</a>
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: <a onclick="return top.js
.OpenExtLink(window,event,this)" href="mailto:exriscer@(protected)" target="_blank
">exriscer@(protected)</a><br>To: <a onclick="return top.js.OpenExtLink(window
,event,this)" href="mailto:snowman327@(protected)" target="_blank">
snowman327@(protected)</a><br>Subject: Re: Query against V$SESSION hangs on 10g
RAC systems<br>CC: <a onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:oracle-l@(protected)" target="_blank">oracle-l@(protected)
</a><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 onclick="return top.js
.OpenExtLink(window,event,this)" 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>On 11/1/07, <b>Dean</b> <<a onclick="return top.js.OpenExtLink
(window,event,this)" href="mailto:snowman327@(protected)" target="_blank"
>snowman327@(protected)</a>> wrote:</span>
<blockquote 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 onclick=
"return top.js.OpenExtLink(window,event,this)" 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 onclick="return top.js.OpenExtLink
(window,event,this)" href="http://club.live.com/star_shuffle.aspx?icid
=starshuffle_wlmailtextlink_oct" target="_blank">
Play Now!</a></div></blockquote></div><br>
|
|
