[20210120]in list与绑定变量个数.txt

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

[20210120]in list与绑定变量个数.txt --//以前写的链接http://blog.itpub.net/267265/viewspace-2215394/ --//里面提到如果绑定变量个数太多,会导致执行时替换绑定变量时间太长,导致sql语句执行缓慢. --//在dbsnake 《基于Oracle的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 create table t as select * from all_objects; create unique index i_t_object_id on t(object_id); --//分析表略. 2.建立测试脚本: $ cat a.txt set pagesize 0 set head off set feedback off set verify off set timing off spool b.txt select 'set termout off' from dual; select 'variable b'||to_char(level)||' number;' from dual connect by level<=&1; select 'begin' from dual; select ':b'||to_char(level)||' :='|| to_char(level)||';' from dual connect by level<=&1; select 'end;' from dual; select '/' from dual ; select 'set termout on' txt from dual; select 'set timing on' txt from dual; select 'select count(data_object_id) from t where (1,object_id) in (' txt from dual ; select '(1,:b'||to_char(level)||'),' txt from dual connect by level<=&&1 -1 ; select '(1,:b'||to_char(&&1)||'));' txt from dual ; select 'set timing off' txt from dual; spool off set pagesize 9999 set head on set feedback on 3.测试: @ a.txt 1000 --//这样生成b.txt脚本.然后执行b.txt脚本: Elapsed: 00:00:00.17 Elapsed: 00:00:00.04 Elapsed: 00:00:00.04 --//我测试并没有作者那样慢.第一次硬解析时间有点长是正常的. SCOTT@book> alter session set statistics_level = all; Session altered. Elapsed: 00:00:00.14 Elapsed: 00:00:00.07 Elapsed: 00:00:00.06 --//也没有很大的差异.即使我取消索引. SCOTT@book> alter index i_t_object_id invisible; Index altered. Elapsed: 00:00:00.13 Elapsed: 00:00:00.08 Elapsed: 00:00:00.08 4.继续测试: --//换一个版本测试. SCOTT@192.168.100.33:1521/test> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi create table t as select * from all_objects; create unique index i_t_object_id on t(object_id); --//分析表略. Elapsed: 00:00:00.32 Elapsed: 00:00:00.07 Elapsed: 00:00:00.08 SCOTT@192.168.100.33:1521/test> alter session set statistics_level = all; Session altered. Elapsed: 00:00:00.36 Elapsed: 00:00:00.08 Elapsed: 00:00:00.08 --//总之,我没有遇到作者的情况,只有在绑定变量很大的情况下才会更加严重. 5.换到11g的环境,继续测试绑定变量更多的情况: SCOTT@book> alter index i_t_object_id visible; Index altered. 绑定变量数量执行时间 -------------------------- 1000 00:00:00.04 2000 00:00:00.12 3000 00:00:00.30 4000 00:00:00.52 5000 00:00:00.92 6000 00:00:01.19 10000 00:00:03.41 (第1次执行00:00:06.26) --//实际上3000个变量,甚至4000个以内并没有这么严重. --//在10000个绑定变量的情况下做一个snapper测试: SYS@book> @ tpt/snapper all 10 1 114 Sampling SID 114 with interval 10 seconds, taking 1 snapshots... -- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 114, SCOTT , STAT, Requests to/from client , 1, .1, , , , , 1 per execution 114, SCOTT , STAT, opened cursors cumulative , 1, .1, , , , , 1 per execution 114, SCOTT , STAT, user calls , 2, .2, , , , , 2 per execution 114, SCOTT , STAT, DB time , 1, .1, , , , , 1 per execution 114, SCOTT , STAT, non-idle wait count , 80, 8.14, , , , , 80 per execution 114, SCOTT , STAT, session uga memory , -340288, -34.63k, , , , , -340.29k per execution 114, SCOTT , STAT, session cursor cache hits , 1, .1, , , , , 0 softparses avoided thanks to cursor cache 114, SCOTT , STAT, parse count (total) , 1, .1, , , , , ~ softparses per hardparse 114, SCOTT , STAT, execute count , 1, .1, , , , , 1 executions per parse 114, SCOTT , STAT, bytes sent via SQL*Net to client , 70023, 7.13k, , , , , 70.02k bytes per roundtrip 114, SCOTT , STAT, bytes received via SQL*Net from client , 578651, 58.89k, , , , , 578.65k bytes per roundtrip 114, SCOTT , STAT, SQL*Net roundtrips to/from client , 1, .1, , , , , 1 per execution 114, SCOTT , TIME, parse time elapsed , 506, 51.5us, .0%, [ ], , , 114, SCOTT , TIME, PL/SQL execution elapsed time , 1402, 142.69us, .0%, [ ], , , 114, SCOTT , TIME, DB CPU , 7000, 712.44us, .1%, [ ], , , 114, SCOTT , TIME, sql execute elapsed time , 3853, 392.15us, .0%, [ ], , , 114, SCOTT , TIME, DB time , 6810, 693.1us, .1%, [ ], , , 1.72 % unaccounted time 114, SCOTT , WAIT, SQL*Net message to client , 2, .2us, .0%, [ ], 1, .1, 2us average wait 114, SCOTT , WAIT, SQL*Net more data to client , 35, 3.56us, .0%, [ ], 8, .81, 4.38us average wait 114, SCOTT , WAIT, SQL*Net message from client , 9650049, 982.15ms, 98.2%, [WWWWWWWWWW], 1, .1, 9.65s average wait 114, SCOTT , WAIT, SQL*Net more data from client , 686, 69.82us, .0%, [ ], 71, 7.23, 9.66us average wait -- End of Stats snap 1, end=2021-01-20 10:03:33, seconds=9.8 <No active sessions captured during the sampling period> -- End of ASH snap 1, end=2021-01-20 10:03:33, seconds=10, samples_taken=96 PL/SQL procedure successfully completed. --//实际上赋值的时间占用还是很长的.在我脚本加入显示日期的内容,执行情况如下: SCOTT@book> @ b.txt SYSDATE ------------------- 2021-01-20 10:10:59 1 row selected. SYSDATE ------------------- 2021-01-20 10:11:05 1 row selected. Elapsed: 00:00:00.00 COUNT(DATA_OBJECT_ID) --------------------- 2135 1 row selected. Elapsed: 00:00:03.44 SYSDATE ------------------- 2021-01-20 10:11:09 1 row selected. Elapsed: 00:00:00.00 --//可以发现定义变量以及赋值使用6秒.而执行时间并不是很长. --//取消赋值部分,snapper看到的情况是: SYS@book> @ tpt/snapper all 5 1 114 Sampling SID 114 with interval 5 seconds, taking 1 snapshots... -- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 114, SCOTT , STAT, Requests to/from client , 6, 1.23, , , , , 2 per execution 114, SCOTT , STAT, opened cursors cumulative , 3, .62, , , , , 1 per execution 114, SCOTT , STAT, user calls , 9, 1.85, , , , , 3 per execution 114, SCOTT , STAT, session logical reads , 266, 54.57, , , , , 19.25k total buffer visits 114, SCOTT , STAT, CPU used when call started , 2, .41, , , , , .67 per execution 114, SCOTT , STAT, CPU used by this session , 2, .41, , , , , .67 per execution 114, SCOTT , STAT, DB time , 2, .41, , , , , .67 per execution 114, SCOTT , STAT, non-idle wait count , 75, 15.39, , , , , 25 per execution 114, SCOTT , STAT, consistent gets , 266, 54.57, , , , , 88.67 per execution 114, SCOTT , STAT, consistent gets from cache , 266, 54.57, , , , , 88.67 per execution 114, SCOTT , STAT, consistent gets from cache (fastpath) , 151, 30.98, , , , , 50.33 per execution 114, SCOTT , STAT, consistent gets - examination , 115, 23.59, , , , , 38.33 per execution 114, SCOTT , STAT, logical read bytes from cache , 2179072, 447.06k, , , , , 726.36k per execution 114, SCOTT , STAT, calls to get snapshot scn: kcmgss , 3, .62, , , , , 1 per execution 114, SCOTT , STAT, no work - consistent read gets , 151, 30.98, , , , , 50.33 per execution 114, SCOTT , STAT, table fetch by rowid , 9511, 1.95k, , , , , 3.17k per execution 114, SCOTT , STAT, index fetch by key , 10000, 2.05k, , , , , 3.33k per execution 114, SCOTT , STAT, session cursor cache hits , 3, .62, , , , , 0 softparses avoided thanks to cursor cache 114, SCOTT , STAT, buffer is pinned count , 18983, 3.89k, , , , , 98.62 % buffer gets avoided thanks to buffer pin caching 114, SCOTT , STAT, buffer is not pinned count , 134, 27.49, , , , , 44.67 per execution 114, SCOTT , STAT, parse count (total) , 3, .62, , , , , ~ softparses per hardparse 114, SCOTT , STAT, execute count , 3, .62, , , , , 1 executions per parse 114, SCOTT , STAT, bytes sent via SQL*Net to client , 1600, 328.26, , , , , 266.67 bytes per roundtrip 114, SCOTT , STAT, bytes received via SQL*Net from client , 570118, 116.97k, , , , , 95.02k bytes per roundtrip 114, SCOTT , STAT, SQL*Net roundtrips to/from client , 6, 1.23, , , , , 2 per execution 114, SCOTT , TIME, parse time elapsed , 414, 84.94us, .0%, [ ], , , 114, SCOTT , TIME, DB CPU , 21997, 4.51ms, .5%, [ ], , , 114, SCOTT , TIME, sql execute elapsed time , 19326, 3.96ms, .4%, [ ], , , 114, SCOTT , TIME, DB time , 22041, 4.52ms, .5%, [ ], , , 2.63 % unaccounted time 114, SCOTT , WAIT, SQL*Net message to client , 5, 1.03us, .0%, [ ], 6, 1.23, .83us average wait 114, SCOTT , WAIT, SQL*Net message from client , 4723914, 969.16ms, 96.9%, [WWWWWWWWWW], 6, 1.23, 787.32ms average wait 114, SCOTT , WAIT, SQL*Net more data from client , 659, 135.2us, .0%, [ ], 69, 14.16, 9.55us average wait -- End of Stats snap 1, end=2021-01-20 10:19:43, seconds=4.9 <No active sessions captured during the sampling period> -- End of ASH snap 1, end=2021-01-20 10:19:43, seconds=5, samples_taken=47 PL/SQL procedure successfully completed. SYS@book> @ tpt/snapper ash 5 1 114 Sampling SID 114 with interval 5 seconds, taking 1 snapshots... -- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) <No active sessions captured during the sampling period> -- End of ASH snap 1, end=2021-01-20 10:21:09, seconds=5, samples_taken=42 PL/SQL procedure successfully completed. --//ash 什么也没有抓到. --//总之我没有遇到作者的情况,也许是某个版本的问题吧.

编辑推荐：

返回列表

[20210120]in list与绑定变量个数.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推