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:
|
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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.
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)”.
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.
… userenv() still comes out on top speed and cpu wise.
Btw really liking the refreshingly dark colour scheme of your site.
Gaz.
Thanks mate, all of us will suffer from our eyes so I use dark theme on everything š
The way of subscribing is ugly.
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.