How to Get Your SID? USERENV vs V$MYSTAT! – Keep Learning Keep Living

Hello everyone,

This is an easy question. How to find your own session SID and when you ask Google for it, you will find many answers. While reading some documents, I realized that some Gurus are using V$MYSTAT to get current SID:

select sid from v$mystat where rownum=1;

1	select sid from v$mystat where rownum=1;

I always prefer USERENV(‘SID’) context to get SID and curious about which one is faster and consume less cpu resources. So I created this sample code:

set serveroutput on;

declare
  x integer;
  s timestamp;
  cpu_stat number;
  procedure sp_get_sid_from_userenv (p1 pls_integer, p in out integer) as
  begin
    select userenv('SID') into p from dual;    
  end;

  procedure sp_get_sid_from_v$mystat (p1 pls_integer, p in out integer) as
  begin
    select SID into p from v$mystat where rownum = 1;    
  end;
  
  function sf_get_cpu_usage return number as
    x number;
  begin
    select b.value into x
    from v$statname a, v$mystat b
    where a.statistic# = b.statistic#
      and name = 'CPU used by this session';
    return x;
  end;
begin
  s := systimestamp;
  cpu_stat := sf_get_cpu_usage ;
  
  for i in 1..100000 loop
    sp_get_sid_from_userenv(i,x);
  end loop;
  
  dbms_output.put_line('userenv time: ' || (systimestamp-s));
  dbms_output.put_line('userenv cpu: ' || (sf_get_cpu_usage - cpu_stat));
  
  /*************************************************************************/
  
  s := systimestamp;
  cpu_stat := sf_get_cpu_usage ;
  
  for i in 1..100000 loop
    sp_get_sid_from_v$mystat(i,x);
  end loop;

  dbms_output.put_line('v$mystat time: ' || (systimestamp-s));
  dbms_output.put_line('v$mystat cpu: ' || (sf_get_cpu_usage - cpu_stat));
end;
/

userenv time: +000000000 00:00:02.680000000
userenv cpu: 269
v$mystat time: +000000000 00:00:03.532000000
v$mystat cpu: 352

set serveroutput on;

declare

x integer;

s timestamp;

cpu_stat number;

procedure sp_get_sid_from_userenv (p1 pls_integer, p in out integer) as

begin

select userenv('SID') into p from dual;

end;

procedure sp_get_sid_from_v$mystat (p1 pls_integer, p in out integer) as

begin

select SID into p from v$mystat where rownum = 1;

end;

function sf_get_cpu_usage return number as

x number;

begin

select b.value into x

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and name = 'CPU used by this session';

return x;

end;

begin

s := systimestamp;

cpu_stat := sf_get_cpu_usage ;

for i in 1..100000 loop

sp_get_sid_from_userenv(i,x);

end loop;

dbms_output.put_line('userenv time: ' || (systimestamp-s));

dbms_output.put_line('userenv cpu: ' || (sf_get_cpu_usage - cpu_stat));

/*************************************************************************/

s := systimestamp;

cpu_stat := sf_get_cpu_usage ;

for i in 1..100000 loop

sp_get_sid_from_v$mystat(i,x);

end loop;

dbms_output.put_line('v$mystat time: ' || (systimestamp-s));

dbms_output.put_line('v$mystat cpu: ' || (sf_get_cpu_usage - cpu_stat));

end;

userenv time: +000000000 00:00:02.680000000

userenv cpu: 269

v$mystat time: +000000000 00:00:03.532000000

v$mystat cpu: 352

as you see, USERENV(‘SID’) is much faster and consume less cpu resource than v$mystat method. so I will keep using it and suggest you do the same.

Comments (6)

Gaz says:

2019-06-20 at 10:42

Hey Mustafa,

A point about “USERENV()” is that it is a legacy functioned maintained only for backward compatibility.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions208.htm
The interesting thing is, the userenv() function is also quicker than the preferred “sys_context(‘userenv’, ‘sid)”.

1. Mustafa says:
  
  2019-06-20 at 23:31
  
  Hi Gaz, it’s nice to see you 🙂 I realized that while I was testing this code but didn’t add my findings to my post and actually sys_context is not slower but giving lowercase parameter values to sys_context cause that slowness 🙂 just make your test with SYS_CONTEXT(‘USERENV’, ‘SID’) almost has the same performance with userenv. so maybe we should have a rule about case sensitivity while passing parameters.
  
  1. Gaz says:
    
    2019-06-21 at 14:24
    
    … userenv() still comes out on top speed and cpu wise.
    Btw really liking the refreshingly dark colour scheme of your site.
    
    Gaz.
    
    1. Mustafa says:
      
      2019-06-22 at 10:42
      
      Thanks mate, all of us will suffer from our eyes so I use dark theme on everything 🙂
      
Amit says:

2021-02-02 at 20:44

The way of subscribing is ugly.

1. Mustafa says:
  
  2021-02-15 at 12:10
  
  Hi Amit,
  
  you might be right. I am literally just an oracle guy and don’t like web stuff 🙂 I ask for a friend to look into it.

Comments (6)

Leave a Reply Cancel reply