ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20210218]Select vs Assign – How To Assign PLSQL Variables.txt

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

原创 Oracle 作者：lfree 时间：2021-02-18 08:58:23 0 删除 编辑

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

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2757411/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20210218]xargs 与here doc测试.txt
下一篇： [20210218]bash echo 建立顺序号.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969582

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593