[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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2642206/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2642206/