[20251219]测试sql语句子光标的执行性能2(21c).txt
[20251219]测试sql语句子光标的执行性能2(21c).txt
--//昨天下午测试在21c下sql语句子光标的执行性能，当时得出的规律如下：
--//建立128个子光标，oracle软软解析探查采用那个子光标的执行计划，是以ChildNumber=99作为分界点开始向ChildNumber=0探查，然
--//后再从ChildNumber=100->ChildNumber=127.
--//这样的探查方式，选择ChildNumber=99的语句执行最快，而选择ChildNumber=127的执行最慢。而以前11g的测试是选择
--//ChildNumber=127的执行最快，ChildNumber=0的最慢
--//当时已经很晚了，回家路上想oracle做出这样有它自己的考虑，毕竟超过100个子光标的情况很少见，如果大于100说明该语句遇到
--//bug或者什么问题，而且许多情况下一些子光标会不再使用，这样新产生的子光标多数情况下也不会大于100.
--//另外仔细看了以前的测试笔记，也遇到中间的某个子光标查询变快的情况，发现该子光标刷出共享池，实际情况仅仅刷出子光标的堆
--//0以及堆6，再次执行建立子光标堆0以及堆6.
--//换一句话讲，前面看到的"规律"仅仅是全部子光标都在的情况下才正确。其探查按照转储library_cache中CursorDiagnosticsNodes
--//显示顺序相关.
--//还是先验证我前面看到的情况是否正确。
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);
--//分析表略。
--//产生128个子光标.
$ 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。
$ shuf -i 1-128 > shuf.txt
--//建立随机显示1-128的数字列表。
$ awk 'NR ==1 || NR == 100 || NR==128 {print $0}' shuf.txt
83
30
43
$ cat shuf.txt | xargs -IQ sqlplus -s -l scott/book@book01p @m13.txt 6 xx Q > /dev/null
--//每个情况执行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
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump library_cache 8
Statement processed.
$ sed -n "124,128p" shuf.txt
1
105
119
68
43
--//查看转储：
NamespaceDump:
Parent Cursor: sql_id=5zfc9hksnyp90 parent=0x6eb59290 maxchild=128 plk=n ppn=n prsfcnt=0 obscnt=0
CursorDiagnosticsNodes:
ChildNode: ChildNumber=126 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 68 43
ChildNode: ChildNumber=125 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 119 68
ChildNode: ChildNumber=124 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 105 119
ChildNode: ChildNumber=123 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 1 105
ChildNode: ChildNumber=122 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 124 1
ChildNode: ChildNumber=121 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 36 124
ChildNode: ChildNumber=120 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 40 36
ChildNode: ChildNumber=119 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 22 40
ChildNode: ChildNumber=118 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 116 22
ChildNode: ChildNumber=117 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 55 116
ChildNode: ChildNumber=116 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 31 55
ChildNode: ChildNumber=115 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 54 31
ChildNode: ChildNumber=114 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 113 54
ChildNode: ChildNumber=113 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 84 113
ChildNode: ChildNumber=112 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 74 84
ChildNode: ChildNumber=111 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 53 74
ChildNode: ChildNumber=110 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 12 53
ChildNode: ChildNumber=109 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 27 12
ChildNode: ChildNumber=108 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 63 27
ChildNode: ChildNumber=107 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 47 63
ChildNode: ChildNumber=106 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 13 47
ChildNode: ChildNumber=105 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 35 13
ChildNode: ChildNumber=104 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 98 35
ChildNode: ChildNumber=103 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 44 98
ChildNode: ChildNumber=102 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 78 44
ChildNode: ChildNumber=101 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 71 78
ChildNode: ChildNumber=100 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 29 71
ChildNode: ChildNumber=99 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 30 29
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ChildNode: ChildNumber=98 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 16 30
ChildNode: ChildNumber=97 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 61 16
ChildNode: ChildNumber=96 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 5 61
ChildNode: ChildNumber=95 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 25 5
ChildNode: ChildNumber=94 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 59 25
ChildNode: ChildNumber=93 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 93 59
ChildNode: ChildNumber=92 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 114 93
ChildNode: ChildNumber=91 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 79 114
ChildNode: ChildNumber=90 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 117 79
ChildNode: ChildNumber=89 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 89 117
ChildNode: ChildNumber=88 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 111 89
ChildNode: ChildNumber=87 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 77 111
ChildNode: ChildNumber=86 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 45 77
ChildNode: ChildNumber=85 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 107 45
ChildNode: ChildNumber=84 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 21 107
ChildNode: ChildNumber=83 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 120 21
ChildNode: ChildNumber=82 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 28 120
ChildNode: ChildNumber=81 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 86 28
ChildNode: ChildNumber=80 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 17 86
ChildNode: ChildNumber=79 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 67 17
ChildNode: ChildNumber=78 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 127 67
ChildNode: ChildNumber=77 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 101 127
ChildNode: ChildNumber=76 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 33 101
ChildNode: ChildNumber=75 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 58 33
ChildNode: ChildNumber=74 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 4 58
ChildNode: ChildNumber=73 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 87 4
ChildNode: ChildNumber=72 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 110 87
ChildNode: ChildNumber=71 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 112 110
ChildNode: ChildNumber=70 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 73 112
ChildNode: ChildNumber=69 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 82 73
ChildNode: ChildNumber=68 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 19 82
ChildNode: ChildNumber=67 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 69 19
ChildNode: ChildNumber=66 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 23 69
ChildNode: ChildNumber=65 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 109 23
ChildNode: ChildNumber=64 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 91 109
ChildNode: ChildNumber=63 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 92 91
ChildNode: ChildNumber=62 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 48 92
ChildNode: ChildNumber=61 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 3 48
ChildNode: ChildNumber=60 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 122 3
ChildNode: ChildNumber=59 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 51 122
ChildNode: ChildNumber=58 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 90 51
ChildNode: ChildNumber=57 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 10 90
ChildNode: ChildNumber=56 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 7 10
ChildNode: ChildNumber=55 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 95 7
ChildNode: ChildNumber=54 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 38 95
ChildNode: ChildNumber=53 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 9 38
ChildNode: ChildNumber=52 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 52 9
ChildNode: ChildNumber=51 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 97 52
ChildNode: ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 24 97
ChildNode: ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 99 24
ChildNode: ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 75 99
ChildNode: ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 50 75
ChildNode: ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 57 50
ChildNode: ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 128 57
ChildNode: ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 108 128
ChildNode: ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 118 108
ChildNode: ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 72 118
ChildNode: ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 32 72
ChildNode: ChildNumber=40 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 85 32
ChildNode: ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 37 85
ChildNode: ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 88 37
ChildNode: ChildNumber=37 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 115 88
ChildNode: ChildNumber=36 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 41 115
ChildNode: ChildNumber=35 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 6 41
ChildNode: ChildNumber=34 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 123 6
ChildNode: ChildNumber=33 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 62 123
ChildNode: ChildNumber=32 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 106 62
ChildNode: ChildNumber=31 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 80 106
ChildNode: ChildNumber=30 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 65 80
ChildNode: ChildNumber=29 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 46 65
ChildNode: ChildNumber=28 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 11 46
ChildNode: ChildNumber=27 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 125 11
ChildNode: ChildNumber=26 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 8 125
ChildNode: ChildNumber=25 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 18 8
ChildNode: ChildNumber=24 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 42 18
ChildNode: ChildNumber=23 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 39 42
ChildNode: ChildNumber=22 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 94 39
ChildNode: ChildNumber=21 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 15 94
ChildNode: ChildNumber=20 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 81 15
ChildNode: ChildNumber=19 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 96 81
ChildNode: ChildNumber=18 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 64 96
ChildNode: ChildNumber=17 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 49 64
ChildNode: ChildNumber=16 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 70 49
ChildNode: ChildNumber=15 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 103 70
ChildNode: ChildNumber=14 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 2 103
ChildNode: ChildNumber=13 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 14 2
ChildNode: ChildNumber=12 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 76 14
ChildNode: ChildNumber=11 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 100 76
ChildNode: ChildNumber=10 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 34 100
ChildNode: ChildNumber=9 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 121 34
ChildNode: ChildNumber=8 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 56 121
ChildNode: ChildNumber=7 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 126 56
ChildNode: ChildNumber=6 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 66 126
ChildNode: ChildNumber=5 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 104 66
ChildNode: ChildNumber=4 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 26 104
ChildNode: ChildNumber=3 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 102 26
ChildNode: ChildNumber=2 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 60 102
ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 20 60
ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 83 20
$ sed -n "1,5p" shuf.txt
83
20
60
102
26
--//测试ChildNumber=99,0,128的执行情况。分别对应optimizer_index_cost_adj= 30，43，83.
--//测试前修改m13.txt
$ 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
$ awk 'NR ==1 || NR== 99 || NR == 100 || NR == 101 || NR==128 {print $0}' shuf.txt | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q > /dev/null 2>&1" 2>&1 | grep real
real 0m13.053s --//1
real 0m7.185s --//99
real 0m7.182s --//100
real 0m12.700s --//101
real 0m15.124s --//128
--//符合前面我的判断。
3.看看并发执行：
$ awk 'NR ==1 || NR== 99 || NR == 100 || NR == 101 || NR==128 {print NR, $0}' shuf.txt
1 83
99 16
100 30
101 29
128 43
--//测试childnum=99.
$ zzdate ; seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book@book01p @m13.txt 2e4 xx 30 > /dev/null ; zzdate
trunc(sysdate)+10/24+14/1440+15/86400 -1766110455.583240506
trunc(sysdate)+10/24+14/1440+23/86400 1766110463.366355420
--//Sum = 7.783114914
SYS@book> @ ashtop sql_id,event,p1raw 1=1 trunc(sysdate)+10/24+14/1440+15/86400 trunc(sysdate)+10/24+14/1440+23/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID EVENT P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ---------------------- ----------------- ------------------- ------------------- ---------- -------- -----------
50 6.3 27% | library cache: mutex X 00000000B14F5520 2025-12-19 10:14:18 2025-12-19 10:14:22 1 5 5
47 5.9 26% | 5zfc9hksnyp90 2025-12-19 10:14:16 2025-12-19 10:14:22 30 7 35
31 3.9 17% | 5zfc9hksnyp90 library cache: mutex X 00000000B14F5520 2025-12-19 10:14:17 2025-12-19 10:14:22 1 6 6
19 2.4 10% | 5h6jmnvr4zd4g 2025-12-19 10:14:17 2025-12-19 10:14:22 18 6 18
16 2.0 9% | 2025-12-19 10:14:17 2025-12-19 10:14:22 1 5 5
11 1.4 6% | 5h6jmnvr4zd4g library cache: mutex X 00000000B14F5520 2025-12-19 10:14:18 2025-12-19 10:14:22 10 5 10
5 .6 3% | 5zfc9hksnyp90 cursor: mutex S 0000000000000000 2025-12-19 10:14:19 2025-12-19 10:14:19 1 1 1
2 .3 1% | cursor: pin S 00000000B14F5520 2025-12-19 10:14:21 2025-12-19 10:14:21 1 1 1
1 .1 1% | 5zfc9hksnyp90 cursor: mutex S 00000000B14F5520 2025-12-19 10:14:18 2025-12-19 10:14:18 1 1 1
1 .1 1% | log file sync 0000000000001261 2025-12-19 10:14:18 2025-12-19 10:14:18 1 1 1
10 rows selected.
SYS@book> @mutexprofz hash,loc,maddr "hash=2974766368 and ts>=trunc(sysdate)+10/24+14/1440+15/86400 and ts<=trunc(sysdate)+10/24+14/1440+23/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
SUM_SLEEPS GETS_DIFF MUTEX_TYPE HASH GET_LOCATION mutex_addr SQL_ID OBJECT_NAME
---------- -------------- --------------- ---------- --------------------------------- -------------------- ------------- --------------------------------------------------------------------------------
55 542876 Cursor Pin 2974766368 kkslce [KKSCHLPIN2] 000000006CB29930 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
50 662427 hash table 2974766368 kkshGetNextChild [KKSHBKLOC1] 000000006EB59680 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
39 604491 Cursor Parent 2974766368 kkscsPruneChild [KKSPRTLOC27] 000000006EB59350 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
30 880309 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
16 813287 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
13 715493 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
11 979127 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
10 409311 Cursor Pin 2974766368 kksLockDelete [KKSCHLPIN6] 000000006CB29930 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
8 rows selected.
--//测试childnum=128.
$ zzdate ; seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book@book01p @m13.txt 1e4 xx 43 > /dev/null ; zzdate
trunc(sysdate)+10/24+19/1440+59/86400 -1766110799.803279657
trunc(sysdate)+10/24+20/1440+31/86400 1766110831.148415907
--//Sum = 31.34513625
SYS@book> @ ashtop sql_id,event,p1raw 1=1 trunc(sysdate)+10/24+19/1440+59/86400 trunc(sysdate)+10/24+20/1440+31/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID EVENT P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ----------------------- ----------------- ------------------- ------------------- ---------- -------- -----------
1111 34.7 83% | 5zfc9hksnyp90 cursor: mutex S 00000000B14F5520 2025-12-19 10:20:01 2025-12-19 10:20:30 1 30 30
140 4.4 10% | 5zfc9hksnyp90 2025-12-19 10:20:01 2025-12-19 10:20:30 17 29 45
49 1.5 4% | 5zfc9hksnyp90 library cache: mutex X 00000000B14F5520 2025-12-19 10:20:01 2025-12-19 10:20:30 1 13 13
15 .5 1% | 2025-12-19 10:20:02 2025-12-19 10:20:26 1 10 10
8 .3 1% | library cache: mutex X 00000000B14F5520 2025-12-19 10:20:02 2025-12-19 10:20:29 1 7 7
7 .2 1% | 5h6jmnvr4zd4g 2025-12-19 10:20:01 2025-12-19 10:20:28 6 6 6
5 .2 0% | 5zfc9hksnyp90 cursor: pin S 00000000B14F5520 2025-12-19 10:20:05 2025-12-19 10:20:05 1 1 1
4 .1 0% | 5h6jmnvr4zd4g library cache: mutex X 00000000B14F5520 2025-12-19 10:20:04 2025-12-19 10:20:23 3 4 3
1 .0 0% | 8btyg0wy808ch 2025-12-19 10:20:01 2025-12-19 10:20:01 1 1 1
1 .0 0% | 9mb61uqwwqkpj 2025-12-19 10:20:00 2025-12-19 10:20:00 1 1 1
1 .0 0% | f0h5rpzmhju11 2025-12-19 10:20:00 2025-12-19 10:20:00 1 1 1
1 .0 0% | LGWR all worker groups 0000000000000000 2025-12-19 10:20:09 2025-12-19 10:20:09 1 1 1
1 .0 0% | library cache lock 0000000070D82F70 2025-12-19 10:20:00 2025-12-19 10:20:00 1 1 1
1 .0 0% | log file parallel write 0000000000000001 2025-12-19 10:20:09 2025-12-19 10:20:09 1 1 1
1 .0 0% | log file sync 00000000000014C5 2025-12-19 10:20:02 2025-12-19 10:20:02 1 1 1
15 rows selected.
--//可以看出密集执行，选择childnum=128，cursor: mutex S占主要等待事件。
SYS@book> @mutexprofz hash,loc,maddr "hash=2974766368 and ts>=trunc(sysdate)+10/24+19/1440+59/86400 and ts<=trunc(sysdate)+10/24+20/1440+31/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
SUM_SLEEPS GETS_DIFF MUTEX_TYPE HASH GET_LOCATION mutex_addr SQL_ID OBJECT_NAME
---------- -------------- --------------- ---------- --------------------------------- -------------------- ------------- --------------------------------------------------------------------------------
53 28165046 hash table 2974766368 kkshGetNextChild [KKSHBKLOC1] 000000006EB59680 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
41 27957407 Cursor Parent 2974766368 kkscsPruneChild [KKSPRTLOC27] 000000006EB59350 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
39 525505 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
24 476231 Cursor Pin 2974766368 kkslce [KKSCHLPIN2] 00000000682FFE40 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
9 148658 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
6 203260 Cursor Pin 2974766368 kksLockDelete [KKSCHLPIN6] 00000000682FFE40 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
5 62376 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
1 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
8 rows selected.
--//看GET_LOCATION基本可以猜测花费在kkshGetNextChild，kkscsPruneChild 。
SYS@book> @ ev_namepr "cursor: mutex S"
==============================
EVENT# : 365
EVENT_ID : 1575214430
NAME : cursor: mutex S
PARAMETER1 : idn
PARAMETER2 : value
PARAMETER3 : where
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
DISPLAY_NAME : cursor: mutex S
CON_ID : 0
PL/SQL procedure successfully completed.
--//昨天下午测试在21c下sql语句子光标的执行性能，当时得出的规律如下：
--//建立128个子光标，oracle软软解析探查采用那个子光标的执行计划，是以ChildNumber=99作为分界点开始向ChildNumber=0探查，然
--//后再从ChildNumber=100->ChildNumber=127.
--//这样的探查方式，选择ChildNumber=99的语句执行最快，而选择ChildNumber=127的执行最慢。而以前11g的测试是选择
--//ChildNumber=127的执行最快，ChildNumber=0的最慢
--//当时已经很晚了，回家路上想oracle做出这样有它自己的考虑，毕竟超过100个子光标的情况很少见，如果大于100说明该语句遇到
--//bug或者什么问题，而且许多情况下一些子光标会不再使用，这样新产生的子光标多数情况下也不会大于100.
--//另外仔细看了以前的测试笔记，也遇到中间的某个子光标查询变快的情况，发现该子光标刷出共享池，实际情况仅仅刷出子光标的堆
--//0以及堆6，再次执行建立子光标堆0以及堆6.
--//换一句话讲，前面看到的"规律"仅仅是全部子光标都在的情况下才正确。其探查按照转储library_cache中CursorDiagnosticsNodes
--//显示顺序相关.
--//还是先验证我前面看到的情况是否正确。
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);
--//分析表略。
--//产生128个子光标.
$ 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。
$ shuf -i 1-128 > shuf.txt
--//建立随机显示1-128的数字列表。
$ awk 'NR ==1 || NR == 100 || NR==128 {print $0}' shuf.txt
83
30
43
$ cat shuf.txt | xargs -IQ sqlplus -s -l scott/book@book01p @m13.txt 6 xx Q > /dev/null
--//每个情况执行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
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump library_cache 8
Statement processed.
$ sed -n "124,128p" shuf.txt
1
105
119
68
43
--//查看转储：
NamespaceDump:
Parent Cursor: sql_id=5zfc9hksnyp90 parent=0x6eb59290 maxchild=128 plk=n ppn=n prsfcnt=0 obscnt=0
CursorDiagnosticsNodes:
ChildNode: ChildNumber=126 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 68 43
ChildNode: ChildNumber=125 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 119 68
ChildNode: ChildNumber=124 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 105 119
ChildNode: ChildNumber=123 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 1 105
ChildNode: ChildNumber=122 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 124 1
ChildNode: ChildNumber=121 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 36 124
ChildNode: ChildNumber=120 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 40 36
ChildNode: ChildNumber=119 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 22 40
ChildNode: ChildNumber=118 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 116 22
ChildNode: ChildNumber=117 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 55 116
ChildNode: ChildNumber=116 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 31 55
ChildNode: ChildNumber=115 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 54 31
ChildNode: ChildNumber=114 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 113 54
ChildNode: ChildNumber=113 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 84 113
ChildNode: ChildNumber=112 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 74 84
ChildNode: ChildNumber=111 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 53 74
ChildNode: ChildNumber=110 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 12 53
ChildNode: ChildNumber=109 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 27 12
ChildNode: ChildNumber=108 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 63 27
ChildNode: ChildNumber=107 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 47 63
ChildNode: ChildNumber=106 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 13 47
ChildNode: ChildNumber=105 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 35 13
ChildNode: ChildNumber=104 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 98 35
ChildNode: ChildNumber=103 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 44 98
ChildNode: ChildNumber=102 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 78 44
ChildNode: ChildNumber=101 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 71 78
ChildNode: ChildNumber=100 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 29 71
ChildNode: ChildNumber=99 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 30 29
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ChildNode: ChildNumber=98 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 16 30
ChildNode: ChildNumber=97 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 61 16
ChildNode: ChildNumber=96 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 5 61
ChildNode: ChildNumber=95 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 25 5
ChildNode: ChildNumber=94 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 59 25
ChildNode: ChildNumber=93 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 93 59
ChildNode: ChildNumber=92 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 114 93
ChildNode: ChildNumber=91 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 79 114
ChildNode: ChildNumber=90 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 117 79
ChildNode: ChildNumber=89 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 89 117
ChildNode: ChildNumber=88 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 111 89
ChildNode: ChildNumber=87 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 77 111
ChildNode: ChildNumber=86 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 45 77
ChildNode: ChildNumber=85 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 107 45
ChildNode: ChildNumber=84 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 21 107
ChildNode: ChildNumber=83 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 120 21
ChildNode: ChildNumber=82 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 28 120
ChildNode: ChildNumber=81 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 86 28
ChildNode: ChildNumber=80 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 17 86
ChildNode: ChildNumber=79 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 67 17
ChildNode: ChildNumber=78 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 127 67
ChildNode: ChildNumber=77 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 101 127
ChildNode: ChildNumber=76 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 33 101
ChildNode: ChildNumber=75 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 58 33
ChildNode: ChildNumber=74 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 4 58
ChildNode: ChildNumber=73 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 87 4
ChildNode: ChildNumber=72 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 110 87
ChildNode: ChildNumber=71 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 112 110
ChildNode: ChildNumber=70 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 73 112
ChildNode: ChildNumber=69 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 82 73
ChildNode: ChildNumber=68 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 19 82
ChildNode: ChildNumber=67 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 69 19
ChildNode: ChildNumber=66 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 23 69
ChildNode: ChildNumber=65 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 109 23
ChildNode: ChildNumber=64 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 91 109
ChildNode: ChildNumber=63 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 92 91
ChildNode: ChildNumber=62 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 48 92
ChildNode: ChildNumber=61 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 3 48
ChildNode: ChildNumber=60 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 122 3
ChildNode: ChildNumber=59 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 51 122
ChildNode: ChildNumber=58 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 90 51
ChildNode: ChildNumber=57 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 10 90
ChildNode: ChildNumber=56 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 7 10
ChildNode: ChildNumber=55 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 95 7
ChildNode: ChildNumber=54 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 38 95
ChildNode: ChildNumber=53 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 9 38
ChildNode: ChildNumber=52 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 52 9
ChildNode: ChildNumber=51 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 97 52
ChildNode: ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 24 97
ChildNode: ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 99 24
ChildNode: ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 75 99
ChildNode: ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 50 75
ChildNode: ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 57 50
ChildNode: ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 128 57
ChildNode: ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 108 128
ChildNode: ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 118 108
ChildNode: ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 72 118
ChildNode: ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 32 72
ChildNode: ChildNumber=40 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 85 32
ChildNode: ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 37 85
ChildNode: ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 88 37
ChildNode: ChildNumber=37 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 115 88
ChildNode: ChildNumber=36 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 41 115
ChildNode: ChildNumber=35 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 6 41
ChildNode: ChildNumber=34 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 123 6
ChildNode: ChildNumber=33 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 62 123
ChildNode: ChildNumber=32 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 106 62
ChildNode: ChildNumber=31 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 80 106
ChildNode: ChildNumber=30 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 65 80
ChildNode: ChildNumber=29 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 46 65
ChildNode: ChildNumber=28 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 11 46
ChildNode: ChildNumber=27 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 125 11
ChildNode: ChildNumber=26 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 8 125
ChildNode: ChildNumber=25 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 18 8
ChildNode: ChildNumber=24 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 42 18
ChildNode: ChildNumber=23 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 39 42
ChildNode: ChildNumber=22 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 94 39
ChildNode: ChildNumber=21 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 15 94
ChildNode: ChildNumber=20 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 81 15
ChildNode: ChildNumber=19 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 96 81
ChildNode: ChildNumber=18 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 64 96
ChildNode: ChildNumber=17 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 49 64
ChildNode: ChildNumber=16 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 70 49
ChildNode: ChildNumber=15 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 103 70
ChildNode: ChildNumber=14 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 2 103
ChildNode: ChildNumber=13 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 14 2
ChildNode: ChildNumber=12 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 76 14
ChildNode: ChildNumber=11 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 100 76
ChildNode: ChildNumber=10 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 34 100
ChildNode: ChildNumber=9 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 121 34
ChildNode: ChildNumber=8 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 56 121
ChildNode: ChildNumber=7 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 126 56
ChildNode: ChildNumber=6 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 66 126
ChildNode: ChildNumber=5 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 104 66
ChildNode: ChildNumber=4 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 26 104
ChildNode: ChildNumber=3 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 102 26
ChildNode: ChildNumber=2 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 60 102
ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 20 60
ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x508 optimizer_index_cost_adj= 83 20
$ sed -n "1,5p" shuf.txt
83
20
60
102
26
--//测试ChildNumber=99,0,128的执行情况。分别对应optimizer_index_cost_adj= 30，43，83.
--//测试前修改m13.txt
$ 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
$ awk 'NR ==1 || NR== 99 || NR == 100 || NR == 101 || NR==128 {print $0}' shuf.txt | xargs -IQ bash -c "time sqlplus -s -l scott/book@book01p @m13.txt 2e5 xx Q > /dev/null 2>&1" 2>&1 | grep real
real 0m13.053s --//1
real 0m7.185s --//99
real 0m7.182s --//100
real 0m12.700s --//101
real 0m15.124s --//128
--//符合前面我的判断。
3.看看并发执行：
$ awk 'NR ==1 || NR== 99 || NR == 100 || NR == 101 || NR==128 {print NR, $0}' shuf.txt
1 83
99 16
100 30
101 29
128 43
--//测试childnum=99.
$ zzdate ; seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book@book01p @m13.txt 2e4 xx 30 > /dev/null ; zzdate
trunc(sysdate)+10/24+14/1440+15/86400 -1766110455.583240506
trunc(sysdate)+10/24+14/1440+23/86400 1766110463.366355420
--//Sum = 7.783114914
SYS@book> @ ashtop sql_id,event,p1raw 1=1 trunc(sysdate)+10/24+14/1440+15/86400 trunc(sysdate)+10/24+14/1440+23/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID EVENT P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ---------------------- ----------------- ------------------- ------------------- ---------- -------- -----------
50 6.3 27% | library cache: mutex X 00000000B14F5520 2025-12-19 10:14:18 2025-12-19 10:14:22 1 5 5
47 5.9 26% | 5zfc9hksnyp90 2025-12-19 10:14:16 2025-12-19 10:14:22 30 7 35
31 3.9 17% | 5zfc9hksnyp90 library cache: mutex X 00000000B14F5520 2025-12-19 10:14:17 2025-12-19 10:14:22 1 6 6
19 2.4 10% | 5h6jmnvr4zd4g 2025-12-19 10:14:17 2025-12-19 10:14:22 18 6 18
16 2.0 9% | 2025-12-19 10:14:17 2025-12-19 10:14:22 1 5 5
11 1.4 6% | 5h6jmnvr4zd4g library cache: mutex X 00000000B14F5520 2025-12-19 10:14:18 2025-12-19 10:14:22 10 5 10
5 .6 3% | 5zfc9hksnyp90 cursor: mutex S 0000000000000000 2025-12-19 10:14:19 2025-12-19 10:14:19 1 1 1
2 .3 1% | cursor: pin S 00000000B14F5520 2025-12-19 10:14:21 2025-12-19 10:14:21 1 1 1
1 .1 1% | 5zfc9hksnyp90 cursor: mutex S 00000000B14F5520 2025-12-19 10:14:18 2025-12-19 10:14:18 1 1 1
1 .1 1% | log file sync 0000000000001261 2025-12-19 10:14:18 2025-12-19 10:14:18 1 1 1
10 rows selected.
SYS@book> @mutexprofz hash,loc,maddr "hash=2974766368 and ts>=trunc(sysdate)+10/24+14/1440+15/86400 and ts<=trunc(sysdate)+10/24+14/1440+23/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
SUM_SLEEPS GETS_DIFF MUTEX_TYPE HASH GET_LOCATION mutex_addr SQL_ID OBJECT_NAME
---------- -------------- --------------- ---------- --------------------------------- -------------------- ------------- --------------------------------------------------------------------------------
55 542876 Cursor Pin 2974766368 kkslce [KKSCHLPIN2] 000000006CB29930 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
50 662427 hash table 2974766368 kkshGetNextChild [KKSHBKLOC1] 000000006EB59680 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
39 604491 Cursor Parent 2974766368 kkscsPruneChild [KKSPRTLOC27] 000000006EB59350 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
30 880309 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
16 813287 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
13 715493 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
11 979127 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
10 409311 Cursor Pin 2974766368 kksLockDelete [KKSCHLPIN6] 000000006CB29930 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
8 rows selected.
--//测试childnum=128.
$ zzdate ; seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book@book01p @m13.txt 1e4 xx 43 > /dev/null ; zzdate
trunc(sysdate)+10/24+19/1440+59/86400 -1766110799.803279657
trunc(sysdate)+10/24+20/1440+31/86400 1766110831.148415907
--//Sum = 31.34513625
SYS@book> @ ashtop sql_id,event,p1raw 1=1 trunc(sysdate)+10/24+19/1440+59/86400 trunc(sysdate)+10/24+20/1440+31/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID EVENT P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ----------------------- ----------------- ------------------- ------------------- ---------- -------- -----------
1111 34.7 83% | 5zfc9hksnyp90 cursor: mutex S 00000000B14F5520 2025-12-19 10:20:01 2025-12-19 10:20:30 1 30 30
140 4.4 10% | 5zfc9hksnyp90 2025-12-19 10:20:01 2025-12-19 10:20:30 17 29 45
49 1.5 4% | 5zfc9hksnyp90 library cache: mutex X 00000000B14F5520 2025-12-19 10:20:01 2025-12-19 10:20:30 1 13 13
15 .5 1% | 2025-12-19 10:20:02 2025-12-19 10:20:26 1 10 10
8 .3 1% | library cache: mutex X 00000000B14F5520 2025-12-19 10:20:02 2025-12-19 10:20:29 1 7 7
7 .2 1% | 5h6jmnvr4zd4g 2025-12-19 10:20:01 2025-12-19 10:20:28 6 6 6
5 .2 0% | 5zfc9hksnyp90 cursor: pin S 00000000B14F5520 2025-12-19 10:20:05 2025-12-19 10:20:05 1 1 1
4 .1 0% | 5h6jmnvr4zd4g library cache: mutex X 00000000B14F5520 2025-12-19 10:20:04 2025-12-19 10:20:23 3 4 3
1 .0 0% | 8btyg0wy808ch 2025-12-19 10:20:01 2025-12-19 10:20:01 1 1 1
1 .0 0% | 9mb61uqwwqkpj 2025-12-19 10:20:00 2025-12-19 10:20:00 1 1 1
1 .0 0% | f0h5rpzmhju11 2025-12-19 10:20:00 2025-12-19 10:20:00 1 1 1
1 .0 0% | LGWR all worker groups 0000000000000000 2025-12-19 10:20:09 2025-12-19 10:20:09 1 1 1
1 .0 0% | library cache lock 0000000070D82F70 2025-12-19 10:20:00 2025-12-19 10:20:00 1 1 1
1 .0 0% | log file parallel write 0000000000000001 2025-12-19 10:20:09 2025-12-19 10:20:09 1 1 1
1 .0 0% | log file sync 00000000000014C5 2025-12-19 10:20:02 2025-12-19 10:20:02 1 1 1
15 rows selected.
--//可以看出密集执行，选择childnum=128，cursor: mutex S占主要等待事件。
SYS@book> @mutexprofz hash,loc,maddr "hash=2974766368 and ts>=trunc(sysdate)+10/24+19/1440+59/86400 and ts<=trunc(sysdate)+10/24+20/1440+31/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
SUM_SLEEPS GETS_DIFF MUTEX_TYPE HASH GET_LOCATION mutex_addr SQL_ID OBJECT_NAME
---------- -------------- --------------- ---------- --------------------------------- -------------------- ------------- --------------------------------------------------------------------------------
53 28165046 hash table 2974766368 kkshGetNextChild [KKSHBKLOC1] 000000006EB59680 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
41 27957407 Cursor Parent 2974766368 kkscsPruneChild [KKSPRTLOC27] 000000006EB59350 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
39 525505 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
24 476231 Cursor Pin 2974766368 kkslce [KKSCHLPIN2] 00000000682FFE40 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
9 148658 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
6 203260 Cursor Pin 2974766368 kksLockDelete [KKSCHLPIN6] 00000000682FFE40 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
5 62376 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
1 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hksnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1
8 rows selected.
--//看GET_LOCATION基本可以猜测花费在kkshGetNextChild，kkscsPruneChild 。
SYS@book> @ ev_namepr "cursor: mutex S"
==============================
EVENT# : 365
EVENT_ID : 1575214430
NAME : cursor: mutex S
PARAMETER1 : idn
PARAMETER2 : value
PARAMETER3 : where
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
DISPLAY_NAME : cursor: mutex S
CON_ID : 0
PL/SQL procedure successfully completed.
浙公网安备 33010602011771号