Re: Seeing the same session_id used on different nodes alot in RAC 11g
- From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- To: rodrigo@xxxxxxxxxxxxxxx
- Date: Fri, 27 Feb 2015 10:17:03 -0500
This is a guess, but I think the session_id is just an offset into a
memory array of session info. I think that when you startup the
instance, it allocates a chunk of memory for sessions ... probably a
fixed length-array based on the SESSIONS init parameter ... and the
SID is just identifying which array entry has your session info. If
I'm guessing correctly then this would explain why SIDs are so
frequently re-used (even without RAC), and why the serial# is so
important for uniquely identifying a session. On a RAC database with
even a moderate level of activity, I'd expect it to be very common to
see the same SID in use simultaneously on multiple nodes. Also there
would be an X$ table that directly exposes the memory array...
referenced by the v$session source... it's a bit lazy of me not to
just search for this myself right now. :)
How does Oracle choose which entry in the SESSION table for a new
connections? Obviously it needs an entry that's not currently being
used; I don't know beyond that. (Maybe just an LRU algorithm? I'm
pretty sure it's not starting at the beginning and taking the first
available slot.)
If anyone actually knows something about this - more than just pure
speculation - I'd be very interested to hear it! Especially if you've
got references to back it up. I'd imagine this is probably covered in
some oracle book somewhere that I can't think of right now.
-Jeremy
--
http://about.me/jeremy_schneider
On Wed, Feb 25, 2015 at 2:00 PM, Rodrigo Mufalani
<rodrigo@xxxxxxxxxxxxxxx> wrote:
> You must put inst_id on your query to identify uniquely the sessions.
> Yes, SID numbers are reutilized frequently and serial# is a kind of counter
> of how many times that Sid are used since instance startup.
>
> [ ]'s
> #mufalani
>
> Desculpe por erros! Este e-mail foi escrito do meu smartphone!
>
> Sorry for typos! This mail was written from my smartphone!!!
>
> On 25/02/2015, at 15:52, Dba DBA <oracledbaquestions@xxxxxxxxx> wrote:
>
> Not causing a problem, just curious. I may have just not noticed before. I
> know that a unique session is SID+serial#.
>
> Oracle 11.2.0.2
> 4 node cluster, but only 2 nodes run this DB (so 2 instances)
> redhat linux 6
>
> Noticing
>
> select sid,username,count(*) from gv$session group by sid,username having
> count(*) > 1
> seeing this for alot of sessions. Not all. both user sessions and background
> process.
>
> This may be common and I never noticed it. Is it common? Also, I have been
> killing alot of blocking lock sessions for developers lately (due to code
> issues) and then I see the same sessions reused when the application
> reconnects.
>
> How does Oracle pick a session_id ? Does anyone know why we have sid+serial#
> and not just 1 field with enough values ? PART of it might be to support
> TAF, but I have not used that feature so not sure if it uses the same SID.
>
>
> We are not using TAF. Per document below I would see the multiple VIPs on
> the same host. I dont see that in crs_stat.
>
> http://gavinsoorma.com/2009/08/11g-rac-transparent-application-failover-taf/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
