[20251219]测试sql语句子光标的执行性能2(21c).txt

来源：这里教程网时间：2026-03-03 23:02:15 作者：

[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='5zfc9hxsnyp90',注意sql语句全部大写，因为PL/SQL内部做了转化为大写。 SYS@book> @ s2h 5zfc9hxsnyp90 SQL_ID HASH_VALUE HASH_HEX KGL_BUCKET KGL_BUCKET_HEX ------------- ----------- --------- ---------- -------------- 5zfc9hxsnyp90 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=5zfc9hxsnyp90 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% | 5zfc9hxsnyp90 2025-12-19 10:14:16 2025-12-19 10:14:22 30 7 35 31 3.9 17% | 5zfc9hxsnyp90 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% | 5zfc9hxsnyp90 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% | 5zfc9hxsnyp90 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 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 50 662427 hash table 2974766368 kkshGetNextChild [KKSHBKLOC1] 000000006EB59680 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 39 604491 Cursor Parent 2974766368 kkscsPruneChild [KKSPRTLOC27] 000000006EB59350 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 30 880309 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 16 813287 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 13 715493 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 11 979127 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 10 409311 Cursor Pin 2974766368 kksLockDelete [KKSCHLPIN6] 000000006CB29930 5zfc9hxsnyp90 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% | 5zfc9hxsnyp90 cursor: mutex S 00000000B14F5520 2025-12-19 10:20:01 2025-12-19 10:20:30 1 30 30 140 4.4 10% | 5zfc9hxsnyp90 2025-12-19 10:20:01 2025-12-19 10:20:30 17 29 45 49 1.5 4% | 5zfc9hxsnyp90 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% | 5zfc9hxsnyp90 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 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 41 27957407 Cursor Parent 2974766368 kkscsPruneChild [KKSPRTLOC27] 000000006EB59350 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 39 525505 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 24 476231 Cursor Pin 2974766368 kkslce [KKSCHLPIN2] 00000000682FFE40 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 9 148658 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 6 203260 Cursor Pin 2974766368 kksLockDelete [KKSCHLPIN6] 00000000682FFE40 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 5 62376 Library Cache 2974766368 kgllkc1 57 000000006EB5A248 5zfc9hxsnyp90 SELECT COUNT(NAME) FROM T WHERE ID=1 1 Library Cache 2974766368 kgllkdl1 85 000000006EB5A248 5zfc9hxsnyp90 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.

编辑推荐：

返回列表

[20251219]测试sql语句子光标的执行性能2(21c).txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推