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

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