[20251218]测试sql语句子光标的执行性能(21c).txt
[20251218]测试sql语句子光标的执行性能(21c).txt
--//以前做的测试在11g，别人提示在19c测试不出来，看了以前链接，感觉以前测试混乱，重新学习整理看看。
--//顺便验证当时的测试是否存在问题。
--//如果一条sql语句产生的子光标很多,除了消耗共享池内存外,也会导致执行时一些性能问题.测试看看在设置
--//session_cached_cursors=0的情况下，出现软解析，children number 0 与children number=N执行时是否存在性能差异.
--//注：如果是软软解析，应该是测试不出来区别的。
--//以前测试的帖子：
d:\notes>dir /s/b "*子光标*"| grep 202108
d:\notes\2021\202108\[20210812]测试sql语句子光标的性能.txt
d:\notes\2021\202108\[20210813]关于测试sql语句子光标的性能的一些补充.txt
d:\notes\2021\202108\[20210816]测试sql语句子光标的性能2.txt
d:\notes\2021\202108\[20210816]测试sql语句子光标的性能3.txt
d:\notes\2021\202108\[20210817]测试sql语句子光标性能遇到的问题.txt
d:\notes\2021\202108\[20210818]测试sql语句子光标性能遇到的问题2.txt
d:\notes\2021\202108\[20210818]测试sql语句子光标的性能4.txt
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.
SYS@book> @ hidez _cursor_obsolete_threshold
SYS@book> @ pr
==============================
NUM : 3719
N_HEX : E87
CON_ID : 0
NAME : _cursor_obsolete_threshold
DESCRIPTION : Number of cursors per parent before obsoletion.
DEFAULT_VALUE : TRUE
SESSION_VALUE : 8192
SYSTEM_VALUE : 8192
ISSES_MODIFIABLE : TRUE
ISSYS_MODIFIABLE : FALSE
2.建立测试脚本：
create table job_times (sid number, time_ela number,method varchar2(20));
create table t as select rownum id ,cast('test' as varchar2(10)) name from dual ;
alter table t modify ( id not null );
create unique index i_t_id on t(id);
--//分析表略。
$ cat m13.txt
--//alter session set session_cached_cursors=0;
set verify off
variable vmethod varchar2(20);
exec :vmethod := '&&2';
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
SELECT COUNT(NAME) INTO v_id FROM T WHERE ID=1;
end loop;
end ;
/
quit
--//说明：参数1表示循环次数，参数2表示method(在这里暂时没用)，参数3表示设置optimizer_index_cost_adj。
3.测试：
$ seq 128 | xargs -IQ sqlplus -s -l scott/book@book01p @m13.txt 6 xx Q > /dev/null
--//产生128个子光标.
--//每个情况执行6次,保证其在共享池中.sql_id='5zfc9hksnyp90',注意sql语句全部大写，因为PL/SQL内部做了转化为大写。
SYS@book> @ s2h 5zfc9hksnyp90
SQL_ID HASH_VALUE HASH_HEX KGL_BUCKET KGL_BUCKET_HEX
------------- ----------- --------- ---------- --------------
5zfc9hksnyp90 2974766368 b14f5520 87328 15520
--//修改m13.txt脚本，取消alter session set session_cached_cursors=0;的注解：
$ cat m13.txt
alter session set session_cached_cursors=0;
set verify off
variable vmethod varchar2(20);
exec :vmethod := '&&2';
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
SELECT COUNT(NAME) INTO v_id FROM T WHERE ID=1;
end loop;
end ;
/
quit
--//设置session_cached_cursors=0;这样每次都是软解析。
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 1 > /dev/null
real 0m13.484s
user 0m0.232s
sys 0m0.012s
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 128 > /dev/null
real 0m14.704s
user 0m0.240s
sys 0m0.013s
--//显然两者差距不是很明显。但是确实与以前的测试存在很大的不同，我以前的测试children number 0的很慢，children number 127
--//的最快，而现在却反了过来.
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 64 > /dev/null
real 0m9.641s
user 0m0.235s
sys 0m0.010s
--//中间的反而很快。
4.在11g下重复测试：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//建表以及前面的一些步骤不再贴出,仅仅贴出测试结果：
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 1 > /dev/null
real 0m13.374s
user 0m0.191s
sys 0m0.007s
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 128 > /dev/null
real 0m6.199s
user 0m0.194s
sys 0m0.004s
--//确实childnum=127的最快。
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 64 > /dev/null
real 0m9.844s
user 0m0.192s
sys 0m0.014s
--//我当时的结论就是oracle 11g在查询合适子光标时从最新的子光标开始探查，如何合适采用该执行计划。这样children number 0最
--//后探查，选择children number 0的执行计划时最慢的，而optimizer_index_cost_adj = 128，马上探查到，因此执行最快。
--//而且实际上按照CursorDiagnosticsNodes的顺序来探查(注：当时仅仅是猜测)
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump library_cache 10
Statement processed.
--//查看转储文件：
...
CursorDiagnosticsNodes:
ChildNode: ChildNumber=127 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 128 1
ChildNode: ChildNumber=126 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 127 128
ChildNode: ChildNumber=125 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 126 127
ChildNode: ChildNumber=124 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 125 126
....
ChildNode: ChildNumber=74 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 75 76
....
ChildNode: ChildNumber=8 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 9 10
ChildNode: ChildNumber=7 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 8 9
ChildNode: ChildNumber=6 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 7 8
ChildNode: ChildNumber=5 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 6 7
ChildNode: ChildNumber=4 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 5 6
ChildNode: ChildNumber=3 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 4 5
ChildNode: ChildNumber=2 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 3 4
ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 2 3
ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 1 2
--//当前确实没有验证自己的判断，其实也很容易验证：
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> /
System altered.
SYS@book> @ sharepool/shp4 5zfc9hksnyp90 -1
no rows selected
$ shuf -i 1-128 | xargs -IQ sqlplus -s -l scott/book@book01p @m13.txt 6 xx Q > /dev/null
--//这样整个子光标对应optimizer_index_cost_adj的值是乱的。
SYS@book> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_5203_0002.trc
SYS@book> oradebug dump library_cache 10
Statement processed.
--//查看转储文件：
NamespaceDump:
Parent Cursor: sql_id=5zfc9hksnyp90 parent=0x896e4150 maxchild=128 plk=n ppn=n
CursorDiagnosticsNodes:
ChildNode: ChildNumber=126 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 70 53
ChildNode: ChildNumber=125 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 106 70
ChildNode: ChildNumber=124 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 119 106
ChildNode: ChildNumber=123 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 94 119
ChildNode: ChildNumber=122 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 126 94
ChildNode: ChildNumber=121 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 33 126
ChildNode: ChildNumber=120 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 79 33
ChildNode: ChildNumber=119 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 99 79
...
ChildNode: ChildNumber=4 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43 124
ChildNode: ChildNumber=3 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 64 43
ChildNode: ChildNumber=2 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 120 64
ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 26 120
ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 65 26
--//这样最快是optimizer_index_cost_adj=70,奇怪仅仅看到127个子光标,查询不到optimizer_index_cost_adj=53,没有像前面形成1个
--//环，应该optimizer_index_cost_adj=53的子光标的执行最快。
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 70 > /dev/null
real 0m6.667s
user 0m0.195s
sys 0m0.006s
--//最慢是optimizer_index_cost_adj=65
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 65 > /dev/null
real 0m12.753s
user 0m0.188s
sys 0m0.010s
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 53 > /dev/null
real 0m6.208s
user 0m0.192s
sys 0m0.011s
--//该测试验证我的判断。
5.回到21c为什么出现这样的情况呢？
$ seq 10 10 120 | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q" > /dev/null
real 0m12.289s
user 0m0.241s
sys 0m0.011s
real 0m11.869s
user 0m0.205s
sys 0m0.012s
real 0m11.320s
user 0m0.259s
sys 0m0.018s
real 0m11.146s
user 0m0.243s
sys 0m0.014s
real 0m10.056s
user 0m0.240s
sys 0m0.016s
real 0m9.520s
user 0m0.245s
sys 0m0.013s
real 0m9.001s
user 0m0.213s
sys 0m0.017s
real 0m8.086s
user 0m0.215s
sys 0m0.015s
real 0m7.884s
user 0m0.261s
sys 0m0.010s
real 0m7.274s --//100,对应的child_num=99.因为child_num从0开始。
user 0m0.245s
sys 0m0.010s
real 0m14.090s --//110
user 0m0.254s
sys 0m0.014s
real 0m14.009s
user 0m0.242s
sys 0m0.011s
--//100到110出现跳跃。
$ seq 100 1 110 | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q 2>&1" > /dev/null | grep real
--//最后的grep不起作用,该问题先放一放。
real 0m7.173s --//100
user 0m0.237s
sys 0m0.013s
real 0m13.097s --//101
user 0m0.245s
sys 0m0.007s
real 0m13.129s
user 0m0.251s
sys 0m0.004s
real 0m13.714s
user 0m0.238s
sys 0m0.009s
real 0m13.674s
user 0m0.218s
sys 0m0.011s
real 0m13.796s
user 0m0.217s
sys 0m0.011s
real 0m13.426s
user 0m0.207s
sys 0m0.014s
real 0m13.191s
user 0m0.210s
sys 0m0.010s
real 0m13.495s
user 0m0.253s
sys 0m0.006s
real 0m13.871s
user 0m0.249s
sys 0m0.009s
real 0m13.618s
user 0m0.219s
sys 0m0.017s
--//100与101的测试差距巨大。
$ seq 101 128 | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q 2>&1" > /dev/null | grep real
real 0m13.230s
user 0m0.240s
sys 0m0.013s
real 0m13.352s
user 0m0.235s
sys 0m0.010s
real 0m13.464s
user 0m0.255s
sys 0m0.013s
real 0m13.965s
user 0m0.202s
sys 0m0.008s
real 0m13.295s
user 0m0.212s
sys 0m0.006s
real 0m13.308s
user 0m0.217s
sys 0m0.010s
real 0m13.473s
user 0m0.251s
sys 0m0.011s
real 0m13.473s
user 0m0.250s
sys 0m0.010s
....
real 0m14.362s
user 0m0.254s
sys 0m0.014s
real 0m14.147s
user 0m0.201s
sys 0m0.010s
real 0m14.136s
user 0m0.201s
sys 0m0.008s
real 0m14.375s
user 0m0.253s
sys 0m0.016s
real 0m15.316s
user 0m0.249s
sys 0m0.015s
real 0m14.224s
user 0m0.243s
sys 0m0.008s
real 0m14.389s
user 0m0.249s
sys 0m0.016s
--//相邻的测试存在一些小误差，不过还是可以看出探查的规律，从ChildNumber=99作为分界点开始向ChildNumber=0探查，然后再从
--//ChildNumber=100->ChildNumber=127.
--//这样ChildNumber=99最快，
--//至于出现更多子光标会出现什么情况，就不是很清楚了，还给继续测试....
--//注解alter session set session_cached_cursors=0;，如下写法可以过滤real的信息。
$ seq 100 1 104 | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q 2>&1 > /dev/null" 2>&1 | grep real
real 0m3.434s
real 0m3.439s
real 0m3.603s
real 0m3.508s
real 0m3.287s
--//可以发现软软解析，就看不到前面的情况了。每次仅仅需要3.X秒完成。
6.另外测试了产生1000个子光标的情况下,直接贴出测试结果，情况应该与前面的测试类似。
$ cat m13b.txt
alter session set session_cached_cursors=0;
set verify off
variable vmethod varchar2(20);
exec :vmethod := '&&2';
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
SELECT /*+ &&2 */ COUNT(*) into v_id FROM T WHERE ID=1;
end loop;
end ;
/
--//quit
--//注：注解最后一行的quit。
--//产生1000个子光标，每个情况执行5次，尽可能保存在共享池。
$ sqlplus -s -l scott/book@book01p <<EOF > /dev/null
$(seq 1000 | xargs -IQ echo @m13b.txt 5 e Q)
quit
EOF
SYS@book> select count(*) from v$sql where sql_id='622knv3914c8h';
COUNT(*)
----------
1000
--//修改m13b.txt，取消最后一行注解。
$ cat m13b.txt
alter session set session_cached_cursors=0;
set verify off
variable vmethod varchar2(20);
exec :vmethod := '&&2';
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
SELECT /*+ &&2 */ COUNT(*) into v_id FROM T WHERE ID=1;
end loop;
end ;
/
quit
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 1 > /dev/null
real 0m6.713s
user 0m0.265s
sys 0m0.011s
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 99 > /dev/null
real 0m3.411s
user 0m0.236s
sys 0m0.007s
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 100 > /dev/null
real 0m3.505s
user 0m0.221s
sys 0m0.009s
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 101 > /dev/null
real 0m6.220s
user 0m0.241s
sys 0m0.008s
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 1000 > /dev/null
real 0m32.869s
user 0m0.254s
sys 0m0.009s
--//完全符合我前面测试的规律。
7.补充：
--//另外仔细看了以前的测试笔记，在11g下也遇到中间的某个子光标查询变快的情况，发现该子光标刷出共享池。
--//换一句话讲，前面看到的"规律"仅仅是全部子光标都在的情况下才正确。其探查按照转储library_cache中CursorDiagnosticsNodes
--//显示顺序相关.
--//顺便贴上当时在11g测试遇到的情况，以下是library_cache转储该语句的相关部分：
--//ChildNumber=19到ChildNumber=50 被刷出共享池，执行时又回来，但是顺序与以前不同。这样执行计划选择
--//optimizer_index_cost_adj= 48突然变快了.
--//如果按照这个推测，21c应该按照CursorDiagnosticsNodes的顺序，从最底下100行对应的子光标开始探查，探查到chile_number=0,
--//然后从101行对应的子光标探查，到最后的子光标。
--//当然这些都是我的猜测，对不对不知道。
--//以下是当时library_cache转储该语句的相关部分：
NamespaceDump:
Parent Cursor: sql_id=5zfc9hksnyp90 parent=0x7c5502d0 maxchild=128 plk=n ppn=n
CursorDiagnosticsNodes:
ChildNode: ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51 1
ChildNode: ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 50 1
ChildNode: ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 49 1
ChildNode: ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 48 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ChildNode: ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 47 1
ChildNode: ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 46 1
ChildNode: ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 45 1
ChildNode: ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 44 1
ChildNode: ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43 1
ChildNode: ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 42 1
ChildNode: ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 40 1
ChildNode: ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 39 1
ChildNode: ChildNumber=40 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 41 1
ChildNode: ChildNumber=37 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 38 1
ChildNode: ChildNumber=36 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 37 1
ChildNode: ChildNumber=35 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 36 1
ChildNode: ChildNumber=34 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 35 1
ChildNode: ChildNumber=28 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 29 1
ChildNode: ChildNumber=27 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 28 1
ChildNode: ChildNumber=26 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 27 1
ChildNode: ChildNumber=25 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 26 1
ChildNode: ChildNumber=24 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 25 1
ChildNode: ChildNumber=23 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 24 1
ChildNode: ChildNumber=22 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 23 1
ChildNode: ChildNumber=21 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 22 1
ChildNode: ChildNumber=20 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 21 1
ChildNode: ChildNumber=19 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 20 1
ChildNode: ChildNumber=127 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 128 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ChildNode: ChildNumber=126 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 127 128
ChildNode: ChildNumber=125 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 126 127
ChildNode: ChildNumber=124 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 125 126
ChildNode: ChildNumber=123 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 124 125
ChildNode: ChildNumber=122 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 123 124
ChildNode: ChildNumber=121 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 122 123
ChildNode: ChildNumber=120 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 121 122
ChildNode: ChildNumber=119 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 120 121
ChildNode: ChildNumber=118 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 119 120
ChildNode: ChildNumber=117 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 118 119
ChildNode: ChildNumber=116 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 117 118
ChildNode: ChildNumber=115 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 116 117
ChildNode: ChildNumber=114 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 115 116
ChildNode: ChildNumber=113 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 114 115
ChildNode: ChildNumber=112 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 113 114
ChildNode: ChildNumber=111 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 112 113
ChildNode: ChildNumber=110 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 111 112
ChildNode: ChildNumber=109 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 110 111
ChildNode: ChildNumber=108 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 109 110
ChildNode: ChildNumber=107 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 108 109
ChildNode: ChildNumber=106 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 107 108
ChildNode: ChildNumber=105 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 106 107
ChildNode: ChildNumber=104 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 105 106
ChildNode: ChildNumber=103 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 104 105
ChildNode: ChildNumber=102 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 103 104
ChildNode: ChildNumber=101 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 102 103
ChildNode: ChildNumber=100 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 101 102
....
ChildNode: ChildNumber=52 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 53 54
ChildNode: ChildNumber=51 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 52 53
ChildNode: ChildNumber=33 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 34 35
ChildNode: ChildNumber=32 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 33 34
ChildNode: ChildNumber=31 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 32 33
.....
ChildNode: ChildNumber=8 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 9 10
ChildNode: ChildNumber=7 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 8 9
ChildNode: ChildNumber=6 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 7 8
ChildNode: ChildNumber=5 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 6 7
ChildNode: ChildNumber=4 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 5 6
ChildNode: ChildNumber=3 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 4 5
ChildNode: ChildNumber=2 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 3 4
ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 2 3
ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 1 2
AgedOutCursorDiagnosticNodes:
ChildNode: ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51 1
ChildNode: ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 50 1
ChildNode: ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 49 1
ChildNode: ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 48 1
ChildNode: ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 47 1
ChildNode: ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 46 1
ChildNode: ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 45 1
ChildNode: ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 44 1
ChildNode: ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43 1
ChildNode: ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 42 1
ChildNode: ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 40 1
ChildNode: ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 39 1
ChildNode: ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51 52
ChildNode: ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 50 51
ChildNode: ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 49 50
ChildNode: ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 48 49
ChildNode: ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 47 48
ChildNode: ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 46 47
ChildNode: ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 45 46
ChildNode: ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 44 45
ChildNode: ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43 44
ChildNode: ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 42 43
ChildNode: ChildNumber=40 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 41 42
ChildNode: ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 40 41
ChildNode: ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 39 40
ChildNode: ChildNumber=37 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 38 39
ChildNode: ChildNumber=36 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 37 38
ChildNode: ChildNumber=35 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 36 37
ChildNode: ChildNumber=34 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 35 36
ChildNode: ChildNumber=28 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 29 30
ChildNode: ChildNumber=27 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 28 29
ChildNode: ChildNumber=26 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 27 28
ChildNode: ChildNumber=25 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 26 27
ChildNode: ChildNumber=24 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 25 26
ChildNode: ChildNumber=23 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 24 25
ChildNode: ChildNumber=22 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 23 24
ChildNode: ChildNumber=21 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 22 23
ChildNode: ChildNumber=20 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 21 22
ChildNode: ChildNumber=19 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 20 21
--//以前做的测试在11g，别人提示在19c测试不出来，看了以前链接，感觉以前测试混乱，重新学习整理看看。
--//顺便验证当时的测试是否存在问题。
--//如果一条sql语句产生的子光标很多,除了消耗共享池内存外,也会导致执行时一些性能问题.测试看看在设置
--//session_cached_cursors=0的情况下，出现软解析，children number 0 与children number=N执行时是否存在性能差异.
--//注：如果是软软解析，应该是测试不出来区别的。
--//以前测试的帖子：
d:\notes>dir /s/b "*子光标*"| grep 202108
d:\notes\2021\202108\[20210812]测试sql语句子光标的性能.txt
d:\notes\2021\202108\[20210813]关于测试sql语句子光标的性能的一些补充.txt
d:\notes\2021\202108\[20210816]测试sql语句子光标的性能2.txt
d:\notes\2021\202108\[20210816]测试sql语句子光标的性能3.txt
d:\notes\2021\202108\[20210817]测试sql语句子光标性能遇到的问题.txt
d:\notes\2021\202108\[20210818]测试sql语句子光标性能遇到的问题2.txt
d:\notes\2021\202108\[20210818]测试sql语句子光标的性能4.txt
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.
SYS@book> @ hidez _cursor_obsolete_threshold
SYS@book> @ pr
==============================
NUM : 3719
N_HEX : E87
CON_ID : 0
NAME : _cursor_obsolete_threshold
DESCRIPTION : Number of cursors per parent before obsoletion.
DEFAULT_VALUE : TRUE
SESSION_VALUE : 8192
SYSTEM_VALUE : 8192
ISSES_MODIFIABLE : TRUE
ISSYS_MODIFIABLE : FALSE
2.建立测试脚本：
create table job_times (sid number, time_ela number,method varchar2(20));
create table t as select rownum id ,cast('test' as varchar2(10)) name from dual ;
alter table t modify ( id not null );
create unique index i_t_id on t(id);
--//分析表略。
$ cat m13.txt
--//alter session set session_cached_cursors=0;
set verify off
variable vmethod varchar2(20);
exec :vmethod := '&&2';
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
SELECT COUNT(NAME) INTO v_id FROM T WHERE ID=1;
end loop;
end ;
/
quit
--//说明：参数1表示循环次数，参数2表示method(在这里暂时没用)，参数3表示设置optimizer_index_cost_adj。
3.测试：
$ seq 128 | xargs -IQ sqlplus -s -l scott/book@book01p @m13.txt 6 xx Q > /dev/null
--//产生128个子光标.
--//每个情况执行6次,保证其在共享池中.sql_id='5zfc9hksnyp90',注意sql语句全部大写，因为PL/SQL内部做了转化为大写。
SYS@book> @ s2h 5zfc9hksnyp90
SQL_ID HASH_VALUE HASH_HEX KGL_BUCKET KGL_BUCKET_HEX
------------- ----------- --------- ---------- --------------
5zfc9hksnyp90 2974766368 b14f5520 87328 15520
--//修改m13.txt脚本，取消alter session set session_cached_cursors=0;的注解：
$ cat m13.txt
alter session set session_cached_cursors=0;
set verify off
variable vmethod varchar2(20);
exec :vmethod := '&&2';
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
SELECT COUNT(NAME) INTO v_id FROM T WHERE ID=1;
end loop;
end ;
/
quit
--//设置session_cached_cursors=0;这样每次都是软解析。
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 1 > /dev/null
real 0m13.484s
user 0m0.232s
sys 0m0.012s
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 128 > /dev/null
real 0m14.704s
user 0m0.240s
sys 0m0.013s
--//显然两者差距不是很明显。但是确实与以前的测试存在很大的不同，我以前的测试children number 0的很慢，children number 127
--//的最快，而现在却反了过来.
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 64 > /dev/null
real 0m9.641s
user 0m0.235s
sys 0m0.010s
--//中间的反而很快。
4.在11g下重复测试：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//建表以及前面的一些步骤不再贴出,仅仅贴出测试结果：
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 1 > /dev/null
real 0m13.374s
user 0m0.191s
sys 0m0.007s
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 128 > /dev/null
real 0m6.199s
user 0m0.194s
sys 0m0.004s
--//确实childnum=127的最快。
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 64 > /dev/null
real 0m9.844s
user 0m0.192s
sys 0m0.014s
--//我当时的结论就是oracle 11g在查询合适子光标时从最新的子光标开始探查，如何合适采用该执行计划。这样children number 0最
--//后探查，选择children number 0的执行计划时最慢的，而optimizer_index_cost_adj = 128，马上探查到，因此执行最快。
--//而且实际上按照CursorDiagnosticsNodes的顺序来探查(注：当时仅仅是猜测)
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump library_cache 10
Statement processed.
--//查看转储文件：
...
CursorDiagnosticsNodes:
ChildNode: ChildNumber=127 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 128 1
ChildNode: ChildNumber=126 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 127 128
ChildNode: ChildNumber=125 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 126 127
ChildNode: ChildNumber=124 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 125 126
....
ChildNode: ChildNumber=74 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 75 76
....
ChildNode: ChildNumber=8 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 9 10
ChildNode: ChildNumber=7 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 8 9
ChildNode: ChildNumber=6 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 7 8
ChildNode: ChildNumber=5 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 6 7
ChildNode: ChildNumber=4 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 5 6
ChildNode: ChildNumber=3 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 4 5
ChildNode: ChildNumber=2 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 3 4
ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 2 3
ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 1 2
--//当前确实没有验证自己的判断，其实也很容易验证：
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> /
System altered.
SYS@book> @ sharepool/shp4 5zfc9hksnyp90 -1
no rows selected
$ shuf -i 1-128 | xargs -IQ sqlplus -s -l scott/book@book01p @m13.txt 6 xx Q > /dev/null
--//这样整个子光标对应optimizer_index_cost_adj的值是乱的。
SYS@book> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_5203_0002.trc
SYS@book> oradebug dump library_cache 10
Statement processed.
--//查看转储文件：
NamespaceDump:
Parent Cursor: sql_id=5zfc9hksnyp90 parent=0x896e4150 maxchild=128 plk=n ppn=n
CursorDiagnosticsNodes:
ChildNode: ChildNumber=126 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 70 53
ChildNode: ChildNumber=125 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 106 70
ChildNode: ChildNumber=124 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 119 106
ChildNode: ChildNumber=123 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 94 119
ChildNode: ChildNumber=122 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 126 94
ChildNode: ChildNumber=121 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 33 126
ChildNode: ChildNumber=120 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 79 33
ChildNode: ChildNumber=119 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 99 79
...
ChildNode: ChildNumber=4 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43 124
ChildNode: ChildNumber=3 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 64 43
ChildNode: ChildNumber=2 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 120 64
ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 26 120
ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 65 26
--//这样最快是optimizer_index_cost_adj=70,奇怪仅仅看到127个子光标,查询不到optimizer_index_cost_adj=53,没有像前面形成1个
--//环，应该optimizer_index_cost_adj=53的子光标的执行最快。
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 70 > /dev/null
real 0m6.667s
user 0m0.195s
sys 0m0.006s
--//最慢是optimizer_index_cost_adj=65
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 65 > /dev/null
real 0m12.753s
user 0m0.188s
sys 0m0.010s
$ time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx 53 > /dev/null
real 0m6.208s
user 0m0.192s
sys 0m0.011s
--//该测试验证我的判断。
5.回到21c为什么出现这样的情况呢？
$ seq 10 10 120 | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q" > /dev/null
real 0m12.289s
user 0m0.241s
sys 0m0.011s
real 0m11.869s
user 0m0.205s
sys 0m0.012s
real 0m11.320s
user 0m0.259s
sys 0m0.018s
real 0m11.146s
user 0m0.243s
sys 0m0.014s
real 0m10.056s
user 0m0.240s
sys 0m0.016s
real 0m9.520s
user 0m0.245s
sys 0m0.013s
real 0m9.001s
user 0m0.213s
sys 0m0.017s
real 0m8.086s
user 0m0.215s
sys 0m0.015s
real 0m7.884s
user 0m0.261s
sys 0m0.010s
real 0m7.274s --//100,对应的child_num=99.因为child_num从0开始。
user 0m0.245s
sys 0m0.010s
real 0m14.090s --//110
user 0m0.254s
sys 0m0.014s
real 0m14.009s
user 0m0.242s
sys 0m0.011s
--//100到110出现跳跃。
$ seq 100 1 110 | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q 2>&1" > /dev/null | grep real
--//最后的grep不起作用,该问题先放一放。
real 0m7.173s --//100
user 0m0.237s
sys 0m0.013s
real 0m13.097s --//101
user 0m0.245s
sys 0m0.007s
real 0m13.129s
user 0m0.251s
sys 0m0.004s
real 0m13.714s
user 0m0.238s
sys 0m0.009s
real 0m13.674s
user 0m0.218s
sys 0m0.011s
real 0m13.796s
user 0m0.217s
sys 0m0.011s
real 0m13.426s
user 0m0.207s
sys 0m0.014s
real 0m13.191s
user 0m0.210s
sys 0m0.010s
real 0m13.495s
user 0m0.253s
sys 0m0.006s
real 0m13.871s
user 0m0.249s
sys 0m0.009s
real 0m13.618s
user 0m0.219s
sys 0m0.017s
--//100与101的测试差距巨大。
$ seq 101 128 | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q 2>&1" > /dev/null | grep real
real 0m13.230s
user 0m0.240s
sys 0m0.013s
real 0m13.352s
user 0m0.235s
sys 0m0.010s
real 0m13.464s
user 0m0.255s
sys 0m0.013s
real 0m13.965s
user 0m0.202s
sys 0m0.008s
real 0m13.295s
user 0m0.212s
sys 0m0.006s
real 0m13.308s
user 0m0.217s
sys 0m0.010s
real 0m13.473s
user 0m0.251s
sys 0m0.011s
real 0m13.473s
user 0m0.250s
sys 0m0.010s
....
real 0m14.362s
user 0m0.254s
sys 0m0.014s
real 0m14.147s
user 0m0.201s
sys 0m0.010s
real 0m14.136s
user 0m0.201s
sys 0m0.008s
real 0m14.375s
user 0m0.253s
sys 0m0.016s
real 0m15.316s
user 0m0.249s
sys 0m0.015s
real 0m14.224s
user 0m0.243s
sys 0m0.008s
real 0m14.389s
user 0m0.249s
sys 0m0.016s
--//相邻的测试存在一些小误差，不过还是可以看出探查的规律，从ChildNumber=99作为分界点开始向ChildNumber=0探查，然后再从
--//ChildNumber=100->ChildNumber=127.
--//这样ChildNumber=99最快，
--//至于出现更多子光标会出现什么情况，就不是很清楚了，还给继续测试....
--//注解alter session set session_cached_cursors=0;，如下写法可以过滤real的信息。
$ seq 100 1 104 | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q 2>&1 > /dev/null" 2>&1 | grep real
real 0m3.434s
real 0m3.439s
real 0m3.603s
real 0m3.508s
real 0m3.287s
--//可以发现软软解析，就看不到前面的情况了。每次仅仅需要3.X秒完成。
6.另外测试了产生1000个子光标的情况下,直接贴出测试结果，情况应该与前面的测试类似。
$ cat m13b.txt
alter session set session_cached_cursors=0;
set verify off
variable vmethod varchar2(20);
exec :vmethod := '&&2';
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
SELECT /*+ &&2 */ COUNT(*) into v_id FROM T WHERE ID=1;
end loop;
end ;
/
--//quit
--//注：注解最后一行的quit。
--//产生1000个子光标，每个情况执行5次，尽可能保存在共享池。
$ sqlplus -s -l scott/book@book01p <<EOF > /dev/null
$(seq 1000 | xargs -IQ echo @m13b.txt 5 e Q)
quit
EOF
SYS@book> select count(*) from v$sql where sql_id='622knv3914c8h';
COUNT(*)
----------
1000
--//修改m13b.txt，取消最后一行注解。
$ cat m13b.txt
alter session set session_cached_cursors=0;
set verify off
variable vmethod varchar2(20);
exec :vmethod := '&&2';
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
SELECT /*+ &&2 */ COUNT(*) into v_id FROM T WHERE ID=1;
end loop;
end ;
/
quit
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 1 > /dev/null
real 0m6.713s
user 0m0.265s
sys 0m0.011s
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 99 > /dev/null
real 0m3.411s
user 0m0.236s
sys 0m0.007s
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 100 > /dev/null
real 0m3.505s
user 0m0.221s
sys 0m0.009s
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 101 > /dev/null
real 0m6.220s
user 0m0.241s
sys 0m0.008s
$ time sqlplus -s -l scott/book@book01p @m13b.txt 1e5 e 1000 > /dev/null
real 0m32.869s
user 0m0.254s
sys 0m0.009s
--//完全符合我前面测试的规律。
7.补充：
--//另外仔细看了以前的测试笔记，在11g下也遇到中间的某个子光标查询变快的情况，发现该子光标刷出共享池。
--//换一句话讲，前面看到的"规律"仅仅是全部子光标都在的情况下才正确。其探查按照转储library_cache中CursorDiagnosticsNodes
--//显示顺序相关.
--//顺便贴上当时在11g测试遇到的情况，以下是library_cache转储该语句的相关部分：
--//ChildNumber=19到ChildNumber=50 被刷出共享池，执行时又回来，但是顺序与以前不同。这样执行计划选择
--//optimizer_index_cost_adj= 48突然变快了.
--//如果按照这个推测，21c应该按照CursorDiagnosticsNodes的顺序，从最底下100行对应的子光标开始探查，探查到chile_number=0,
--//然后从101行对应的子光标探查，到最后的子光标。
--//当然这些都是我的猜测，对不对不知道。
--//以下是当时library_cache转储该语句的相关部分：
NamespaceDump:
Parent Cursor: sql_id=5zfc9hksnyp90 parent=0x7c5502d0 maxchild=128 plk=n ppn=n
CursorDiagnosticsNodes:
ChildNode: ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51 1
ChildNode: ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 50 1
ChildNode: ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 49 1
ChildNode: ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 48 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ChildNode: ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 47 1
ChildNode: ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 46 1
ChildNode: ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 45 1
ChildNode: ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 44 1
ChildNode: ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43 1
ChildNode: ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 42 1
ChildNode: ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 40 1
ChildNode: ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 39 1
ChildNode: ChildNumber=40 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 41 1
ChildNode: ChildNumber=37 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 38 1
ChildNode: ChildNumber=36 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 37 1
ChildNode: ChildNumber=35 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 36 1
ChildNode: ChildNumber=34 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 35 1
ChildNode: ChildNumber=28 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 29 1
ChildNode: ChildNumber=27 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 28 1
ChildNode: ChildNumber=26 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 27 1
ChildNode: ChildNumber=25 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 26 1
ChildNode: ChildNumber=24 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 25 1
ChildNode: ChildNumber=23 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 24 1
ChildNode: ChildNumber=22 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 23 1
ChildNode: ChildNumber=21 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 22 1
ChildNode: ChildNumber=20 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 21 1
ChildNode: ChildNumber=19 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 20 1
ChildNode: ChildNumber=127 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 128 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ChildNode: ChildNumber=126 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 127 128
ChildNode: ChildNumber=125 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 126 127
ChildNode: ChildNumber=124 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 125 126
ChildNode: ChildNumber=123 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 124 125
ChildNode: ChildNumber=122 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 123 124
ChildNode: ChildNumber=121 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 122 123
ChildNode: ChildNumber=120 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 121 122
ChildNode: ChildNumber=119 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 120 121
ChildNode: ChildNumber=118 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 119 120
ChildNode: ChildNumber=117 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 118 119
ChildNode: ChildNumber=116 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 117 118
ChildNode: ChildNumber=115 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 116 117
ChildNode: ChildNumber=114 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 115 116
ChildNode: ChildNumber=113 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 114 115
ChildNode: ChildNumber=112 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 113 114
ChildNode: ChildNumber=111 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 112 113
ChildNode: ChildNumber=110 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 111 112
ChildNode: ChildNumber=109 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 110 111
ChildNode: ChildNumber=108 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 109 110
ChildNode: ChildNumber=107 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 108 109
ChildNode: ChildNumber=106 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 107 108
ChildNode: ChildNumber=105 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 106 107
ChildNode: ChildNumber=104 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 105 106
ChildNode: ChildNumber=103 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 104 105
ChildNode: ChildNumber=102 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 103 104
ChildNode: ChildNumber=101 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 102 103
ChildNode: ChildNumber=100 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 101 102
....
ChildNode: ChildNumber=52 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 53 54
ChildNode: ChildNumber=51 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 52 53
ChildNode: ChildNumber=33 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 34 35
ChildNode: ChildNumber=32 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 33 34
ChildNode: ChildNumber=31 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 32 33
.....
ChildNode: ChildNumber=8 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 9 10
ChildNode: ChildNumber=7 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 8 9
ChildNode: ChildNumber=6 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 7 8
ChildNode: ChildNumber=5 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 6 7
ChildNode: ChildNumber=4 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 5 6
ChildNode: ChildNumber=3 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 4 5
ChildNode: ChildNumber=2 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 3 4
ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 2 3
ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 1 2
AgedOutCursorDiagnosticNodes:
ChildNode: ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51 1
ChildNode: ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 50 1
ChildNode: ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 49 1
ChildNode: ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 48 1
ChildNode: ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 47 1
ChildNode: ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 46 1
ChildNode: ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 45 1
ChildNode: ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 44 1
ChildNode: ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43 1
ChildNode: ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 42 1
ChildNode: ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 40 1
ChildNode: ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 39 1
ChildNode: ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51 52
ChildNode: ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 50 51
ChildNode: ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 49 50
ChildNode: ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 48 49
ChildNode: ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 47 48
ChildNode: ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 46 47
ChildNode: ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 45 46
ChildNode: ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 44 45
ChildNode: ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43 44
ChildNode: ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 42 43
ChildNode: ChildNumber=40 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 41 42
ChildNode: ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 40 41
ChildNode: ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 39 40
ChildNode: ChildNumber=37 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 38 39
ChildNode: ChildNumber=36 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 37 38
ChildNode: ChildNumber=35 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 36 37
ChildNode: ChildNumber=34 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 35 36
ChildNode: ChildNumber=28 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 29 30
ChildNode: ChildNumber=27 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 28 29
ChildNode: ChildNumber=26 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 27 28
ChildNode: ChildNumber=25 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 26 27
ChildNode: ChildNumber=24 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 25 26
ChildNode: ChildNumber=23 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 24 25
ChildNode: ChildNumber=22 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 23 24
ChildNode: ChildNumber=21 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 22 23
ChildNode: ChildNumber=20 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 21 22
ChildNode: ChildNumber=19 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 20 21
浙公网安备 33010602011771号