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

From: William Robertson <>
Date: Sat, 28 Feb 2015 10:55:17 +0000
Message-ID: <-881148505023529998_at_unknownmsgid>

Interesting discussion.

Does this shed any light on why Oracle can't give us the session serial# via sys_context, or is it just one of those things they haven't got around to yet? (Though now I google it, I see we can get it from dbms_debug_jdwp.current_session_serial, which might simplify my next logging procedure.)

William Robertson

On 28 Feb 2015, at 08:52, pavan kumar <> wrote:

Hi Tanel,

Impressed, It's one of the good enlightening concept/workflow you have shared across.
One dumb question, from my end as you have stated

"the entire array must fit into the linear address range of a single granule."...

Does it mean we are going to having fixed number of sessions(only) allocated (as it's single granule, does it mean, out of allocated granules (whole sga), only one single dedicate granule is going to take care/utilized for sessions - allocation/de-allocations),

  • Thanks Pavan Kumar N

 *From:* Tanel Poder <> *To:*
*Cc:* Stefan Koehler <>; oracle-l-freelists Freelists <>
*Sent:* Saturday, 28 February 2015 12:43 AM *Subject:* Re: Seeing the same session_id used on different nodes alot in RAC 11g

Yep the SID is the *slot* number in v$session's underlying ksuse array just like PID is the *slot* number of v$process underlying ksupr array. These two arrays are not segmented as far as I know (otherwise they'd show up in x$ksmdd). This is the reason why you may need to increase _ksmg_granule_size to avoid ORA-64 when configuring tens of thousands of sessions - the entire array must fit into the linear address range of a single granule.

Oracle (and any regular C program) would address the array elements this way: array_start_addr + (SID * slot size) + element_offset_in_slot. With segmented arrays it would be less trivial as you'll need to determine in which segment the slot would reside and then look up that segment's starting address.

An example of ORA-64 from past:,3

Tanel Poder

On Fri, Feb 27, 2015 at 11:46 AM, <> wrote:

Thanks Stefan, it’s a shame I forgot about this (time for yet another read
of the book!) :-)

> Am 27.02.2015 um 18:43 schrieb Stefan Koehler <>:
> Hi Sigrid,
> Jonathan Lewis described the difference and its functionality very well
in his book "Oracle Core: Essential Internals for DBAs and Developers". I am
> just quoting from page 59.
> >
> "An array is essentially a list of objects of the same shape and size,
and since all the objects are the same size, it’s easy to walk through the
> array looking at each object in turn. For example, x$ksuse (the structure
for user sessions reported through v$session) is a fixed array with rows of
> 11,360 bytes in Oracle on 32-bit Windows. Oracle need only
record the starting position of the array, and the code can work out the starting
> position of any row in the array by simple arithmetic.
> In some cases the array is a “segmented” array, which means Oracle
allocates an initial chunk of memory for a fixed number of array items, but may
> allocate more chunks dynamically as needed. In this case Oracle needs to
record the addresses of the starting point of each chunk—which might mean
> that it keeps a little list of the chunks, or that each chunk includes a
little information about the next chunk in the list. The structure x$ktatl
> (the entry labeled temporary_table_locks in v$resource_limit) is such a
case. In a little test I ran against a 10g instance, this started life as an
> array with 16 entries of 144 bytes each, but then added chunks of 16 rows
at a time, with each chunk allocated fairly randomly in memory. In a similar
> fashion, although x$ksqrs and x$ksqeq start with a much larger array size
they can grow by chunks of 32 rows at a time." > >
> So segmented means that it may allocates chunks dynamically and it needs
to track the pointer / address to the next randomly distributed memory chunk
> (not only between array entries).
> Best Regards
> Stefan Koehler
> Freelance Oracle performance consultant and researcher
> Homepage:
> Twitter: _at_OracleSK
> >> "" <> hat am 27. Februar 2015 um 18:24 geschrieben: >> >> Wanted to know for a long time, this is the opportunity to ask, - what exactly do you mean by that (segmented array signifying „list is in-built“)? >> >> Does it mean the structs (the elements of the array) have pointers to the previous & next element? (If yes, I wouldn’t have guessed that from the >> expression „segmented array“) >> >> thanks! >> Sigrid -- --
Received on Sat Feb 28 2015 - 11:55:17 CET

Original text of this message