[20210426]execute immediate.txt
[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語句。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2770015/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- app:processReleaseResources, Failed to execute aaptAPPAIAPT
- i_init_func_execute_data
- Conversion to Dalvik format failed: Unable to execute dexORMAI
- Apache httpclient的execute方法除錯ApacheHTTPclient除錯
- 載入驅動三種execute
- [20180321]toad下execute as script的fetch
- EXECUTE IMMEDIATE 儲存過程中 許可權不足及EXECUTE IMMEDIATE的除錯避坑儲存過程除錯
- Failed to execute aapt的奇怪解決方法AIAPT
- Java執行緒池中的execute和submitJava執行緒MIT
- General options: wwwxbs666999com 17008768000 execute commands
- 執行緒池 execute() 的工作邏輯執行緒
- [20200309]rlwrap: error: Cannot execute sqlplus: Too many levels of symbolic linErrorSQLSymbol
- azkaban叢集模式安裝與execute-as-user配置模式
- MySQL AttributeError: ‘Engine’物件沒有’execute’屬性的錯誤MySqlError物件
- Could not execute Write_rows event on table time_task.tt_log
- MySQL: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1MySql
- NetworkError: Failed to execute 'send' on 'XMLHttpRequest': Failed to load xxxx錯誤解決方法ErrorAIXMLHTTP
- Failed to execute user defined function(anonfun$concatStr$1: (map＜string,string＞, string) =＞ string)AIFunction
- Unable to execute SonarScanner analysis: Fail to get bootstrap index from server: Host is unreachable(Host unreachable)AIbootIndexServer
- Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.1:compileAIGoApacheMavenPluginCompile
- MongoDB 檢視DDL檢視not authorized on xxx to execute command { find: system.viewsMongoDBZedView
- MySQL案例01：Last_SQL_Errno: 1755 Cannot execute the current event group in the parallel modeMySqlASTParallel
- Failed to execute goal org.codehaus.mojo:exec-maven-plugin:1.6.0:exec (default-cli)AIGoMavenPlugin
- ] Failed to execute goal org.mybatis.generator:mybatis-generator-maven-plugin Cannot instantiate object of typeAIGoMyBatisMavenPluginObject
- 11G R2中的並行執行，dbms_parallel_execute並行Parallel
- 血的教訓--如何正確使用執行緒池submit和execute方法執行緒MIT
- hibernate配置級聯刪除時報錯:could not execute statement； SQL [n/a]； constraint [null]SQLAINull
- TiDB 原始碼閱讀系列文章（二十三）Prepare/Execute 請求處理TiDB原始碼
- mongodb錯誤解決辦法-bash: ./mongod: cannot execute binary file: Exec format errorMongoDBORMError
- linux系統中安裝jdk出現bash: ./java: cannot execute binary file 問題LinuxJDKJava
- 【k8s】etcd叢集took too long to execute慢日誌告警問題分析K8S
- Oracle 12.2 報錯：ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_7458″OracleError
- 解決Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.1:compile(default-compile)AIGoApacheMavenPluginCompile
- PostgreSQL 原始碼解讀（135）- MVCC#19（vacuum過程-heap_execute_freeze_tuple函式）SQL原始碼MVCC#函式
- SpringCloud問題解決：spring-cloud-eureka啟動出錯Cannot execute request on any known serverSpringGCCloudServer
- Flowable 6.6.0 BPMN使用者指南 - 17 高階用例 - 17.5 執行自定義SQL（Execute custom SQL）SQL
- bat 指令碼在 cmd 中執行是好的 ，但是在 jenkins 的 Execute Windows batch command 中執行控制檯輸出就一直在 loadingBAT指令碼JenkinsWindows