[20260605]补充分析赋值语句取当前用户与当前日期的测试.txt

--//补充分析赋值语句取当前用户与当前日期的测试,主要是发现11g与21c两者有点不同.

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 z2.txt
set verify off
variable v_method varchar2(30);
exec :v_method := '&&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_user varchar2(30);
v_date date;
begin
    for i in 1 .. &&1 loop
      --//v_sid := sys_context ('userenv', 'sid');
      --//v_sid := userenv('sid');
      --//select sid into v_sid from v$mystat where rownum=1;
      v_user := user;
      --//v_user := sys_context ('userenv', 'session_user');
      --//v_date := sysdate;
    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.测试：
$ zzdate ; sqlplus -s -l scott/book@book01p @z2.txt 1e6 user_test2 1 > /dev/null;zzdate
trunc(sysdate)+16/24+56/1440+16/86400 1780649776.506051693
trunc(sysdate)+16/24+56/1440+18/86400 1780649778.395385222

--//修改注解，继续：
$ zzdate ; sqlplus -s -l scott/book@book01p @z2.txt 1e6 sys_context_test2 1 > /dev/null;zzdate
trunc(sysdate)+16/24+56/1440+58/86400 1780649818.171173395
trunc(sysdate)+16/24+56/1440+59/86400 1780649819.942395625

--//修改注解，继续：

$ zzdate ; sqlplus -s -l scott/book@book01p @z2.txt 1e6 sysdate_test2 1 > /dev/null;zzdate
trunc(sysdate)+16/24+57/1440+48/86400 1780649868.648250209
trunc(sysdate)+16/24+57/1440+49/86400 1780649869.608354877

SCOTT@book01p> select method,count(*),round(avg(TIME_ELA2-TIME_ELA1),0),sum(TIME_ELA2-time_ela1) from scott.job_times where method like '%test2' group by method order by 3;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA2-TIME_ELA1),0) SUM(TIME_ELA2-TIME_ELA1)
-------------------- ---------- --------------------------------- ------------------------
sysdate_test2                 1                                66                       66
sys_context_test2             1                               147                      147
user_test2                    1                               160                      160

--//v_user := user; 确实比以前快了许多。
--//以前11g ，调用SELECT USER FROM SYS.DUAL。

--//在11g环境重复测试看看。

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

$ zzdate ; sqlplus -s -l scott/book@book01p @z2.txt 1e6 user_test2 1 > /dev/null;zzdate
trunc(sysdate)+17/24+07/1440+11/86400 1780650431.104789127
trunc(sysdate)+17/24+07/1440+22/86400 1780650442.733438406

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
------------- ------------------------------------------------------------ ----------
1v717nvrhgbn9 SELECT USER FROM SYS.DUAL                                       1000000
96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,       1361
               timestamp#, sample_size, minimum, maximum, distcnt, lowval,
               hival, density, col#, spare1, spare2, avgcln from hist_head
              $ where obj#=:1 and intcol#=:2

--//可以看出11g下真实执行的是SELECT USER FROM SYS.DUAL。

$ zzdate ; sqlplus -s -l scott/book@book01p @z2.txt 1e6 sys_context_test2 1 > /dev/null;zzdate
trunc(sysdate)+17/24+08/1440+10/86400 1780650490.568670584
trunc(sysdate)+17/24+08/1440+12/86400 1780650492.173115440

$ zzdate ; sqlplus -s -l scott/book@book01p @z2.txt 1e6 sysdate_test2 1 > /dev/null;zzdate
trunc(sysdate)+17/24+09/1440+43/86400 1780650583.759458521
trunc(sysdate)+17/24+09/1440+44/86400 1780650584.588495531

SCOTT@book>  select method,count(*),round(avg(TIME_ELA2-TIME_ELA1),0),sum(TIME_ELA2-time_ela1) from scott.job_times where method like '%test%' group by method order by 3;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA2-TIME_ELA1),0) SUM(TIME_ELA2-TIME_ELA1)
-------------------- ---------- --------------------------------- ------------------------
sysdate_test2                 1                                60                       60
sys_context_test2             1                               134                      134
user_test2                    1                              1134                     1134
--//可以看出v_user := user;的方式最慢.

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> @ desc_proc sys STANDARD sysdate
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                       SYSDATE                                 1                                DATE                           OUT       N

--//pl/sql的优化有时候很奇怪，同样是sys.STANDARD包的函数。sysdate就可以直接赋值，而user就通过SELECT USER FROM SYS.DUAL取值。