[20210218]Select vs Assign – How To Assign PLSQL Variables.txt

[20210218]Select vs Assign – How To Assign PLSQL Variables.txt

--//連結https://blog.pythian.com/select-vs-assign-how-to-assign-pl-sql-variables/，我測試看看：

1.環境：
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試例子：
$ cat get-curr-ospid.sql

-- get-curr-ospid.sql
--
-- get the server OS Pid for the current session
-- Jared Still jkstill@gmail.com still@pythian.com
col username format a20

select
    s.username,
    s.sid,
    p.spid
from v$session s, v$process p
where s.sid = sys_context('userenv','sid')
    and p.addr = s.paddr
order by username, sid
/

$ cat select.sql

@get-curr-ospid

prompt
prompt Testing speed of 'select into var'
prompt
prompt Press ENTER when ready
prompt

accept dummy
prompt Working...
prompt

set timing on

declare
    vDate date;
begin
    for i in 1..1e6
    loop
        select sysdate into vDate from dual;
    end loop;
end;
/

$ cat assign.sql

@get-curr-ospid

prompt
prompt Testing speed of 'var := something'
prompt
prompt Press ENTER when ready
prompt

accept dummy
prompt Working...
prompt

set timing on

declare
    vDate date;
begin
    for i in 1..1e6
    loop
        vDate := sysdate;
    end loop;
end;
/

3.測試:
SCOTT@test01p> @ select.sql
USERNAME                    SID SPID
-------------------- ---------- --------------------
SCOTT                        94 7108

Testing speed of 'select into var'
Press ENTER when ready
Working...
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.34

SCOTT@test01p> @ assign.sql
USERNAME                    SID SPID
-------------------- ---------- --------------------
SCOTT                        94 7108
Elapsed: 00:00:00.12
Testing speed of 'var := something'
Press ENTER when ready
Working...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.50

--//29.34對比0.50,快了29.34/0.50 = 58.68倍.

4.之所以做這個測試,主要我給我們系統建立觸發器,有執行如下:
--//還想起來一次開發寫的一個觸發器要團隊建立，也是採用select的方式賦值,程式碼如下:
SELECT  SYS_CONTEXT('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'IP_ADDRESS')
  INTO I_CLIENT_NAME, I_CLIENT_IP
  FROM dual;

--//實際上到了自己也可能會犯類似的錯誤。我寫的觸發器中的部分程式碼。
...
SELECT NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1')
  INTO v_client_info
  FROM DUAL;

-- add and set clint_ip to application_info
DBMS_APPLICATION_INFO.set_client_info (v_client_info);

--增加的程式碼
DBMS_SESSION.set_identifier (v_client_info);
-- Get user SID information
SELECT SID
  INTO v_sid
  FROM v$mystat
 WHERE ROWNUM = 1;

-- Get Program executable,OSUSER Details,Machine Details for this session
SELECT LOWER (program)
      ,osuser
      ,machine
      ,module
  INTO v_exe
      ,v_osuser
      ,v_machine
      ,v_module
  FROM v$session
 WHERE SID = v_sid;
...

--//測試修改如下:
$ cat select.sql
set timing on
declare
    vDate date;
    vString1 varchar2(32);
    vString2 varchar2(32);
begin
    for i in 1..1e6
    loop
--//select sysdate into vDate from dual;
   SELECT NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1'), SYS_CONTEXT ('USERENV','SID') INTO vString1,vString2 FROM DUAL;
    end loop;
end;
/

$cat assign.sql
set timing on
declare
    vDate date;
    vString1 varchar2(32);
    vString2 varchar2(32);
begin
    for i in 1..1e6
    loop
--//        vDate := sysdate;
    vString1 := NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1');
    vString2 := SYS_CONTEXT ('USERENV','SID');
    end loop;
end;
/

SCOTT@test01p> @ select
PL/SQL procedure successfully completed.
Elapsed: 00:00:36.33

SCOTT@test01p> @ assign
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.85
--//也存在36.33/5.85 = 6.2倍的差異.
--//上班再測試最後的情況:
5.測試:
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

SCOTT@book> @ select.sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:19.23

SCOTT@book> @ assign.sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.40

--//可以發現差異還是很顯著的,而且程式會出現經常呼叫的情況,這樣採用assign的方式更好.

6.測試使用seq的情況:
CREATE SEQUENCE SCOTT.SEQ1
  START WITH 100001
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20000
  NOORDER;

$ cat select.sql
set timing on
declare
    vDate date;
    vString1 varchar2(32);
    vString2 varchar2(32);
    vNum number;
begin
    for i in 1..1e6
    loop
--//select sysdate into vDate from dual;
--//   SELECT NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1'), SYS_CONTEXT ('USERENV','SID') INTO vString1,vString2 FROM DUAL;
select SEQ1.nextval into vNum from dual;
    end loop;
end;
/

$ cat assign.sql
set timing on
declare
    vDate date;
        vString1 varchar2(32);
        vString2 varchar2(32);
        vNum number;
begin
    for i in 1..1e6
    loop
--//        vDate := sysdate;
--//    vString1 := NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1');
--//    vString2 := SYS_CONTEXT ('USERENV','SID');
        vNum := seq1.nextval;
    end loop;
end;
/

SCOTT@book> @ select.sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.83

SCOTT@book> @ assign.sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.74
--//兩者差別不大.

SCOTT@book> select SEQ1.nextval  from dual;
   NEXTVAL
----------
   2100001

--//如果你做10046跟蹤,就發現實際上賦值seq,也是透過select取值的,差別不大就很正常了.
--//跟蹤轉儲如下:
=====================
PARSING IN CURSOR #140700009277320 len=326 dep=0 uid=83 oct=47 lid=83 tim=1613609368891825 hv=941697850 ad='7f503420' sqlid='gkmtftww22atu'
declare
    vDate date;
        vString1 varchar2(32);
        vString2 varchar2(32);
        vNum number;
begin
    for i in 1..4
    loop
--//        vDate := sysdate;
--//    vString1 := NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1');
--//    vString2 := SYS_CONTEXT ('USERENV','SID');
        vNum := seq1.nextval;
    end loop;
end;
END OF STMT
PARSE #140700009277320:c=4999,e=5232,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1613609368891821
=====================
PARSING IN CURSOR #140700009273424 len=29 dep=1 uid=83 oct=3 lid=83 tim=1613609368894009 hv=973102965 ad='7ef52618' sqlid='1x14xzwx00rvp'
Select SEQ1.NEXTVAL from dual
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END OF STMT
PARSE #140700009273424:c=1999,e=1863,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=51561390,tim=1613609368894008
EXEC #140700009273424:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=51561390,tim=1613609368894170
FETCH #140700009273424:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=51561390,tim=1613609368894278
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
STAT #140700009273424 id=1 cnt=1 pid=0 pos=1 obj=88881 op='SEQUENCE  SEQ1 (cr=0 pr=0 pw=0 time=45 us)'
STAT #140700009273424 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=1 us cost=2 size=0 card=1)'
CLOSE #140700009273424:c=0,e=2,dep=1,type=3,tim=1613609368894454
EXEC #140700009273424:c=1000,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=51561390,tim=1613609368894527
FETCH #140700009273424:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=51561390,tim=1613609368894590
CLOSE #140700009273424:c=0,e=2,dep=1,type=3,tim=1613609368894637
EXEC #140700009273424:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=51561390,tim=1613609368894683
FETCH #140700009273424:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=51561390,tim=1613609368894738
CLOSE #140700009273424:c=0,e=2,dep=1,type=3,tim=1613609368894781
EXEC #140700009273424:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=51561390,tim=1613609368894824
FETCH #140700009273424:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=51561390,tim=1613609368894897
CLOSE #140700009273424:c=0,e=2,dep=1,type=3,tim=1613609368894952
EXEC #140700009277320:c=2999,e=2935,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1613609368894980
WAIT #140700009277320: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1613609368895033
*** 2021-02-18 08:49:32.701
WAIT #140700009277320: nam='SQL*Net message from client' ela= 3806721 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1613609372701786
CLOSE #140700009277320:c=0,e=29,dep=0,type=0,tim=1613609372701946
=====================
PARSING IN CURSOR #140700009277320 len=55 dep=0 uid=83 oct=42 lid=83 tim=1613609372702178 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'

--//如果是 vDate := sysdate;,跟蹤看到的情況如下:
=====================
PARSING IN CURSOR #140540245698440 len=326 dep=0 uid=83 oct=47 lid=83 tim=1613609579972410 hv=2866309712 ad='7c0590a0' sqlid='0x1cu4updhukh'
declare
    vDate date;
    vString1 varchar2(32);
    vString2 varchar2(32);
    vNum number;
begin
    for i in 1..4
    loop
        vDate := sysdate;
--//    vString1 := NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1');
--//    vString2 := SYS_CONTEXT ('USERENV','SID');
--//        vNum := seq1.nextval;
    end loop;
end;
END OF STMT
PARSE #140540245698440:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1613609579972406
EXEC #140540245698440:c=0,e=178,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1613609579972827
WAIT #140540245698440: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1613609579972930

*** 2021-02-18 08:53:03.555
WAIT #140540245698440: nam='SQL*Net message from client' ela= 3582783 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1613609583555747
CLOSE #140540245698440:c=0,e=53,dep=0,type=0,tim=1613609583555900
=====================
PARSING IN CURSOR #140540245698440 len=55 dep=0 uid=83 oct=42 lid=83 tim=1613609583556029 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'

6.總結:
--//除了seq外,採用賦值的方式效率基本比select的方式要好,減少metux,以及fetch的呼叫.
--//應該儘可能的使用assign方式賦值.