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

来源：这里教程网时间：2026-03-03 12:03:27 作者：

[20180930]in list与绑定变量个数.txt --//跟别人的讨论,提到如果绑定变量个数太多,会导致执行时替换绑定变量时间太长,导致sql语句执行缓慢. --//在dbsnake 《基于Oracle的SQL优化》提到,我给测试看看: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 SCOTT@test01p> create table t as select * from all_objects; Table created. SCOTT@test01p> create unique index i_t_object_id on t(object_id); Index created. --//分析表略. --//正好前几天做了http://blog.itpub.net/267265/viewspace-2214966/=>[20180926]神奇的规避ORA-01795方法.txt --//使用这个例子来演示看看. 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 Elapsed: 00:00:00.08 Elapsed: 00:00:00.04 Elapsed: 00:00:00.04 --//第一次要经过1次硬解析,时间多一点点正常的.做一个表格,取第2次以后的执行时间: 绑定变量数量执行时间 -------------------------- 1000 00:00:00.04 2000 00:00:00.14 3000 00:00:00.36 4000 00:00:00.70 5000 00:00:01.17 6000 00:00:01.92 7000 00:00:02.76 10000 00:00:08.12 (第1次执行00:00:11.56) -------------------------- --//实际上3000个变量,甚至4000个以内并没有这么严重. --//补充7000的执行计划: Plan hash value: 1665370044 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 305 (100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | | 2 | INLIST ITERATOR | | | | | | | 3 | TABLE ACCESS BY INDEX ROWID| T | 7000 | 49000 | 305 (1)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | I_T_OBJECT_ID | 7000 | | 182 (2)| 00:00:01 | ------------------------------------------------------------------------------------------------ --//打开statistics_level = all SCOTT@test01p> alter session set statistics_level = all; Session altered. SCOTT@test01p> @ b.txt COUNT(DATA_OBJECT_ID) --------------------- 1619 1 row selected. Elapsed: 00:00:04.87 SCOTT@test01p> @ b.txt COUNT(DATA_OBJECT_ID) --------------------- 1619 1 row selected. Elapsed: 00:00:03.65 SCOTT@test01p> @ b.txt COUNT(DATA_OBJECT_ID) --------------------- 1619 1 row selected. Elapsed: 00:00:02.74 SCOTT@test01p> @ dpc '' '-peeked_binds -note -alias' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 3q9p1rxcghc8t, child number 1 ------------------------------------- select count(data_object_id) from t where (1,object_id) in ( (1,:b1), (1,:b2), (1,:b3), (1,:b4), (1,:b5), (1,:b6), (1,:b7), (1,:b8), (1,:b9), (1,:b10), (1,:b11), (1,:b12), (1,:b13), (1,:b14), (1,:b15), (1,:b16), (1,:b17), (1,:b18), (1,:b19), (1,:b20), (1,:b21), (1,:b22), (1,:b23), (1,:b24), (1,:b25), (1,:b26), (1,:b27), (1,:b28), (1,:b29), (1,:b30), (1,:b31), (1,:b32), (1,:b33), (1,:b34), (1,:b35), (1,:b36), (1,:b37), (1,:b38), (1,:b39), (1,:b40), (1,:b41), (1,:b42), (1,:b43), (1,:b44), (1,:b45), (1,:b46), (1,:b47), (1,:b48), (1,:b49), (1,:b50), (1,:b51), (1,:b52), (1,:b53), (1,:b54), (1,:b55), (1,:b56), (1,:b57), (1,:b58), (1,:b59), (1,:b60), (1,:b61), (1,:b62), (1,:b63), (1,:b64), (1,:b65), (1,:b66), (1,:b67), (1,:b68), (1,:b69), (1,:b70), (1,:b71), (1,:b72), (1,:b73), (1,:b74), (1,:b75), (1,:b76), (1,:b77), (1,:b78), (1,:b79), (1,:b80), (1,:b81), (1,:b82), (1,:b83), (1,:b84), (1,:b85), (1,:b86), (1,:b87), (1,:b88), (1,:b89), (1,:b90), (1,:b91), (1,:b92), (1,:b93), (1,:b94), (1,:b95) Plan hash value: 1665370044 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 305 (100)| | 1 |00:00:00.03 | 210 | | 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.03 | 210 | | 2 | INLIST ITERATOR | | 1 | | | | | 6745 |00:00:00.03 | 210 | | 3 | TABLE ACCESS BY INDEX ROWID| T | 7000 | 7000 | 49000 | 305 (1)| 00:00:01 | 6745 |00:00:00.02 | 210 | |* 4 | INDEX UNIQUE SCAN | I_T_OBJECT_ID | 7000 | 7000 | | 182 (2)| 00:00:01 | 6745 |00:00:00.01 | 105 | ----------------------------------------------------------------------------------------------------------------------------------------- ... --//打开statistics_level = all,实际上导致执行时间延长的. A-Time=00:00:00.03. --//说明绑定变量替换需要时间还是蛮多的,但是要在3000个以内消耗并不是很大. 4.我也测试dbsnake书上的脚本,并没有他测试的问题这么严重: SCOTT@test01p> create table t1 as select * from all_objects; Table created. --//注:没有建立索引 --//建立过程. CREATE OR REPLACE PROCEDURE p_gennrate_many_bind_v_demo ( i_n_bind_v_number NUMBER ,o_vc_return_flag OUT VARCHAR2 ) IS vc_sql VARCHAR2 (32767); vc_inlist VARCHAR2 (32767); n_temp NUMBER; BEGIN vc_inlist := '1'; FOR i IN 2 .. i_n_bind_v_number LOOP vc_inlist := vc_inlist || ',' || TO_CHAR (i); END LOOP; vc_sql := 'select count(*) from t1 where object_id in (' || vc_inlist || ')' || ' or object_id in ( ' || vc_inlist || ')' || ' or object_id in ( ' || vc_inlist || ')' || ' or object_id in ( ' || vc_inlist || ')' || ' or object_id in ( ' || vc_inlist || ')' || ' or object_id in ( ' || vc_inlist || ')'; EXECUTE IMMEDIATE vc_sql INTO n_temp; o_vc_return_flag := TO_CHAR (n_temp); EXCEPTION WHEN OTHERS THEN o_vc_return_flag := 'E' || '-' || SQLCODE || '_' || SQLERRM; RETURN; END p_gennrate_many_bind_v_demo; / SCOTT@test01p> alter system set cursor_sharing=force scope=memory; System altered. SCOTT@test01p> alter system flush shared_pool; System altered. --//退出再进入: SCOTT@test01p> var a varchar2(32767) SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:02.99 --//第1次执行硬解析执行时间长一点是正常的. SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:00.53 SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:00.46 SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:00.50 SCOTT@test01p> print :a A --------- 891 --//很奇怪使用他的例子问题没有这么严重.我猜测可能他使用存储过程的原因. SCOTT@test01p> create unique index i_t1_object_id on t1(object_id); Index created. SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:02.57 SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:00.31 SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:00.31 SCOTT@test01p> @ dpc 3z307yzbxg19z '-peeked_binds -note -alias' ... Plan hash value: 351707332 -------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 181 (100)| | 1 |00:00:00.01 | 16 | | 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.01 | 16 | | 2 | INLIST ITERATOR | | 1 | | | | | 891 |00:00:00.01 | 16 | |* 3 | INDEX UNIQUE SCAN| I_T1_OBJECT_ID | 1000 | 6000 | 30000 | 181 (2)| 00:00:01 | 891 |00:00:00.01 | 16 | -------------------------------------------------------------------------------------------------------------------------------- --//如果建立索引.时间还更小一些.另外Starts=1000次.作者定义变量重复定义. 5.修改作者的脚本: CREATE OR REPLACE PROCEDURE p_gennrate_many_bind_v_demo1 ( i_n_bind_v_number NUMBER ,o_vc_return_flag OUT VARCHAR2 ) IS vc_sql VARCHAR2 (32767); vc_inlist0 VARCHAR2 (32767); vc_inlist1 VARCHAR2 (32767); vc_inlist2 VARCHAR2 (32767); vc_inlist3 VARCHAR2 (32767); vc_inlist4 VARCHAR2 (32767); vc_inlist5 VARCHAR2 (32767); n_temp NUMBER; BEGIN vc_inlist0 := '1'; FOR i IN 2 .. i_n_bind_v_number LOOP vc_inlist0 := vc_inlist0 || ',' || TO_CHAR (i); END LOOP; vc_inlist1 := '1001'; FOR i IN 2 .. i_n_bind_v_number LOOP vc_inlist1 := vc_inlist1 || ',' || TO_CHAR (i+1000); END LOOP; vc_inlist2 := '2001'; FOR i IN 2 .. i_n_bind_v_number LOOP vc_inlist2 := vc_inlist2 || ',' || TO_CHAR (i+2000); END LOOP; vc_inlist3 := '3001'; FOR i IN 2 .. i_n_bind_v_number LOOP vc_inlist3 := vc_inlist3 || ',' || TO_CHAR (i+3000); END LOOP; vc_inlist4 := '4001'; FOR i IN 2 .. i_n_bind_v_number LOOP vc_inlist4 := vc_inlist4 || ',' || TO_CHAR (i+4000); END LOOP; vc_inlist5 := '5001'; FOR i IN 2 .. i_n_bind_v_number LOOP vc_inlist5 := vc_inlist5 || ',' || TO_CHAR (i+5000); END LOOP; vc_sql := 'select count(*) from t1 where object_id in (' || vc_inlist0 || ')' || ' or object_id in ( ' || vc_inlist1 || ')' || ' or object_id in ( ' || vc_inlist2 || ')' || ' or object_id in ( ' || vc_inlist3 || ')' || ' or object_id in ( ' || vc_inlist4 || ')' || ' or object_id in ( ' || vc_inlist5 || ')'; EXECUTE IMMEDIATE vc_sql INTO n_temp; o_vc_return_flag := TO_CHAR (n_temp); EXCEPTION WHEN OTHERS THEN o_vc_return_flag := 'E' || '-' || SQLCODE || '_' || SQLERRM; RETURN; END p_gennrate_many_bind_v_demo1; / --//删除索引测试. SCOTT@test01p> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ -------------------- ---------- cursor_sharing string FORCE SCOTT@test01p> exec p_gennrate_many_bind_v_demo1(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:03.09 SCOTT@test01p> exec p_gennrate_many_bind_v_demo1(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:00.49 SCOTT@test01p> exec p_gennrate_many_bind_v_demo1(1000,:a); PL/SQL procedure successfully completed. Elapsed: 00:00:00.48 SCOTT@test01p> print :a A ---- 5801 --//6000个绑定变量,测试也就是00:00:00.48.第一次硬解析时间长一点正常的. 6.注意参数cursor_sharing=force: --//最后注意cursor_sharing=force对于集合方式查询的影响,因为这样(1,:b1) 变成 (:"SYS_B001",:b1),这样无形绑定变量参数增加. SCOTT@test01p> @ a.txt 2000 SCOTT@test01p> alter session set cursor_sharing=force ; Session altered. SCOTT@test01p> @ b.txt COUNT(DATA_OBJECT_ID) --------------------- 1185 1 row selected. Elapsed: 00:00:48.51 ==>第1次. SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 1yb6a2tn2gsz6, child number 0 ------------------------------------- select count(data_object_id) from t where (:"SYS_B_0000",object_id) in ( (:"SYS_B_0001",:b1), (:"SYS_B_0002",:b2), (:"SYS_B_0003",:b3), (:"SYS_B_0004",:b4), (:"SYS_B_0005",:b5), (:"SYS_B_0006",:b6), (:"SYS_B_0007",:b7), (:"SYS_B_0008",:b8), (:"SYS_B_0009",:b9), (:"SYS_B_0010",:b10), (:"SYS_B_0011",:b11), (:"SYS_B_0012",:b12), (:"SYS_B_0013",:b13), (:"SYS_B_0014",:b14), (:"SYS_B_0015",:b15), (:"SYS_B_0016",:b16), (:"SYS_B_0017",:b17), (:"SYS_B_0018",:b18), (:"SYS_B_0019",:b19), (:"SYS_B_0020",:b20), (:"SYS_B_0021",:b21), (:"SYS_B_0022",:b22), (:"SYS_B_0023",:b23), (:"SYS_B_0024",:b24), (:"SYS_B_0025",:b25), (:"SYS_B_0026",:b26), (:"SYS_B_0027",:b27), (:"SYS_B_0028",:b28), (:"SYS_B_0029",:b29), (:"SYS_B_0030",:b30), (:"SYS_B_0031",:b31), (:"SYS_B_0032",:b32), (:"SYS_B_0033",:b33), (:"SYS_B_0034",:b34), (:"SYS_B_0035",:b35), (:"SYS_B_0036",:b36), (:"SYS_B_0037",:b37), (:"SYS_B_0038",:b38), (:"SYS_B_0039",:b39), (:"SYS_B_0040",:b40), (:"SYS_B_0041",:b41), (:"SYS_B_0042",:b42), Plan hash value: 2966233522 ---------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 651 (100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | TABLE ACCESS FULL| T | 20 | 140 | 651 (36)| 00:00:01 | ---------------------------------------------------------------------------- --//这样执行计划也发生了变化,走全表扫描. SCOTT@test01p> @ b.txt COUNT(DATA_OBJECT_ID) --------------------- 1185 1 row selected. Elapsed: 00:00:07.48 --//这样执行时间也增加不少. --//加入提示 /*+ CURSOR_SHARING_EXACT */ ,继续测试: SCOTT@test01p> @ b.txt COUNT(DATA_OBJECT_ID) --------------------- 1185 1 row selected. Elapsed: 00:00:00.34 SCOTT@test01p> @ b.txt COUNT(DATA_OBJECT_ID) --------------------- 1185 1 row selected. Elapsed: 00:00:00.14 --//可以发现执行时间减少不少. 总结: --//总之:绑定变量数量不多,问题没这么严重.反倒是我前面使用集合绑定变量多执行时间长一些.