Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

How to Get Your SID? USERENV vs V$MYSTAT!

Mustafa, 2019-05-232020-07-05

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:

Oracle PL/SQL
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:

Oracle PL/SQL
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.

11g 12c 18c 19c Administration Development SQL / PLSQL context variablecurrent sidhow to get sidsession idsiduserenvv$mystat

Post navigation

Previous post
Next post

Comments (6)

  1. 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)”.

    Reply
    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.

      Reply
      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.

        Reply
        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 🙂

          Reply
  2. Amit says:
    2021-02-02 at 20:44

    The way of subscribing is ugly.

    Reply
    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.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes