Re: How to make a session active with wait event of 'sql*net message FROM client'?

From: Cary Millsap <cary.millsap_at_method-r.com>
Date: Thu, 18 Jun 2009 08:02:24 -0500
Message-ID: <3a2a84fc0906180602o6ca87594rf71337af89394413_at_mail.gmail.com>

There's a description of the phenomenon (including a picture) in *Optimizing Oracle Performance*, pages 184–187.

Cary Millsap
Method R Corporation

http://method-r.com
http://carymillsap.blogspot.com
http://twitter.com/cary_millsap

On Wed, Jun 17, 2009 at 6:13 PM, Bobak, Mark <Mark.Bobak_at_proquest.com>wrote:

> Hmm...I just went to a moderately busy database and had no problems finding
> such sessions:
> SQL> l
> 1* select count(*) From v$session where status='ACTIVE' and event =
> 'SQL*Net message from client'
> SQL> /
>
> COUNT(*)
> ----------
> 9
>
> 1 row selected.
>
> SQL> /
>
> COUNT(*)
> ----------
> 7
>
> 1 row selected.
>
> SQL> /
>
> COUNT(*)
> ----------
> 3
>
> 1 row selected.
>
> SQL> /
>
> COUNT(*)
> ----------
> 6
>
> 1 row selected.
>
> SQL> /
>
> COUNT(*)
> ----------
> 8
>
> 1 row selected.
>
> SQL> /
>
> COUNT(*)
> ----------
> 3
>
> 1 row selected.
>
> SQL> /
>
> COUNT(*)
> ----------
> 0
>
> 1 row selected.
>
> SQL>
>
> This is 11.1.0.7, 64-bit on Linux, if it matters.
>
> But, I think Vladimir hit the nail on the head. There is no guarantee of
> read consistency from V$ (or X$ for that matter). So, even if it doesn't
> make logical sense to have a session that's both STATUS='ACTIVE' and
> EVENT='SQL*Net message from client', I don't think it's a stretch to see
> such situations pop up from time to time. It' just a race condition.
>
> Hope that helps,
>
> -Mark
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Vladimir Andreev
> Sent: Wednesday, June 17, 2009 6:26 PM
> To: fmhabash_at_gmail.com; Oracle-L Group
> Subject: Re: How to make a session active with wait event of 'sql*net
> message FROM client'?
>
> Hi,
> as far as I know, the v$ views are not guaranteed to be consistent,
> and wait events change quickly. So my theory is that you've seen (in
> the real-life situation) the status ACTIVE, but before your query got
> to the wait event it changed.
> So if that's true, you may be able to reproduce it by issuing lots ov
> very short calls to (along the lines of "select 1 from dual" - each
> one of these gives you 3 or 4 rapid-fire calls - parse, execute, and
> one or two fetches, depending on array size) and running your query
> against V$session (10g and above) repeatedly.
> Of course, if I'm right, reproducing the situation will give you no
> useful insight - but you may have your reasons...
> Cheers!
> Flado
>
> On 17/06/2009, Fmhabash <fmhabash_at_gmail.com> wrote:
> > Hi all, am trying to duplicate a real-life situation where a db had
> several
> > sessions with status of active and wait event of sq*net message FROM
> client.
> > So far, am not able to do that. I tried a session with uncommitted DML
> or
> > waiting on a substitution variable, but both show as inactive.
> >
> > How can this status/event combination be reproduced?
> >
> >
> > ---------------
> > Thank You.
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
> --
> Sent from my mobile device
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Thu Jun 18 2009 - 08:02:24 CDT