[20190423]简单测试user和SYS_CONTEXT ('USERENV','CURRENT_USER').txt

[20190423]简单测试user和SYS_CONTEXT ('USERENV','CURRENT_USER').txt


1.环境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


2.建立测试脚本:

--//create table job_times (sid number, time_ela number,method varchar2(20));


$ cat u1.txt

set verify off

host sleep $(echo &&3/50 | bc -l )

variable vmethod varchar2(20);

exec :vmethod := '&&2';

insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;

commit ;

declare

v_id number;

v_d date;

v varchar2(30);

begin

    for i in 1 .. &&1 loop

  v := USER;

  --//v := sys_context('USERENV', 'CURRENT_USER');

  --//SELECT USER INTO v FROM dual;

    end loop;

end ;

/

update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;

commit;

quit


--//注:最好不要写成  SELECT USER INTO v FROM dual;之类的语句!!特别是使用sys_context方式,我没有测试.


3.测试:


$ sqlplus -s -l scott/book @u1.txt 1e6 user 0 >/dev/null

$ seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @u1.txt 1e6 user50 {} >/dev/null


--//修改脚本,重复测试:


$ sqlplus -s -l scott/book @u1.txt 1e6  sys_context 0 >/dev/null

$ seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @u1.txt 1e6 sys_context50 {} >/dev/null


--//修改脚本,重复测试,使用SELECT USER INTO v FROM dual;

$ sqlplus -s -l scott/book @u1.txt 1e6 suser 0 >/dev/null


4.结果:

SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times 

where method in ('user','user50','sys_context','sys_context50','suser','suser50') group by method order by 3 ;

METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)

-------------------- ---------- ---------------------- -------------

sys_context                   1                    191           191

sys_context50                50                    660         32977

suser                         1                   1610          1610

user                          1                   1755          1755

suser50                      50                   6296        314790

user50                       50                   6701        335063

6 rows selected.

--//可以发现通过sys_context取user最快,实际上具体应用很少人使用sys_context取user.

--//问题在与user函数,实际上调用SELECT USER FROM SYS.DUAL,如果查询共享池就可以发现问题.


SCOTT@book> select * from (select sql_id,sql_text,executions from v$sqlarea order by 3 desc) where rownum<=3;

SQL_ID        SQL_TEXT                                                     EXECUTIONS

------------- ------------------------------------------------------------ ----------

1v717nvrhgbn9 SELECT USER FROM SYS.DUAL                                      50198062

d6r1mk4p3j2uf SELECT USER FROM DUAL                                          34955112

96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,      51568

               timestamp#, sample_size, minimum, maximum, distcnt, lowval,

               hival, density, col#, spare1, spare2, avgcln from hist_head

              $ where obj#=:1 and intcol#=:2

--//执行次数异常之高.实际上使用user函数,在SYS.STANDARD 包中


SCOTT@book> @ desc_proc sys STANDARD user

INPUT OWNER PACKAGE_NAME OBJECT_NAME

sample : @desc_proc sys dbms_stats gather_%_stats


OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DEFAULTED

---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- ----------

SYS        STANDARD             USER                                    1                      VARCHAR2             OUT       N



SCOTT@book> alter system flush shared_pool ;

System altered.


SCOTT@book> alter system flush shared_pool ;

System altered.


$ sqlplus -s -l scott/book @u1.txt 1e5 Suser 0 >/dev/null


SCOTT@book> select * from (select sql_id,sql_text,executions from v$sqlarea order by 3 desc) where rownum<=2;

SQL_ID        SQL_TEXT                                                     EXECUTIONS

------------- ------------------------------------------------------------ ----------

d6r1mk4p3j2uf SELECT USER FROM DUAL                                            100000

96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,      52549

               timestamp#, sample_size, minimum, maximum, distcnt, lowval,

               hival, density, col#, spare1, spare2, avgcln from hist_head

              $ where obj#=:1 and intcol#=:2


请使用浏览器的分享功能分享到微信等