[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 什么也没有抓到.
--//总之我没有遇到作者的情况,也许是某个版本的问题吧.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2751278/，如需转载，请注明出处，否则将追究法律责任。
