[20260604]简单测试获取sid的最佳方法.txt

[20260604]简单测试获取sid的最佳方法.txt

--//对比测试sys_context('userenv', 'sid') ，userenv('sid')，以及执行sql语句select sid from v$mystat where rownum=1;。
--//那种方法最佳。
--//另外测试分析为什么pdb与cdb下使用sys用户执行select sid from v$mystat where rownum=1;存在巨大差异。

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.建立测试脚本:
--//SCOTT@book01p> create table job_times (sid number, serial# number,time_ela1 number,time_ela2 number ,start_date timestamp,end_date timestamp,method varchar2(20));
--//Table created.

$ cat z1.txt
set verify off
variable vmethod varchar2(30);
exec :vmethod := '&&2';

insert into job_times values ( sys_context ('userenv', 'sid') ,&3,dbms_utility.get_time ,null , localtimestamp,null ,:v_method) ;
commit ;

declare
v_sid number;
v_d date;
v varchar2(30);
begin
    for i in 1 .. &&1 loop
      &&4 v_sid := sys_context ('userenv', 'sid');
      &&5 v_sid := userenv('sid');
      &&6 select sid into v_sid from v$mystat where rownum=1;
    end loop;
end ;
/

update job_times set time_ela2 = dbms_utility.get_time ,end_date=localtimestamp  where sid=sys_context ('userenv', 'sid') and serial#= &3 and method= :v_method ;
commit;
quit

3.单个会话执行测试:

sqlplus -s -l scott/book@book01p @z1.txt 1e6 sys_context1 1 '' -- --
sqlplus -s -l scott/book@book01p @z1.txt 1e6 userenv1     1 -- '' --
sqlplus -s -l scott/book@book01p @z1.txt 1e6 select1      1 -- -- ''

SCOTT@book01p> select method,count(*),round(avg(TIME_ELA2-TIME_ELA1),0),sum(TIME_ELA2-time_ela1) from scott.job_times group by method order by 3 ;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA2-TIME_ELA1),0) SUM(TIME_ELA2-TIME_ELA1)
-------------------- ---------- --------------------------------- ------------------------
sys_context1                  1                                89                       89
userenv1                      1                               164                      164
select1                       1                              1632                     1632

4.多个会话执行测试：

seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book@book01p @z1.txt 1e6 sys_context50 Q '' -- -- > /dev/null
seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book@book01p @z1.txt 1e6 userenv50     Q -- '' -- > /dev/null
seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book@book01p @z1.txt 1e6 select50      Q -- -- '' > /dev/null

SCOTT@book01p> select method,count(*),round(avg(TIME_ELA2-TIME_ELA1),0),sum(TIME_ELA2-time_ela1) from scott.job_times where method like '%50' group by method order by 3 ;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA2-TIME_ELA1),0) SUM(TIME_ELA2-TIME_ELA1)
-------------------- ---------- --------------------------------- ------------------------
sys_context50                50                              1022                    51110
userenv50                    50                              1917                    95866
select50                     50                             21324                   106622

--//可以看出还是采用sys_context+赋值语句的方法最快。

SCOTT@book01p> select min(start_date),max(end_date) from scott.job_times where method ='select50';
MIN(START_DATE)             MAX(END_DATE)
--------------------------- ---------------------------
2026-06-04 10:21:07.642414  2026-06-04 10:24:48.450645

SYS@book> @ ashtop event 1=1 "timestamp'2026-06-04 10:21:07'" "timestamp'2026-06-04 10:24:49'"
    Total                                                                                                      Distinct Distinct    Distinct
  Seconds     AAS %This   EVENT                                      FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
     6895    31.1   64% |                                            2026-06-04 10:21:07 2026-06-04 10:24:48       3885      221        4103
     3859    17.4   36% | cursor: pin S                              2026-06-04 10:21:10 2026-06-04 10:24:46          1      211         211
        4      .0    0% | log file parallel write                    2026-06-04 10:21:13 2026-06-04 10:24:44          1        4           4
        3      .0    0% | LGWR all worker groups                     2026-06-04 10:22:01 2026-06-04 10:23:28          1        3           3
        1      .0    0% | control file parallel write                2026-06-04 10:24:26 2026-06-04 10:24:26          1        1           1
        1      .0    0% | log file sync                              2026-06-04 10:21:13 2026-06-04 10:21:13          1        1           1
6 rows selected.

SYS@book> @ ashtop sql_id "event='cursor: pin S'" "timestamp'2026-06-04 10:21:07'" "timestamp'2026-06-04 10:24:49'"
    Total                                                                         Distinct Distinct    Distinct
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
     2747    12.4   71% |               2026-06-04 10:21:10 2026-06-04 10:24:46          1      204         204
     1112     5.0   29% | 3cx1jjd8hkhv1 2026-06-04 10:21:10 2026-06-04 10:24:46          1      195         195

SYS@book> @ sql_id 3cx1jjd8hkhv1
-- SQL_ID = 3cx1jjd8hkhv1 come from shared pool
SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;

--//可以看出使用赋值方式要优于执行sql语句,而且sys_context优于userenv。
--//一般情况优先选择赋值方式操作，减少sql语句执行导致的上下文切换.

--//11g 下如果赋值如果采用出现使用如下情况，实际上调用的sql语句。
--// 类似于 v := user 或者 n := seq.nextval ,就是取当前用户名或者seq号的下一个值.
--//21c 仅仅 n := seq.nextval ,还是调用sql语句.
--//有点不好理解的是取sysdate的值11g与21c都不会调用sql语句.

--//另外发现在cdb下密集执行select sid into v_sid from v$mystat where rownum=1;与pdb下密集执行性能差异巨大，限于篇幅另外
--//写一篇文章分析。