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

--//重复测试:
1.环境:
Elapsed: 00:00:02.79
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.建立测试脚本:
$ cat select.sql1
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@book01p> @ select.sql1
PL/SQL procedure successfully completed.
Elapsed: 00:00:13.05

SCOTT@book01p> @ assign.sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.79

--//通过这样方法简单sql语句执行,性能提高 13.05/2.79 = 4.68倍.


SYS@book> @ashtop sql_id 1=1  trunc(sysdate)+17/24+32/1440+44/86400  trunc(sysdate)+17/24+33/1440+23/86400

    Total                                                                         Distinct Distinct    Distinct
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
       13      .3   57% |               2024-09-05 17:32:46 2024-09-05 17:33:20          1       11          11
        9      .2   39% | 1w3bnfhyp4u5h 2024-09-05 17:32:49 2024-09-05 17:33:00          9        9           9
        1      .0    4% | f1hbvj9nmqvy3 2024-09-05 17:32:48 2024-09-05 17:32:48          1        1           1

SYS@book> @ sql_id 1w3bnfhyp4u5h
--SQL_ID = 1w3bnfhyp4u5h

SELECT NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1'), SYS_CONTEXT ('USERENV','SID') FROM DUAL;

SYS@book> @ sql_id f1hbvj9nmqvy3

--SQL_ID = f1hbvj9nmqvy3

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;;


SYS@book> @ashtop sql_id 1=1   trunc(sysdate)+17/24+34/1440+18/86400 trunc(sysdate)+17/24+34/1440+26/86400

    Total                                                                         Distinct Distinct    Distinct
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
        3      .4  100% | 285ch5t1rgja4 2024-09-05 17:34:22 2024-09-05 17:34:24          1        3           1

SYS@book> @ sql_id 285ch5t1rgja4

--SQL_ID = 285ch5t1rgja4

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;;