[20210426]execute immediate.txt

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

[20210426]execute immediate.txt --//以前给别人讲解绑定变量的好处,总是写一个脚本对比使用绑定变量与非绑定变量的好处,基本会使用execute immediate, --//因为需要比较,总不能非绑定变量的使用execute immediate来拚接sql语句,另外一个不使用execute immediate.例子: execute immediate 'select pad from t where id = ' || i into v_pad; execute immediate 'select pad from t where id = :j ' into v_pad using i ; --//这样编写测试至少公正一些. --//今天比较两种方式的区别,实际上测试的目的主要是我发现生产系统的一个函数,里面全部sql语句都是execute immediate. --//不知道开发为什么这么写,从我某种意义讲我觉得编程者可能第一次写PL/SQL脚本或者不经常写.实际上我也属于不会写的那类^_^. --//也测试看看性能是否存在差距. 1.环境: 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> create table job_times (sid number, time_ela number,method varchar2(20)); Table created. SCOTT@book> create table t as select rownum id ,cast('test' as varchar2(10)) name from dual ; Table created. SCOTT@book> alter table t modify ( id not null ); Table altered. --//分析表略。 create unique index i_t_id on t(id); 2.建立测试脚本: $ cat m11.txt set verify off host sleep $(echo &&3/150 | bc -l ) variable vmethod varchar2(20); exec :vmethod := '&&2'; insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ; commit ; declare v_id number; v_d date; begin for i in 1 .. &&1 loop select /*+ index(t) &&3 */ count (name) into v_id from t where id=1; --execute immediate 'select /*+ Index(t) &&3 */ count (name) from t where id=1' into v_id; --execute immediate 'select /*+ INdex(t) &&3 */ count (name) from t where id='||i into v_id; end loop; end ; / update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod; commit; quit 3.单个进程测试: --//分别测试3中情况。 $ sqlplus -s -l scott/book @m11.txt 1e6 p1=1 1 >/dev/null $ sqlplus -s -l scott/book @m11.txt 1e6 p1exe=1 1 >/dev/null $ sqlplus -s -l scott/book @m11.txt 1e5 p1exe_nobind=1 1 >/dev/null --//注:我也顺便测试使用非绑定变量的情况,不过减少了循环测试1e5.不然太浪费时间. --//测试结果如下： SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ; METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) -------------------- ---------- ---------------------- ------------- p1=1 1 2399 2399 p1exe=1 1 3130 3130 p1exe_nobind=1 1 5532 5532 --//非绑定变量不再说明，本来就很慢，可以发现execute immediate的调用比直接select的要很慢。 4.多个进程测试: $ seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m11.txt 2e5 p2=150 {} >/dev/null $ seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m11.txt 2e5 p2exe=150 {} >/dev/null --//测试循环测试设置2e5,不然时间有点长。 SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ; METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) -------------------- ---------- ---------------------- ------------- p1=1 1 2399 2399 p1exe=1 1 3130 3130 p2=150 150 4840 725953 p2exe=150 150 5387 808032 --//不管那种模式，execute immediate都要慢一点点。 5.继续分析： $ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m11.txt 2e5 p3=150 {} >/dev/null;zzdate trunc(sysdate)+10/24+53/1440+15/86400 == 2021/04/26 10:53:15 trunc(sysdate)+10/24+54/1440+05/86400 == 2021/04/26 10:54:05 SCOTT@book> @ ashtop sql_id,event,p1,p2,p3 1=1 trunc(sysdate)+10/24+53/1440+15/86400 trunc(sysdate)+10/24+54/1440+05/86400 Total Seconds AAS %This SQL_ID EVENT P1 P2 P3 FIRST_SEEN LAST_SEEN --------- ------- ------- ------------- ------------- ---------- ---------- ---------- ------------------- ------------------- 888 17.8 12% | 1650815232 1 0 2021-04-26 10:53:15 2021-04-26 10:54:04 39 .8 1% | bxzr72xbttp9p 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 39 .8 1% | g5jk5qhqz3jt6 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:03 38 .8 1% | 5drwax8fqfpyu 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 38 .8 1% | ac9zqscdmf9jb 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 38 .8 1% | b1vwf4qz54n07 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 38 .8 1% | btbnvhwc5wf5v 1650815232 1 0 2021-04-26 10:53:17 2021-04-26 10:54:01 38 .8 1% | c0pa8271ypyk7 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 38 .8 1% | cqd6a708y6sqv 1650815232 1 0 2021-04-26 10:53:17 2021-04-26 10:54:04 38 .8 1% | gm6j6ms4v2y2t 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:03 37 .7 1% | 84ysnr3yh220n 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 37 .7 1% | f4dvfms42fts4 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 36 .7 1% | 1k67m3kp08mwa 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 36 .7 1% | 1typfc22ntsfs 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 36 .7 1% | 1zqghmqsd3dt2 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:03 36 .7 1% | 5534dccb81upv 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 36 .7 1% | 7f3xr6z4x6x9a 1650815232 1 0 2021-04-26 10:53:17 2021-04-26 10:54:03 36 .7 1% | 9n8jt2as50a3n 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:03 36 .7 1% | audf2w5zdrm21 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:02 36 .7 1% | dfwp1nznbwjth 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:03 36 .7 1% | dvrxyf0qgdnta 1650815232 1 0 2021-04-26 10:53:17 2021-04-26 10:54:04 36 .7 1% | fa041482wj3hn 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:00 36 .7 1% | fgap543ywdvs1 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 36 .7 1% | fhb1kwqmas68w 1650815232 1 0 2021-04-26 10:53:15 2021-04-26 10:54:04 35 .7 0% | 0hvydr4qq635t 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 35 .7 0% | 12kfrtvckuwzc 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:03 35 .7 0% | 40nt0nbr0ngw4 1650815232 1 0 2021-04-26 10:53:16 2021-04-26 10:54:04 35 .7 0% | 4wgdy2gkg9bvn 1650815232 1 0 2021-04-26 10:53:15 2021-04-26 10:54:02 35 .7 0% | 63b297t1a7xyz 1650815232 1 0 2021-04-26 10:53:17 2021-04-26 10:54:04 35 .7 0% | 862bh2t0r6rx3 1650815232 1 0 2021-04-26 10:53:17 2021-04-26 10:54:04 30 rows selected. $ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m11.txt 2e5 p3exe=150 {} >/dev/null;zzdate trunc(sysdate)+10/24+56/1440+08/86400 == 2021/04/26 10:56:08 trunc(sysdate)+10/24+57/1440+02/86400 == 2021/04/26 10:57:02 SCOTT@book> @ ashtop sql_id,event,p1,p2,p3 1=1 trunc(sysdate)+10/24+56/1440+08/86400 trunc(sysdate)+10/24+57/1440+02/86400 Total Seconds AAS %This SQL_ID EVENT P1 P2 P3 FIRST_SEEN LAST_SEEN --------- ------- ------- ------------- ------------- ---------- ---------- ---------- ------------------- ------------------- 960 17.8 12% | 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 40 .7 1% | 1u8ay9kvzfgcj 1650815232 1 0 2021-04-26 10:56:11 2021-04-26 10:57:01 40 .7 1% | 4y11rcjuavm0r 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:00 39 .7 1% | 0hraatbh1j21j 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 39 .7 1% | 254r509f0a2w1 1650815232 1 0 2021-04-26 10:56:11 2021-04-26 10:57:01 39 .7 1% | 3ad7rqaftzprw 1650815232 1 0 2021-04-26 10:56:11 2021-04-26 10:57:00 39 .7 1% | 3tmzqan6b7972 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 38 .7 0% | 2frc5m06qgc91 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 38 .7 0% | 2n8yvbqcmcq9r 1650815232 1 0 2021-04-26 10:56:11 2021-04-26 10:57:01 38 .7 0% | 2qpm3g8fnapw3 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:56:58 38 .7 0% | 38a7p5ttb8ysv 1650815232 1 0 2021-04-26 10:56:11 2021-04-26 10:57:00 38 .7 0% | 3j2t4ghjj62bv 1650815232 1 0 2021-04-26 10:56:11 2021-04-26 10:57:01 38 .7 0% | 50mawfgzdcykd 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:00 38 .7 0% | 51n9xh10zsr0z 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 38 .7 0% | 5d98bgycmfgvm 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:00 38 .7 0% | 5wsqzr3d5rr68 1650815232 1 0 2021-04-26 10:56:12 2021-04-26 10:57:00 38 .7 0% | 6954azkwsfgm3 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:56:56 38 .7 0% | 72mjmhhhyark4 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 38 .7 0% | 7qa3pvn803j9v 1650815232 1 0 2021-04-26 10:56:11 2021-04-26 10:57:00 38 .7 0% | bsprts61usm05 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 38 .7 0% | cafptwgpk94xn 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 38 .7 0% | d2b0kfdp4qwhy 1650815232 1 0 2021-04-26 10:56:11 2021-04-26 10:57:00 38 .7 0% | fgffh4fnfg8w1 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 38 .7 0% | fzaxunnkyq30p 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 37 .7 0% | 0sg8q2fgms7dn 1650815232 1 0 2021-04-26 10:56:11 2021-04-26 10:57:01 37 .7 0% | 5768r83v60uvm 1650815232 1 0 2021-04-26 10:56:12 2021-04-26 10:56:58 37 .7 0% | 5by0swaxv6jrx 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 37 .7 0% | 6x232fpy1ppcq 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:56:59 37 .7 0% | 77vcnx24zs61w 1650815232 1 0 2021-04-26 10:56:10 2021-04-26 10:57:01 37 .7 0% | ab5bms77mxkpn 1650815232 1 0 2021-04-26 10:56:12 2021-04-26 10:56:59 30 rows selected. SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ; METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) -------------------- ---------- ---------------------- ------------- p1=1 1 2399 2399 p1exe=1 1 3130 3130 p3=150 150 4831 724649 p2=150 150 4840 725953 p3exe=150 150 5150 772438 p2exe=150 150 5387 808032 6 rows selected. --//在CPU的使用上增加一点点时间。很奇怪event显示是null.P1表示什么。 6.总结： --//应该该尽量避免使用execute immediate执行sql语句,execute immediate主要用于动态执行的sql语句。