[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下密集执行性能差异巨大，限于篇幅另外
--//写一篇文章分析。
