[20250421]in list相关问题.txt

来源：这里教程网时间：2026-03-03 21:51:43 作者：

[20250421]in list相关问题.txt --//前几天测试in list相关绑定变量太多的问题，有点吃惊的是，大量执行相同sql语句，执行很慢，而且看不到相关等待事件，问题已 --//经确定主要集中在客户端。 --//我也测试了通过函数str2varlist，str2numlist，这样仅仅将数字或者字符拼接成一个通过英文逗号分割的字符串作为函数的参数来 --//实现绑定变量的情况，测试1个参数包括1000个数字参数的情况，可以发现服务端消耗CPU相对很多，但是执行语句的时间缩短了，仅 --//仅仅仅一个赋值操作。 --//可惜到目前为止我没有发现某个开发程序使用类似str2varlist，str2numlist函数，还是使用拼接的方式来实现，这样程序出现大量 --//相关语句的文字变量，即使使用cursor_sharing=force,也仅仅稍微缓解。 --//测试发现in list如果绑定变量很多，其子光标堆6占用chunk会很多，通过测试说明问题，另外顺序测试使用str2varlist， --//str2numlist函数的情况。 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.建立测试环境以及相关函数以及测试中使用的脚本： CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER / CREATE OR REPLACE FUNCTION str2numlist (p_string IN VARCHAR2) RETURN numtabletype AS v_str LONG DEFAULT p_string || ','; v_n PLS_INTEGER; v_data numtabletype := numtabletype (); BEGIN LOOP v_n := TO_NUMBER (INSTR (v_str, ',')); EXIT WHEN (NVL (v_n, 0) = 0); v_data.EXTEND; v_data (v_data.COUNT) := LTRIM (RTRIM (SUBSTR (v_str, 1, v_n - 1))); v_str := SUBSTR (v_str, v_n + 1); END LOOP; RETURN v_data; END; / CREATE OR REPLACE TYPE vartabletype AS TABLE OF VARCHAR2 (4000) / CREATE OR REPLACE FUNCTION str2varlist (p_string IN VARCHAR2) RETURN vartabletype AS v_str LONG DEFAULT p_string || ','; v_n PLS_INTEGER; v_data vartabletype := vartabletype (); BEGIN LOOP v_n := INSTR (v_str, ','); EXIT WHEN (NVL (v_n, 0) = 0); v_data.EXTEND; v_data (v_data.COUNT) := LTRIM (RTRIM (SUBSTR (v_str, 1, v_n - 1))); v_str := SUBSTR (v_str, v_n + 1); END LOOP; RETURN v_data; END; / create table t as select * from all_objects; create unique index i_t_object_id on t(object_id); --//分析略。 $ cat ax.txt set pagesize 0 set head off set feedback off set verify off set timing off spool b1.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; spool off spool b2.txt --select 'set timing on' txt from dual; select 'select count(data_object_id) from t where object_id in (' txt from dual ; select ':b'||to_char(level)||',' txt from dual connect by level<=&&1 -1 ; select ':b'||to_char(&&1)||');' txt from dual ; --select 'set timing off' txt from dual; spool off set pagesize 9999 set head on set feedback 6 --//没有采用集合方式。 $ seq 100 | paste -sd',' >|aa.txt $ cat str2numlist.txt variable v_str varchar2(4000) exec :v_str := '1,2,3,4,...,100'; --// 太长，我截断了，在vim下直接读取aa.txt进来修改一些就可以了。 SELECT COUNT (data_object_id) FROM t where object_id in (select * from str2numlist(:v_str) ); 3.测试： SCOTT@book01p> @ a.txt 100 --//输出略。in list 仅仅包括100个绑定变量参数。 SCOTT@book01p> @ b1.txt SCOTT@book01p> @ b2.txt COUNT(DATA_OBJECT_ID) --------------------- 99 SCOTT@book01p> @ hashz HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- ---------- ------------------- ----------- 2694612486 d62xftyh9t1h6 0 34310 a09c8606 2025-04-19 10:01:32 16777216 SYS@book> @ sharepool/shp4 d62xftyh9t1h6 -1 HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 0000000062FB3460 0000000063498BF8 select count(data_object_id) from t wher 0 0 1 0000000067DB3D10 0000000067E59CB0 17832 80944 6353 105129 105129 2694612486 d62xftyh9t1h6 0 parent handle address 0000000063498BF8 0000000063498BF8 select count(data_object_id) from t wher 0 0 1 0000000062F875E0 00 8128 0 0 8128 8128 2694612486 d62xftyh9t1h6 65535 --//堆6描述符=0000000067E59CB0。 SYS@book> @ ksmsp 0000000067E59CB0 '' LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 HEAP_DESC --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- ------------------------- SGA 0000000069738A30 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000069738A30 0000000069739A30 KSMCHPAR=0000000067E59CB0 SGA 0000000069439FB0 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000069439FB0 000000006943AFB0 KSMCHPAR=0000000067E59CB0 SGA 0000000069438FB0 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000069438FB0 0000000069439FB0 KSMCHPAR=0000000067E59CB0 SGA 0000000068F1BFD0 1 4 SQLA^a09c8606 4096 recr 4095 0000000067E59CB0 0000000068F1BFD0 0000000068F1CFD0 KSMCHPAR=0000000067E59CB0 SGA 00000000676EFF38 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 00000000676EFF38 00000000676F0F38 KSMCHPAR=0000000067E59CB0 SGA 00000000676EEF38 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 00000000676EEF38 00000000676EFF38 KSMCHPAR=0000000067E59CB0 SGA 000000006749C480 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 000000006749C480 000000006749D480 KSMCHPAR=0000000067E59CB0 SGA 0000000067473480 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000067473480 0000000067474480 KSMCHPAR=0000000067E59CB0 SGA 00000000655D94A8 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 00000000655D94A8 00000000655DA4A8 KSMCHPAR=0000000067E59CB0 SGA 0000000065588320 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000065588320 0000000065589320 KSMCHPAR=0000000067E59CB0 SGA 00000000649767F0 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 00000000649767F0 00000000649777F0 KSMCHPAR=0000000067E59CB0 SGA 00000000649757F0 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 00000000649757F0 00000000649767F0 KSMCHPAR=0000000067E59CB0 SGA 0000000064470D48 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000064470D48 0000000064471D48 KSMCHPAR=0000000067E59CB0 SGA 000000006446FD48 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 000000006446FD48 0000000064470D48 KSMCHPAR=0000000067E59CB0 SGA 0000000063ADFFD0 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000063ADFFD0 0000000063AE0FD0 KSMCHPAR=0000000067E59CB0 SGA 0000000062B714A8 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000062B714A8 0000000062B724A8 KSMCHPAR=0000000067E59CB0 SGA 0000000062A2BFB8 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000062A2BFB8 0000000062A2CFB8 KSMCHPAR=0000000067E59CB0 SGA 0000000062A2AFB8 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000062A2AFB8 0000000062A2BFB8 KSMCHPAR=0000000067E59CB0 SGA 0000000060E57FD0 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000060E57FD0 0000000060E58FD0 KSMCHPAR=0000000067E59CB0 SGA 0000000060D55FD0 1 4 SQLA^a09c8606 4096 freeabl 0 0000000067E59CB0 0000000060D55FD0 0000000060D56FD0 KSMCHPAR=0000000067E59CB0 SGA 0000000067E59440 1 1 KGLH0^a09c8606 4096 recr 4095 0000000062F875E0 0000000067E59440 0000000067E5A440 21 rows selected. --//可以发现简单的sql语句包括100个绑定变量,就导致堆6占用20个chunk，每个大小4096*20 = 81920. --//注：其中堆6的描述符的位置非常特殊在父游标堆0中(KSMCHCOM=KGLH0^a09c8606)。 --//建立b2.txt还是使用绑定变量的情况，如果使用文字拼接，这样消耗共享池内存更加厉害，并且每次执行也许都是硬解析。除非设置 --//cursor_sharing=force. 4.再来看看使用str2numlist的情况： SCOTT@book01p> @　str2numlist.txt PL/SQL procedure successfully completed. COUNT(DATA_OBJECT_ID) --------------------- 99 SCOTT@book01p> @hashz HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- ---------- ------------------- ----------- 3783993142 fugmvwbhsq9tq 0 75574 e18b2736 2025-04-19 10:12:49 16777216 SYS@book> @ sharepool/shp4 fugmvwbhsq9tq -1 HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 0000000067AEB7A0 0000000063DB4208 SELECT COUNT (data_object_id) FROM t whe 0 0 0 00000000635E7ED0 00000000620F6398 8080 32368 3382 43830 43830 3783993142 fugmvwbhsq9tq 0 parent handle address 0000000063DB4208 0000000063DB4208 SELECT COUNT (data_object_id) FROM t whe 0 0 0 000000006737D270 00 4064 0 0 4064 4064 3783993142 fugmvwbhsq9tq 65535 SYS@book> @ ksmsp 00000000620F6398 '' LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 HEAP_DESC --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- ------------------------- SGA 0000000065CF5938 1 4 SQLA^e18b2736 4096 recr 4095 00000000620F6398 0000000065CF5938 0000000065CF6938 KSMCHPAR=00000000620F6398 SGA 0000000065CE8938 1 4 SQLA^e18b2736 4096 freeabl 0 00000000620F6398 0000000065CE8938 0000000065CE9938 KSMCHPAR=00000000620F6398 SGA 0000000065CE7938 1 4 SQLA^e18b2736 4096 freeabl 0 00000000620F6398 0000000065CE7938 0000000065CE8938 KSMCHPAR=00000000620F6398 SGA 0000000065CE6938 1 4 SQLA^e18b2736 4096 freeabl 0 00000000620F6398 0000000065CE6938 0000000065CE7938 KSMCHPAR=00000000620F6398 SGA 00000000614B6C08 1 4 SQLA^e18b2736 4096 freeabl 0 00000000620F6398 00000000614B6C08 00000000614B7C08 KSMCHPAR=00000000620F6398 SGA 0000000060F24FD0 1 4 SQLA^e18b2736 4096 freeabl 0 00000000620F6398 0000000060F24FD0 0000000060F25FD0 KSMCHPAR=00000000620F6398 SGA 0000000060D54FD0 1 4 SQLA^e18b2736 4096 freeabl 0 00000000620F6398 0000000060D54FD0 0000000060D55FD0 KSMCHPAR=00000000620F6398 SGA 0000000060D53FD0 1 4 SQLA^e18b2736 4096 freeabl 0 00000000620F6398 0000000060D53FD0 0000000060D54FD0 KSMCHPAR=00000000620F6398 SGA 00000000620F5B40 1 1 KGLH0^e18b2736 4096 recr 4095 000000006737D270 00000000620F5B40 00000000620F6B40 9 rows selected. --//可以发现这样的情况导致堆6占用8个chunk，每个4096字节，至少比前面少12个chunk。而且在使用绑定变量的情况下更加节省共享池 --//内存。当然没有考虑函数str2numlist占用共享池内存的情况，不过这个是共用的，不过还是顺便看看占用共享内存的情况。 SYS@book> @ kglob '' '' '' kglnaobj='STR2NUMLIST' ============================== INST_ID : 1 OWNER : SCOTT NAME : STR2NUMLIST DB_LINK : NAMESPACE : TABLE/PROCEDURE TYPE : FUNCTION NAMESPACE_NUM : 1 NAMESPACE_HEX : 1 SHARABLE_MEM : 24272 PERSISTENT_MEM : 0 RUNTIME_MEM : 0 LOADS : 1 EXECUTIONS : 1 LOCKS : 1 PINS : 0 KEPT : NO CHILD_LATCH : 118195 INVALIDATIONS : 0 HASH_VALUE : 1321061811 HASH_HEX : 4ebdcdb3 LOCK_MODE : NULL PIN_MODE : NONE STATUS : VALID TIMESTAMP : 2025-04-19/09:54:19 PREVIOUS_TIMESTAMP : LOCKED_TOTAL : 2 PINNED_TOTAL : 2 PROPERTY : FULL_HASH_VALUE : 8fd070c6ff6ecf415515b2d94ebdcdb3 CON_ID : 3 CON_NAME : BOOK01P ADDR : 0000000063FE3988 PAR_ADDR : 0000000063FE3988 HANDLE_TYPE : parent handle address KGLOBHD0 : 00000000636E15E0 KGLOBHD6 : 00 EDITION : SQL_ID : OBJECT_STR : STR2NUMLIST.SCOTT.BOOK01P\x1\0\0\0 PL/SQL procedure successfully completed. $ sql_idz.sh 'STR2NUMLIST.SCOTT.BOOK01P\x1\0\0\0' 3 sql_text = STR2NUMLIST.SCOTT.BOOK01P\x1\0\0\0 full_hash_value(16) = 8FD070C6FF6ECF415515B2D94EBDCDB3 or 8fd070c6ff6ecf415515b2d94ebdcdb3 xxxxx_matching_signature(10) = 6131003114133507507 or 24577747187843059123 hash_value(10) = 1321061811 or hash_value(16) = 4EBDCDB3 or 4ebdcdb3 sql_id(16) = 5515B2D94EBDCDB3 or 5515b2d94ebdcdb3 sql_id(32) = 5a5dkv57bvmdm sql_id(32) = 5a5dkv57bvmdm sql_id(32) = 5a5dkv57bvmdm --//hash_value值的计算有前面的输出一致。 SYS@book> @ sharepool/shp4 '' 1321061811 SYS@book> @ pr ============================== HANDLE_TYPE : parent handle address KGLHDADR : 0000000063FE3988 KGLHDPAR : 0000000063FE3988 C40 : STR2NUMLIST.SCOTT KGLHDLMD : 1 KGLHDPMD : 0 KGLHDIVC : 0 KGLOBHD0 : 00000000636E15E0 KGLOBHD6 : 00 KGLOBHS0 : 4064 KGLOBHS6 : 0 KGLOBT16 : 0 N0_6_16 : 4064 N20 : 24272 KGLNAHSH : 1321061811 KGLOBT03 : KGLOBT09 : 0 PL/SQL procedure successfully completed. SYS@book> @ fchaz 0000000063FE3988 LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 0000000063FE3958 1 1 KGLHD 816 recr 80 00 0000000063FE3958 0000000063FE3C88 SYS@book> @ ksmsp 00000000636E15E0 '' LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 HEAP_DESC --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- ------------------------- SGA 0000000063DA6448 1 1 KGLH0^4ebdcdb3 4096 recr 4095 00000000636E15E0 0000000063DA6448 0000000063DA7448 KSMCHPAR=00000000636E15E0 SGA 00000000636E1570 1 1 KGLDA 512 freeabl 0 00 00000000636E1570 00000000636E1770 --//可以发现函数并没有占用多少共享池内存空间。 --//通过这么简单的比较可以发现使用函数带来的好处。 --//1.先拼接数字或者字符到1个字符串变量，然后仅仅传递1个参数，很容易实现绑定变量。 --//2.即使cursor_sharing=force的情况下也可以节约共享池内存，我的测试使用100个参数。 --//注：测试10个绑定变量参数的情况，前者占用6个chunk，大小4096. 测试1000个绑定变量参数的情况，前者占用165个chunk，大小4096. --//而后者无论什么情况占用chunk都是8,大小4096. 5.相关缺点： --//使用in list最大的问题就是参数限制最多1000个，超过会报错。 --//而使用函数虽然没有这个限制，但是传入的参数是字符串类型，最大长度4000字节，这样可能不能容纳1000个参数拼接的字符串。 --//注：我不知道是否可以将参数定义clob类型，这样就没有这个限制，编程是否方便。 --//另外注意的问题采用str2numlist,str2varlist，缺省返回值行数8168,可能改变多表的连接顺序，可以通过提示控制。

编辑推荐：

返回列表

[20250421]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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推