[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,可能改变多表的连接顺序，可以通过提示控制。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3081957/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
