Re: The same session run on both instances (RAC), is it normal?

There is something more..I re-ran the query and it looks like audsid is the only field that could be seen across the instances for the same user. So, sid is still unique...wasn't able to replicate your scenario.

On 9/27/07, Rajeev Prabhakar <rprabha01_at_gmail.com> wrote:
>
> Hi qihua wu
> **
> Coz you are using a gv$ view, a parallel execution is
> taking place. The following query should help in that
> regard..
>
> select a.inst_id, a.sid,a.serial#, b.sid,
> a.qcsid,b.osuser,b.username,b.machine,
> b.command, b.serial#,
> b.inst_id
> from gv$px_session a, gv$session b
> where a.sid=b.sid
> and b.username='<NAME OF USER HERE>'
> and a.serial#=b.serial#
> and a.inst_id=b.inst_id
> -Rajeev
>
>
> > On 9/27/07, qihua wu <staywithpin_at_gmail.com > wrote:
> > >
> > > select username, machine, osuser, to_char(logon_time, 'YYYY-MM-DD
> > > HH24:MI') LG ,last_call_et
> > > from gv$session
> > > where username = 'GDBA_WUQIH'
> > >
> > > GDBA_WUQIH ASIAPACIFIC\SCN7872 wuqih 2007-09-27 17:23 0
> > > GDBA_WUQIH ASIAPACIFIC\SCN7872 wuqih 2007-09-27 16:40 0
> > > GDBA_WUQIH ASIAPACIFIC\SCN7872 wuqih 2007-09-27 16:40 0
> > > GDBA_WUQIH ASIAPACIFIC\SCN7872 wuqih 2007-09-27 17:23 0
> > >
> > > From the result, the session connects to two instances at the same
> > > time. it's amazing. Is it correct that a session connect to two nodes?
> > >
> > > On 9/27/07, LS Cheng <exriscer_at_gmail.com > wrote:
> > > >
> > > > I dont work with PL/SQL Developer so I am not sure what it does
> > > > underwood. But this is very easy to check
> > > >
> > > > select username, machine, module, osuser, to_char(logon_time,
> > > > 'YYYY-MM-DD HH24:MI') LG ,last_call_et
> > > > from gv$session
> > > > where username = 'GDBA_WUQIH'
> > > >
> > > > Thanks
> > > >
> > > > --
> > > > LSC
> > > >
> > > >
> > > >
> > > > On 9/27/07, qihua wu <staywithpin_at_gmail.com > wrote:
> > > > >
> > > > > select inst_id,count(*) from gv$session where
> > > > > schemaname='GDBA_WUQIH' group by inst_id
> > > > > 1 2
> > > > > 2 2
> > > > >
> > > > > And I have only two session through plsql devloper. Each of these
> > > > > sessions lands on both instances. That surprise me. One session sits on both
> > > > > instance at the same time.
> > > > >
> > > > > On 9/27/07, LS Cheng <exriscer_at_gmail.com> wrote:
> > > > > >
> > > > > > please add a predicate for your user and not groupping at global
> > > > > > level because for a idle RAC system there are quite a few background
> > > > > > processes and RAC monitoring processes and if you have dbconsole then more
> > > > > >
> > > > > > thanks
> > > > > >
> > > > > > --
> > > > > > LSC
> > > > > >
> > > > > >
> > > > > >
> > > > > > On 9/27/07, qihua wu <staywithpin_at_gmail.com > wrote:
> > > > > > >
> > > > > > > I don't know why ask for groupping by inst_id, anyway the
> > > > > > > result is.
> > > > > > > select inst_id,count(*) from gv$session group by inst_id
> > > > > > > 1 36
> > > > > > > 2 39
> > > > > > >
> > > > > > > David may be correct that sid is not unique accross instance,
> > > > > > > but why gv$mystat will show non-zero stats value on both instances(since
> > > > > > > non-zeror statatics, that means the session operates on both instance).
> > > > > > >
> > > > > > > Dan:
> > > > > > > a session connects to the database multiple times, it still
> > > > > > > has the same session id, otherwise they are not the same session, right?
> > > > > > >
> > > > > > > My question is "one session can only land on one instance
> > > > > > > during its lifetime?".
> > > > > > >
> > > > > > > Thanks very much for your reply. Have a good day.
> > > > > > >
> > > > > > > On 9/26/07, LS Cheng <exriscer_at_gmail.com> wrote:
> > > > > > > >
> > > > > > > > how about group by inst_id...?
> > > > > > > >
> > > > > > > > thanks
> > > > > > > >
> > > > > > > > --
> > > > > > > > LSC
> > > > > > > >
> > > > > > > >
> > > > > > > > On 9/27/07, qihua wu < staywithpin_at_gmail.com > wrote:
> > > > > > > > >
> > > > > > > > > We have a two nodes RAC, the strange thing is that many
> > > > > > > > > sessions run on both nodes.
> > > > > > > > > select sid from gv$session group by sid having
> > > > > > > > > count(*)>1
> > > > > > > > > they are lots of rows return.
> > > > > > > > >
> > > > > > > > > My understanding for load balance is: if sessions on
> > > > > > > > > instance 1 are too many, then new session will be connected to instance 2.
> > > > > > > > > But if at the beginning there were100 sessions on both
> > > > > > > > > inst1 and inst2, after all the sessions on inst2 are completed, no existing
> > > > > > > > > session on instance1 will go to instance2.
> > > > > > > > >
> > > > > > > > > But the fact of the above query told me some sessions run
> > > > > > > > > on both instance. How can this be true?
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> >
> > --
> > Andrew W. Kerber
> >
> > 'If at first you dont succeed, dont take up skydiving.'
>
>
>

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