Re: Seeing the same session_id used on different nodes alot in RAC 11g

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Fri, 27 Feb 2015 10:17:03 -0500
Message-ID: <CA+fnDAa=RU4OHm7kpW9h9MJ8uZ+LqWLDYK3pUZh_1CPuDa1Kow_at_mail.gmail.com>

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_at_mufalani.com.br> 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_at_gmail.com> 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/

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

Received on Fri Feb 27 2015 - 16:17:03 CET