[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