Skip to Main Content
  • Questions

  • sys_context returning session id in RAC

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 10, 2017 - 5:36 am UTC

Last updated: March 14, 2017 - 3:58 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,
In a RAC oracle environment where lets say multiple instance with same session id's are running, then what is the best way of getting the session ID from gv$session without affecting performance? what would sys_context('userenv', 'sessionid') returns?

and Connor said...

Doing an execution plan suggests SID might be better than AUDSID (which is what sessionid is)

SQL> select * from gv$session where sid = 123;

Execution Plan
----------------------------------------------------------
Plan hash value: 1627146547

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |   372 |     0   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN     |                 |     1 |   372 |     0   (0)| 00:00:01 |
|   2 |   NESTED LOOPS            |                 |     1 |   120 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |     1 |    55 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    65 |     0   (0)| 00:00:01 |
|   5 |   BUFFER SORT             |                 |     1 |   252 |     0   (0)| 00:00:01 |
|*  6 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |     1 |   252 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("W"."KSLWTSID"=123)
   4 - filter("W"."KSLWTEVT"="E"."INDX")
   6 - filter("S"."INDX"=123 AND BITAND("S"."KSSPAFLG",1)<>0 AND
              BITAND("S"."KSUSEFLG",1)<>0)

SQL> select * from gv$session where audsid = 123123;

Execution Plan
----------------------------------------------------------
Plan hash value: 3425234845

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |   372 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS             |                 |     1 |   372 |     0   (0)| 00:00:01 |
|   2 |   NESTED LOOPS            |                 |     1 |   307 |     0   (0)| 00:00:01 |
|   3 |    FIXED TABLE FULL       | X$KSLWT         |    31 |  1705 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |     1 |   252 |     0   (0)| 00:00:01 |
|*  5 |   FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |     1 |    65 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("S"."INDX"="W"."KSLWTSID" AND "S"."KSUUDSES"=123123 AND
              BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
   5 - filter("W"."KSLWTEVT"="E"."INDX")


so perhaps the combination of SID, INST_ID but you'd probably bring SESSIONID along as well as a surrogate for "SERIAL#"

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

not what am looking for

prasannakrishna, March 10, 2017 - 9:21 am UTC

I want sessionID for some other purpose and SID will not serve that purpose.
What would sys_context('userenv','sessionid') return if multiple instances are running in RAC?
Connor McDonald
March 11, 2017 - 1:53 am UTC

No it will not.

But I'm doing to know what "other purpose" is :-)


Prasannakrishna, March 13, 2017 - 5:31 am UTC

ONE of the purpose is to distinguish oracle background process form user process.
So there is no way to get sessionID through sys_context in RAC environment other than querying gv$session table.
Connor McDonald
March 14, 2017 - 3:58 am UTC

If you're querying the information about a session *other* than the one you are in, then you'll always be querying v$session / gv$session.

If you need information about the session you are *in*, then by definition you are in a foreground session. The only exception to that would be perhaps if you define a scheduler job as a background job, in which case, the sys_context function can also give you information about jobs (see BG_JOB_ID/FG_JOB_ID)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.