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

来源：这里教程网时间：2026-03-03 16:27:22 作者：

[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方式赋值.