[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;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3081656/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
