[20250415]21c下测试软软解析与大量绑定变量的情况.txt

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

[20250415]21c下测试软软解析与大量绑定变量的情况.txt --//测试大量用户同时执行相同sql语句存在大量绑定变量的情况，看看会出现什么等待事件。 --//前几天测试单个用户的执行情况，至少我认为问题不是很大，今天测试大量执行的情况，结果有点出乎我的意料，再次说明思考问题 --//不能太想当然,通过例子说明问题。 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 table t as select * from all_objects; create unique index i_t_object_id on t(object_id); --//分析略。 $ cat a.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 (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 SCOTT@book01p> @ a.txt 1000 --//输出略。这样建立2个文本b1.txt,b2.txt . --//采用集合的比较，这样可以突破in list 1000个值的限制。 $ uniq -c loop5.txt 1 @ b1.txt 1 @ b2.txt 99 / 1 quit --//先执行b1.txt赋值，然后执行b2.txt 脚本 100次. 3.测试： $ zzdate; sqlplus -s -l scott/book@book01p @loop5.txt > /dev/null;zzdate trunc(sysdate)+08/24+49/1440+23/86400 -1744764563.257191308 trunc(sysdate)+08/24+49/1440+27/86400 1744764567.798606704 --//Sum = 4.541415396 --//首先执行1次，避免一些递归语句。 $ zzdate;seq 50 | xargs -P 50 -IQ sqlplus -s -l scott/book@book01p @loop5.txt > /dev/null;zzdate trunc(sysdate)+08/24+49/1440+52/86400 -1744764592.903725398 trunc(sysdate)+08/24+50/1440+49/86400 1744764649.299647668 --//Sum = 56.39592227 SYS@book> @ ashtop event 1=1 trunc(sysdate)+08/24+49/1440+52/86400 trunc(sysdate)+08/24+50/1440+49/86400 Total Distinct Distinct Distinct Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- ----------- 73 1.3 87% | SQL*Net more data from client 2025-04-16 08:49:55 2025-04-16 08:50:46 1 43 43 9 .2 11% | 2025-04-16 08:49:52 2025-04-16 08:50:42 6 9 9 1 .0 1% | ADR block file read 2025-04-16 08:50:05 2025-04-16 08:50:05 1 1 1 1 .0 1% | LGWR all worker groups 2025-04-16 08:50:47 2025-04-16 08:50:47 1 1 1 SYS@book> @ ashtop sql_id,event 1=1 trunc(sysdate)+08/24+49/1440+52/86400 trunc(sysdate)+08/24+50/1440+49/86400 Total Distinct Distinct Distinct Seconds AAS %This SQL_ID EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------- ------------------------------------------ ------------------- ------------------- ---------- -------- ----------- 73 1.3 87% | SQL*Net more data from client 2025-04-16 08:49:55 2025-04-16 08:50:46 1 43 43 7 .1 8% | bg8772p2chvnk 2025-04-16 08:49:56 2025-04-16 08:50:42 6 7 7 2 .0 2% | 2025-04-16 08:49:52 2025-04-16 08:50:03 1 2 2 1 .0 1% | 661ut1qj78akz ADR block file read 2025-04-16 08:50:05 2025-04-16 08:50:05 1 1 1 1 .0 1% | LGWR all worker groups 2025-04-16 08:50:47 2025-04-16 08:50:47 1 1 1 --//使用ashtop看仅仅7秒花在语句的执行，有点奇怪的是没有看到其他等待事件，没有看到mutex以及latch相关等待事件。 --//看到大量的SQL*Net more data from client。 --//靠前的等待事件是SQL*Net more data from client，而且居然73秒，几乎每秒都存在这个等待事情。很明显awr取样放大该等待 --//事件。导致出现SQL*Net more data from client事件的1个原因是sql语句太长。要想避免该等待事件，修改sqlnet.ora文件加入 --//DEFAULT_SDU_SIZE=1048576. $ ls -l b[12].txt -rw-r--r--. 1 oracle oinstall 34723 2025-04-15 16:00:29 b1.txt -rw-r--r--. 1 oracle oinstall 10955 2025-04-15 16:00:29 b2.txt --//补充说明可以修改sqlnet.ora文件，加入DEFAULT_SDU_SIZE，再测试就看不见SQL*Net more data from client相关等待事件。 --//注意21c版本sqlnet.ora文件的位置. $ cat /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora DEFAULT_SDU_SIZE=1048576 #SQLNET.COMPRESSION=on #SQLNET.COMPRESSION_LEVELS=(low,high) #SQLNET.COMPRESSION_THRESHOLD=1024 $ zzdate;seq 50 | xargs -P 50 -IQ sqlplus -s -l scott/book@book01p @loop5.txt > /dev/null;zzdate trunc(sysdate)+08/24+56/1440+10/86400 -1744764970.506092823 trunc(sysdate)+08/24+57/1440+06/86400 1744765026.801417850 --//Sum = 56.295325027,跟前面的测试执行时间基本一致，看看相关等待事件。 SYS@book> @ ashtop event 1=1 trunc(sysdate)+08/24+56/1440+10/86400 trunc(sysdate)+08/24+57/1440+06/86400 Total Distinct Distinct Distinct Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- ----------- 11 .2 79% | 2025-04-16 08:56:10 2025-04-16 08:56:57 8 11 11 3 .1 21% | db file async I/O submit 2025-04-16 08:56:46 2025-04-16 08:56:49 1 3 3 --//SQL*Net more data from client消失了，从这里也应该注意awr报表的局限性，如果解决SQL*Net more data from client，相关报 --//表好看了，但是实际的问题还是存在。但是有点意外在做绑定变量替换时，没有相关等待事件吗？其他5X秒在做什么，oracle从 --//ashtop看基本看不到问题。 --//我以前一直认为绑定变量太多对性能影响不大，但是测试下来很奇怪，看不到相关等待事件。 --//难道在做绑定变量替换时，oracle没有相关等待事件吗？ SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+08/24+56/1440+10/86400" -- MutexProf by Tanel Poder (http://www.tanelpoder.com) -- Showing profile of top 50 sleeps... -- column info : id idn hash hash_value=>hash_value ts=>sleep_timestamp -- req=>requesting_session blk=>blocking_session val=>mutex_value maddr=>mutex_addr no rows selected 4.继续测试： --//减少绑定变量赋值。 SCOTT@book01p> @ a.txt 10 --//增加测试数量4000，@ a.txt 10 的情况： $ uniq -c loop6.txt 1 @ b1.txt 1 @ b2.txt 3999 / 1 quit --//首先执行1次，避免一些递归语句。 $ zzdate; sqlplus -s -l scott/book@book01p @loop6.txt > /dev/null;zzdate trunc(sysdate)+09/24+17/1440+19/86400 -1744766239.672961545 trunc(sysdate)+09/24+17/1440+22/86400 1744766242.512937109 --//Sum = 2.839975564,可以发现给10个变量赋值，执行4000次，仅仅3秒不到测试完成。 $ zzdate;seq 50 | xargs -P 50 -IQ sqlplus -s -l scott/book@book01p @loop6.txt > /dev/null;zzdate trunc(sysdate)+09/24+18/1440+23/86400 -1744766303.247885469 trunc(sysdate)+09/24+19/1440+03/86400 1744766343.436237674 --//Sum = 40.188352205 SYS@book> @ ashtop event,sql_id,p1raw 1=1 trunc(sysdate)+09/24+18/1440+23/86400 trunc(sysdate)+09/24+19/1440+03/86400 Total Distinct Distinct Distinct Seconds AAS %This EVENT SQL_ID P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ----------- ------------- ----------------- ------------------- ------------------- ---------- -------- ----------- 12 .3 63% | cptkd039su71r 2025-04-16 09:18:29 2025-04-16 09:18:58 9 12 12 6 .2 32% | 2025-04-16 09:18:27 2025-04-16 09:18:53 1 6 6 1 .0 5% | f0h5rpzmhju11 2025-04-16 09:18:26 2025-04-16 09:18:26 1 1 1 --//你可以发现赋值10个变量，我的测试50个并发用户同时执行相同sql语句，并没有出现mutex以及latch等待事件， --//按照以前的测试会出现cursor: pin S等待事件。 --//增加并发用户数量到100. $ zzdate;seq 100 | xargs -P 100 -IQ sqlplus -s -l scott/book@book01p @loop6.txt > /dev/null;zzdate trunc(sysdate)+09/24+24/1440+08/86400 -1744766648.186042536 trunc(sysdate)+09/24+25/1440+53/86400 1744766753.643213892 --//Sum = 105.457171356 SYS@book> @ ashtop event,sql_id,p1raw 1=1 trunc(sysdate)+09/24+24/1440+08/86400 trunc(sysdate)+09/24+25/1440+53/86400 Total Distinct Distinct Distinct Seconds AAS %This EVENT SQL_ID P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ----------------------- ------------- ----------------- ------------------- ------------------- ---------- -------- ----------- 37 .4 49% | cptkd039su71r 2025-04-16 09:24:12 2025-04-16 09:25:51 27 32 36 11 .1 14% | 8gbt6t0s3jn0t 2025-04-16 09:24:36 2025-04-16 09:24:54 2 11 11 9 .1 12% | 2025-04-16 09:24:14 2025-04-16 09:25:46 1 9 9 3 .0 4% | db file sequential read 5ft3ypgbyvs42 000000000000000A 2025-04-16 09:24:43 2025-04-16 09:24:45 3 3 3 3 .0 4% | 5pj6mtazkhmdd 2025-04-16 09:24:33 2025-04-16 09:24:47 3 3 3 2 .0 3% | 5rurx5xtjwcu2 2025-04-16 09:24:35 2025-04-16 09:24:48 1 2 2 2 .0 3% | 7jfkksfz5bpn7 2025-04-16 09:24:30 2025-04-16 09:24:41 1 2 2 2 .0 3% | f3ww8rgva3hrs 2025-04-16 09:24:32 2025-04-16 09:24:46 2 2 2 1 .0 1% | LGWR wait for redo copy 0000000000000007 2025-04-16 09:24:32 2025-04-16 09:24:32 1 1 1 1 .0 1% | log file parallel write 0000000000000001 2025-04-16 09:24:46 2025-04-16 09:24:46 1 1 1 1 .0 1% | oracle thread bootstrap 0000000000000000 2025-04-16 09:24:29 2025-04-16 09:24:29 1 1 1 1 .0 1% | os thread creation 000000004D5A3030 2025-04-16 09:24:29 2025-04-16 09:24:29 1 1 1 1 .0 1% | 06gfrprr7w0r2 2025-04-16 09:24:24 2025-04-16 09:24:24 1 1 1 1 .0 1% | 3dbzmtf9ahvzt 2025-04-16 09:24:42 2025-04-16 09:24:42 1 1 1 1 .0 1% | 5ft3ypgbyvs42 2025-04-16 09:24:31 2025-04-16 09:24:31 1 1 1 15 rows selected. SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+09/24+24/1440+08/86400 and idn=3549240375" -- MutexProf by Tanel Poder (http://www.tanelpoder.com) -- Showing profile of top 50 sleeps... -- column info : id idn hash hash_value=>hash_value ts=>sleep_timestamp -- req=>requesting_session blk=>blocking_session val=>mutex_value maddr=>mutex_addr SUM_SLEEPS GETS_DIFF MUTEX_TYPE IDN HASH GET_LOCATION mutex_addr SQL_ID OBJECT_NAME ---------- -------------- --------------- ---------- ---------- --------------------------------- -------------------- ------------- -------------------------------------------------------------------------------- 715 638230 Cursor Pin 3549240375 3549240375 kksfbc [KKSCHLPIN1] 0000000066352B90 cptkd039su71r 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)) 2 166117 Cursor Pin 3549240375 3549240375 kksLockDelete [KKSCHLPIN6] 0000000066352B90 cptkd039su71r 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)) --//即使100个并发，mutex出现可以小量sleep，并没有成为主要等待事件。 --//从以上测试可以发现大量绑定变量替换实际上消耗时间并不小，单次执行感觉不明显，但是累积起来还是很明显的， --//有点不理解的是oracle并没有相关等待事件说明该问题.... 5.其他方法测试： --//我以前写过 [20220104]in list 几种写法性能测试.txt，改写测试看看。 CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER / CREATE OR REPLACE FUNCTION SCOTT.str2numlist (p_string IN VARCHAR2) RETURN numtabletype PIPELINED AS v_str LONG := p_string || ','; v_n PLS_INTEGER; v_index PLS_INTEGER := 1; BEGIN LOOP v_n := TO_NUMBER (INSTR (v_str, ',',v_index)); EXIT WHEN (NVL (v_n, 0) = 0); PIPE ROW ( LTRIM (RTRIM (SUBSTR(p_string, v_index, v_n - v_index)))); v_index := v_n + 1; END LOOP; RETURN; END; / --//改写使用PIPE。 $ seq 1000 | paste -sd',' >|aa.txt $ uniq -c loop8.txt 1 variable v_str varchar2(4000) 1 exec :v_str := '1,2,3,4,5,...,998,999,1000'; --// 太长，我截断了，在vim下直接读取aa.txt进来修改一些就可以了。 1 1 SELECT COUNT (data_object_id) FROM t where object_id in (select * from table (cast(str2numlist(:v_str) as numtabletype))); 99 / 1 quit --//以前测试使用pl/sql，采用直接在sqlplus执行测试比较公平。 $ zzdate;seq 50 | xargs -P 50 -IQ sqlplus -s -l scott/book@book01p @loop8.txt > /dev/null;zzdate trunc(sysdate)+09/24+41/1440+39/86400 -1744767699.018460887 trunc(sysdate)+09/24+42/1440+13/86400 1744767733.266746439 --//Sum = 34.248285552，比前面测试快了不少。 SYS@book> @ ashtop event,sql_id,p1raw 1=1 trunc(sysdate)+09/24+41/1440+39/86400 trunc(sysdate)+09/24+42/1440+13/86400 Total Distinct Distinct Distinct Seconds AAS %This EVENT SQL_ID P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------------------------------------ ------------- ----------------- ------------------- ------------------- ---------- -------- ----------- 1348 39.6 98% | fugmvwbhsq9tq 2025-04-16 09:41:42 2025-04-16 09:42:12 1348 31 1348 26 .8 2% | library cache load lock fugmvwbhsq9tq 0000000063EF4C60 2025-04-16 09:41:41 2025-04-16 09:41:41 26 1 26 1 .0 0% | db file sequential read fugmvwbhsq9tq 0000000000000009 2025-04-16 09:41:41 2025-04-16 09:41:41 1 1 1 1 .0 0% | row cache mutex 2z1aczac47jh0 0000000000000008 2025-04-16 09:41:39 2025-04-16 09:41:39 1 1 1 1 .0 0% | f0h5rpzmhju11 2025-04-16 09:41:39 2025-04-16 09:41:39 1 1 1 1 .0 0% | f8jrfxkh28gqt 2025-04-16 09:41:55 2025-04-16 09:41:55 1 1 1 1 .0 0% | 2025-04-16 09:42:12 2025-04-16 09:42:12 1 1 1 7 rows selected. --//改用1个赋值使用函数str2numlist导致的CPU使用上升。 --//如果你看ashtop的输出，感觉使用这个测试更慢，实际的情况不是这样。再次看出大量绑定变量赋值，从ashtop上很难反映出来。 --//补充测试,不使用pipe模式看看。 CREATE OR REPLACE FUNCTION str2numlist1 (p_string IN VARCHAR2) RETURN numtabletype AS v_str LONG DEFAULT p_string || ','; v_n NUMBER; 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; / $ seq 1000 | paste -sd',' >|aa.txt $ uniq -c loop9.txt 1 variable v_str varchar2(4000) 1 exec :v_str := '1,2,3,4,5,...,998,999,1000'; --// 太长，我截断了，在vim下直接读取aa.txt进来修改一些就可以了。 1 1 SELECT COUNT (data_object_id) FROM t where object_id in (select * from table (cast(str2numlist1(:v_str) as numtabletype))); 99 / 1 quit $ zzdate;seq 50 | xargs -P 50 -IQ sqlplus -s -l scott/book@book01p @loop9.txt > /dev/null;zzdate trunc(sysdate)+09/24+47/1440+44/86400 -1744768064.308475874 trunc(sysdate)+09/24+48/1440+07/86400 1744768087.904877660 --//Sum = 23.596401786 --//不使用pipe，更快。 SYS@book> @ ashtop event,sql_id,p1raw 1=1 trunc(sysdate)+09/24+47/1440+44/86400 trunc(sysdate)+09/24+48/1440+07/86400 Total Distinct Distinct Distinct Seconds AAS %This EVENT SQL_ID P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------------------------------------ ------------- ----------------- ------------------- ------------------- ---------- -------- ----------- 724 31.5 100% | cgjtcssc26b02 2025-04-16 09:47:45 2025-04-16 09:48:06 724 22 724 1 .0 0% | library cache: mutex X cgjtcssc26b02 00000000E7D376CD 2025-04-16 09:48:00 2025-04-16 09:48:00 1 1 1 --//我的测试相当于字符串拆解1000个变量，如果仅仅在100以内不会消耗这么多CPU资源的。 --//测试100个变量的情况 $ uniq -c loop9.txt 1 variable v_str varchar2(4000) 1 exec :v_str := '1,2,3,4,5,6,7,8,...,100'; 1 1 SELECT COUNT (data_object_id) FROM t where object_id in (select * from str2numlist1(:v_str) ); 99 / 1 quit $ zzdate;seq 50 | xargs -P 50 -IQ sqlplus -s -l scott/book@book01p @loop9.txt > /dev/null;zzdate trunc(sysdate)+15/24+09/1440+53/86400 -1744787393.026476043 trunc(sysdate)+15/24+09/1440+57/86400 1744787397.683731123 --//Sum = 4.65725508 --//启动数据库马上测试的情况，存在一个递归以及并发导致的library cache load lock。 SYS@book> @ ashtop event,sql_id,p1raw 1=1 trunc(sysdate)+15/24+09/1440+53/86400 trunc(sysdate)+15/24+09/1440+57/86400 Total Distinct Distinct Distinct Seconds AAS %This EVENT SQL_ID P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------------------------------------ ------------- ----------------- ------------------- ------------------- ---------- -------- ----------- 13 3.3 42% | read by other session cgjtcssc26b02 000000000000000C 2025-04-16 15:09:55 2025-04-16 15:09:55 13 1 13 7 1.8 23% | cgjtcssc26b02 2025-04-16 15:09:55 2025-04-16 15:09:56 7 2 7 4 1.0 13% | library cache load lock cgjtcssc26b02 0000000063F2A4E0 2025-04-16 15:09:55 2025-04-16 15:09:55 4 1 4 2 .5 6% | library cache load lock cgjtcssc26b02 0000000062CABC10 2025-04-16 15:09:55 2025-04-16 15:09:55 2 1 2 2 .5 6% | library cache load lock 0000000064377E40 2025-04-16 15:09:53 2025-04-16 15:09:53 1 1 1 1 .3 3% | db file sequential read 6f1z7ma144m9j 0000000000000009 2025-04-16 15:09:53 2025-04-16 15:09:53 1 1 1 1 .3 3% | db file sequential read cgjtcssc26b02 000000000000000C 2025-04-16 15:09:55 2025-04-16 15:09:55 1 1 1 1 .3 3% | library cache: mutex X cgjtcssc26b02 00000000C008D076 2025-04-16 15:09:56 2025-04-16 15:09:56 1 1 1 8 rows selected. --//第2次执行： $ zzdate;seq 50 | xargs -P 50 -IQ sqlplus -s -l scott/book@book01p @loop9.txt > /dev/null;zzdate trunc(sysdate)+15/24+12/1440+48/86400 -1744787568.021650449 trunc(sysdate)+15/24+12/1440+52/86400 1744787572.571541267 --//Sum = 4.549890818 SYS@book> @ ashtop event,sql_id,p1raw 1=1 trunc(sysdate)+15/24+12/1440+48/86400 trunc(sysdate)+15/24+12/1440+52/86400 Total Distinct Distinct Distinct Seconds AAS %This EVENT SQL_ID P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------------------------------------ ------------- ----------------- ------------------- ------------------- ---------- -------- ----------- 3 .8 75% | cgjtcssc26b02 2025-04-16 15:12:50 2025-04-16 15:12:51 3 2 3 1 .3 25% | library cache: mutex X cgjtcssc26b02 00000000E7D376CD 2025-04-16 15:12:49 2025-04-16 15:12:49 1 1 1 6.总结： --//测试有点乱。 --//绑定变量太多时的情况平时很少注意，我以前仅仅测试单次执行，自己以前一直以为差别并不是太明显。没有想到在大量重复执 --//行的情况下这部分时间不能小看，另外oracle视乎没有相关等待事件记录这种情况。 --//另外可以看出采用函数str2numlist，仅仅传1个参数的情况要明显好于多次赋值，而且没有想到pipe方式效率有点低。 --//注：我的测试相当于字符串拆解1000个变量，消耗CPU资源多属于正常的情况，如果仅仅在100以内不会消耗这么多CPU资源的。 --//顺便贴上字符串的函数，在网上应该也能找到，当然有1个限制，传入的字符串长度最大4000个字符，附在最后。 --//如果是设置假设每个数字占9个字符加上逗号分开，这样4000/10 = 400，仅仅最多接收400个参数。 --//字符串也存在类似情况。没有这个限制采用str2numlist,str2varlist效果最佳。 --//另外注意的问题采用str2numlist,str2varlist，缺省返回值行数8168,可能改变多表的连接顺序，可以通过提示控制。 --//还有就是看awr报表的问题，一些细节应该注意。 Plan hash value: 4075081508 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 286 (100)| | 1 |00:00:00.02 | 864 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 9 | | | 1 |00:00:00.02 | 864 | | | | | 2 | NESTED LOOPS | | 1 | 19 | 171 | 286 (1)| 00:00:01 | 824 |00:00:00.02 | 864 | | | | | 3 | NESTED LOOPS | | 1 | 255 | 171 | 286 (1)| 00:00:01 | 848 |00:00:00.02 | 16 | | | | | 4 | SORT UNIQUE | | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 1000 |00:00:00.02 | 0 | 48128 | 48128 |43008 (0)| | 5 | COLLECTION ITERATOR PICKLER FETCH| STR2NUMLIST1 | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 1000 |00:00:00.02 | 0 | | | | |* 6 | INDEX UNIQUE SCAN | I_T_OBJECT_ID | 1000 | 1 | | 0 (0)| | 848 |00:00:00.01 | 16 | | | | |* 7 | TABLE ACCESS BY INDEX ROWID | T | 848 | 1 | 7 | 1 (0)| 00:00:01 | 824 |00:00:00.01 | 848 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//E-Rows=8168. SELECT COUNT (data_object_id) FROM t where object_id in (select /*+ CARDINALITY("KOKBF$0"@"SEL$3" 1000) */ * from table (cast(str2numlist1(:v_str) as numtabletype))); SELECT COUNT (data_object_id) FROM t where object_id in (select /*+ CARDINALITY( tx 1000) */ * from table (cast(str2numlist1(:v_str) as numtabletype)) tx ); Plan hash value: 4075081508 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 285 (100)| | | | | | 1 | SORT AGGREGATE | | 1 | 9 | | | | | | | 2 | NESTED LOOPS | | 19 | 171 | 285 (1)| 00:00:01 | | | | | 3 | NESTED LOOPS | | 255 | 171 | 285 (1)| 00:00:01 | | | | | 4 | SORT UNIQUE | | 1000 | 2000 | 29 (0)| 00:00:01 | 48128 | 48128 |43008 (0)| | 5 | COLLECTION ITERATOR PICKLER FETCH| STR2NUMLIST1 | 1000 | 2000 | 29 (0)| 00:00:01 | | | | |* 6 | INDEX UNIQUE SCAN | I_T_OBJECT_ID | 1 | | 0 (0)| | | | | |* 7 | TABLE ACCESS BY INDEX ROWID | T | 1 | 7 | 1 (0)| 00:00:01 | | | | ----------------------------------------------------------------------------------------------------------------------------------- --//当然还可以采用临时表接收这些数据，缺点如果大量执行消耗小量redo。 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 VARCHAR2 (2000); 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; /

编辑推荐：

返回列表

[20250415]21c下测试软软解析与大量绑定变量的情况.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推