[20211031]18c row cache mutext等待事件探究.txt

来源：这里教程网时间：2026-03-03 17:06:06 作者：

[20211031]18c row cache mutext等待事件探究.txt --//昨天测试了多个用户并发执行select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = --//'NLS_CHARACTERSET';，非常缓慢的情况,实际上我在虚拟机器上执行性能并不好，但是缓慢的程度还是超出我的想像。 --//扫描视图nls_database_parameters实际上查询x$props基表，而查询x$props在12C以上版本实际上是访问数据字典，正是这一变化导 --//致并发访问出现大量row cache mutext等待事件，今天探究执行一次的情况。 --//测试前我看了ksun-oracle.blogspot.com 博客下相关文章，使用链接中的一些脚本。例如： --// ksun-oracle.blogspot.com/2020/08/row-cache-object-and-row-cache-mutex.html 1.环境： SYS@192.168.a.b:1521/orcl> @ ver SYS@192.168.a.b:1521/orcl> @ prxx ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 18.0.0.0.0 BANNER : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. SYS@192.168.a.b:1521/orcl> @ hide _kqr_optimistic_reads NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES_MODI ISSYS_MODIFIABLE --------------------- --------------------------------------- ------------- ------------- ------------ ---------- ------------------ _kqr_optimistic_reads optimistic reading of row cache objects TRUE TRUE TRUE TRUE IMMEDIATE --//建立函数dump_hex2str。 create or replace function dump_hex2str (dump_hex varchar2) return varchar2 is l_str varchar2(100); begin with sq_pos as (select level pos from dual connect by level <= 1000) ,sq_chr as (select pos, chr(to_number(substr(dump_hex, (pos-1)*2+1, 2), 'XX')) ch from sq_pos where pos <= length(dump_hex)/2) select listagg(ch, '') within group (order by pos) word into l_str from sq_chr; return replace(l_str,chr(0),''); end; / --//注：我修改一点点，返回使用replace(l_str,chr(0),''),这样感觉更加简洁一些,实际上作者的处理有点问题。 --//如果结尾ascii码是50(字符P)，使用dump_hex2str(rtrim(key,'0'))的输出会丢失P的显示，大家可以自行测试，不过目前的测试暂 --//时不使用。 $ cat dc.txt column gets new_value gets_old; column fastgets new_value gets_oldf; select sum(gets) gets, sum(fastgets) fastgets from v$rowcache where parameter = '&&1'; select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; SELECT (sum(gets)-&gets_old) gets_delta, (sum(fastgets)-&gets_oldf) gets_deltaf, sum(gets) gets, sum(fastgets) fastgets FROM v$rowcache WHERE parameter = '&&1'; --//执行如下在PDB层面上执行: SYS@192.168.a.b:1521/orcl> @ dc.txt dc_props GETS FASTGETS ---------- ---------- 266623440 0 VALUE -------- US7ASCII GETS_DELTA GETS_DELTAF GETS FASTGETS ---------- ----------- ---------- ---------- 82 0 266623522 0 SYS@192.168.a.b:1521/orcl> @ dc.txt dc_cdbprops GETS FASTGETS ---------- ---------- 19766971 0 VALUE -------- US7ASCII GETS_DELTA GETS_DELTAF GETS FASTGETS ---------- ----------- ---------- ---------- 7 0 19766978 0 --//你可以反复测试，因为可能别人也会访问数据字典，正常都是dc_props 82次，dc_cdbprops 7次，加起来89次。 --//这样猜测每个循环都出现1次gets，这样89次。 2.测试： SYS@192.168.a.b:1521/orcl> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---- ---------- ---------- ------------------ -------------------- ------- ---------- -------------------------------------------------- 411 15860 7740:5232 DEDICATED 19189 71 72933 alter system kill session '411,15860' immediate; select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; --//执行5次以上，避免一些递归。 --//查询orafun.info网站： Oracle C functions annotations Stack Single kqrpre - kernel query dictionary/row cache read a parent cache object The other hits are: Name Description kqrpre1 kernel query dictionary/row cache read a parent cache object 1 kqrpre2 kernel query dictionary/row cache read a parent cache object 2 - this is the real function kqreqd - kernel query dictionary/row cache enqueue delete --//建立gdb测试脚本： $ cat dc.gdb set pagination off set logging file /tmp/dc.log set logging overwrite on set logging on set $pre = 0 set $pre1 = 0 set $pre2 = 0 set $eqd = 0 break snttread commands set $pre = 0 set $pre1 = 0 set $pre2 = 0 set $eqd = 0 continue end break kqrpre commands silent shell echo -n $( date +"%Y/%m/%d %T.%N : ") printf "call kqrpre %d\n",++$pre continue end break kqrpre1 commands silent shell echo -n $( date +"%Y/%m/%d %T.%N : ") printf "call krqpre1 %d\n",++$pre1 continue end break kqrpre2 commands silent shell echo -n $( date +"%Y/%m/%d %T.%N : ") printf "call kqrpre2 %d\n",++$pre2 continue end break kqreqd commands silent shell echo -n $( date +"%Y/%m/%d %T.%N : ") printf "call kqreqd %d\n",++$eqd continue end --//session 1: # gdb -x dc.gdb -p 19189 ... Breakpoint 1 at 0x124ab6b0 Breakpoint 2 at 0x12186250 Breakpoint 3 at 0x1218d870 Breakpoint 4 at 0x12186290 Breakpoint 5 at 0x1218ddf0 (gdb) --//session 2: TTT@orcl> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; --//挂起： --//session 1: (gdb) c Continuing. 2021/11/01 09:37:03.845113320 :call krqpre1 1 2021/11/01 09:37:03.853013138 :call kqrpre2 1 2021/11/01 09:37:03.861123384 :call kqreqd 1 2021/11/01 09:37:03.869276275 :call krqpre1 2 2021/11/01 09:37:03.876952760 :call kqrpre2 2 2021/11/01 09:37:03.884459723 :call kqreqd 2 ... 2021/11/01 09:37:05.557477867 :call krqpre1 88 2021/11/01 09:37:05.563898406 :call kqrpre2 88 2021/11/01 09:37:05.570246951 :call kqreqd 88 2021/11/01 09:37:05.577686127 :call krqpre1 89 2021/11/01 09:37:05.584557377 :call kqrpre2 89 2021/11/01 09:37:05.591103306 :call kqreqd 89 Breakpoint 1, 0x00000000124ab6b0 in snttread () --//可以发现反复调用kqrpre1,kqrpre2,kqreqd函数，这也是为什么并发出现执行缓慢的原因。我不知道还有那些视图看上去是sql语句 --//，实际上访问数据字典的情况。可以发现调用正好89次。 $ grep kqrpre2 /tmp/dc.log |wc 89 267 1415 oracle@hosp2 IP=a.b ~/test $ grep kqrpre1 /tmp/dc.log |wc 0 0 0 oracle@hosp2 IP=a.b ~/test $ grep kqreqd /tmp/dc.log |wc 89 267 1326 --//一次执行每次调用89次。为什么是89次，难道存在89个访问对象吗？ --//即使我执行select * from sys.x$props where rownum=1; 也是调用这么多次。 SYS@orcl> SELECT cache# ,TYPE ,subordinate# ,parameter ,COUNT ,usage ,fixed ,gets ,fastgets FROM v$rowcache WHERE parameter IN ('dc_props', 'dc_cdbprops') ; CACHE# TYPE SUBORDINATE# PARAMETER COUNT USAGE FIXED GETS FASTGETS ---------- -------------------- ------------ -------------------- ---------- -------- ---------- ---------- ---------- 15 PARENT dc_props 145 145 0 266627340 0 60 PARENT dc_cdbprops 12 12 0 19767118 0 --//按照链接介绍，gets介绍至少145次。而我的测试仅仅89次。 --//在cdb层面执行如下： SYS@orclcdb> select count(*),cache_name from v$rowcache_parent where cache_name in ('dc_props','dc_cdbprops') group by cache_name; COUNT(*) CACHE_NAME ---------- ---------------------------------------------------------------- 12 dc_cdbprops 145 dc_props --//很明显前面count计数还包括cdb层面的信息。 SYS@orcl> select cache_name, existent, count(*) cnt from v$rowcache_parent where cache_name in ('dc_props','dc_cdbprops') group by cache_name, existent; CACHE_NAME E CNT ---------- - ---------- dc_props Y 36 dc_props N 24 --//dc_props 也就是60个。 SYS@orclcdb> SELECT cache_name ,existent ,con_id ,COUNT (*) cnt FROM v$rowcache_parent WHERE cache_name IN ('dc_props', 'dc_cdbprops') GROUP BY cache_name, existent, con_id ORDER BY con_id, cache_name; CACHE_NAME EXISTENT CON_ID CNT ------------ -------- ---------- ---------- dc_cdbprops N 1 11 dc_cdbprops Y 1 1 dc_props N 1 41 dc_props Y 1 42 dc_props N 3 24 dc_props Y 3 36 dc_props Y 5 2 --//看不出89次如何得出的。如果拿con_id=1来计算结果倒是很接近。 --//我在cdb层面上测试： (gdb) c Continuing. 2021/11/01 10:37:20.798161748 :call krqpre1 1 2021/11/01 10:37:20.804974451 :call kqrpre2 1 2021/11/01 10:37:20.811859822 :call krqpre1 2 2021/11/01 10:37:20.818386617 :call kqrpre2 2 --//前面2次单独调用krqpre1，krqpre2. 2021/11/01 10:37:20.825252708 :call krqpre1 3 2021/11/01 10:37:20.831884460 :call kqrpre2 3 2021/11/01 10:37:20.838560610 :call kqreqd 1 ... 2021/11/01 10:37:22.642237223 :call kqreqd 92 2021/11/01 10:37:22.648591872 :call krqpre1 95 2021/11/01 10:37:22.655067821 :call kqrpre2 95 2021/11/01 10:37:22.661674928 :call kqreqd 93 Breakpoint 1, 0x00000000124ab6b0 in snttread () SYS@orclcdb> @ dc.txt dc_props GETS FASTGETS ---------- ---------- 266631678 0 VALUE -------------------- AL32UTF8 GETS_DELTA GETS_DELTAF GETS FASTGETS ---------- ----------- ---------- ---------- 86 0 266631764 0 SYS@orclcdb> @ dc.txt dc_cdbprops GETS FASTGETS ---------- ---------- 19767328 0 VALUE -------------------- AL32UTF8 GETS_DELTA GETS_DELTAF GETS FASTGETS ---------- ----------- ---------- ---------- 7 0 19767335 0 --//正常dc_props 86次，dc_cdbprops 7次，加起来93次。 --//隐含参数_kqr_optimistic_reads = false 结果也是一样。 --//Row Cache Data Dump With following command, we can dump 'dc_props'. (See MOS Bug 19354335 - Diagnostic enhancement for rowcache data dumps (Doc ID 19354335.8)) alter session set tracefile_identifier = 'dc_props_dump'; -- dump level 0xf2b: f is cache id 15 ('dc_props'), 2 is single cacheiddump, b is level of 11 alter session set events 'immediate trace name row_cache level 0xf2b'; alter session set events 'immediate trace name row_cache off'; SYS@192.168.a.b:1521/orclcdb> alter session set events 'immediate trace name row_cache level 0xf2b'; Session altered. SYS@192.168.a.b:1521/orclcdb> alter session set events 'immediate trace name row_cache off'; Session altered. # egrep "^BUCKET|row cache parent object" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_18802.trc BUCKET 1: row cache parent object: addr=0x103b5f140 cid=15(dc_props) conid=1 conuid=1 BUCKET 2: row cache parent object: addr=0x12abf3f48 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12cb005d0 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 3: row cache parent object: addr=0x12e6923f8 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0xe1c52058 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 4: row cache parent object: addr=0xc0176f40 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12a327d98 cid=15(dc_props) conid=1 conuid=1 BUCKET 5: row cache parent object: addr=0x12bc01950 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x130a1b6a8 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 6: row cache parent object: addr=0x130f40618 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x12aebb778 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 9: row cache parent object: addr=0xdbf7a998 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12bf9de68 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x128d3ca58 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12dd604e8 cid=15(dc_props) conid=1 conuid=1 BUCKET 10: row cache parent object: addr=0x12a1f4898 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12e021228 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x7bf110e8 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 12: row cache parent object: addr=0x105ac9d80 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x1286f1d28 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 13: row cache parent object: addr=0xdb1c4a48 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 14: row cache parent object: addr=0x106f224e0 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 15: row cache parent object: addr=0x1002f9790 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x10459d288 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12ae8d608 cid=15(dc_props) conid=1 conuid=1 BUCKET 16: row cache parent object: addr=0x12e083918 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12cbe7330 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x128d29c90 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12bbe1dc8 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 17: row cache parent object: addr=0x13095c8d8 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x12e317fc8 cid=15(dc_props) conid=1 conuid=1 BUCKET 18: row cache parent object: addr=0x12eb92720 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12bf4cc38 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x12c57b2b0 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x10cba43e8 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 19: row cache parent object: addr=0x7432a200 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0xb7b138c0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12c792050 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x1305f5b08 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x10069e5c8 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 20: row cache parent object: addr=0x100470ca0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x1277d01e0 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x13053aa48 cid=15(dc_props) conid=5 conuid=707315228 BUCKET 21: row cache parent object: addr=0xe1994c58 cid=15(dc_props) conid=1 conuid=1 BUCKET 22: row cache parent object: addr=0x100250418 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12e64a1a0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0xe34234f0 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0xe44952d8 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12cc535d8 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0xe5361a80 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0xffb77848 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0xe3329f98 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x7ec8ca68 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 23: row cache parent object: addr=0x6d1f8a58 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12d83d5d8 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0xce936c48 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x12d2e58d8 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 24: row cache parent object: addr=0x10483f1b0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x108375480 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 25: row cache parent object: addr=0x7e1814d8 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12cdc69a0 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 26: row cache parent object: addr=0xe498a450 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 27: row cache parent object: addr=0x13039f688 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12a9f1db0 cid=15(dc_props) conid=1 conuid=1 BUCKET 28: row cache parent object: addr=0x1275bcc80 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12de961f8 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 29: row cache parent object: addr=0x12b548328 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 31: row cache parent object: addr=0x12c838738 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x72977890 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x1031d60b8 cid=15(dc_props) conid=5 conuid=707315228 row cache parent object: addr=0x104c58328 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 32: row cache parent object: addr=0x12e024748 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x80994460 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x10c49bbd8 cid=15(dc_props) conid=1 conuid=1 BUCKET 33: row cache parent object: addr=0x10c6ed3a0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x6fe6e868 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12ced2578 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x820c1260 cid=15(dc_props) conid=1 conuid=1 BUCKET 35: row cache parent object: addr=0x12be3cf40 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x10010eb78 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x100341188 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 36: row cache parent object: addr=0x72cee590 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12a458188 cid=15(dc_props) conid=1 conuid=1 BUCKET 37: row cache parent object: addr=0x12dae27f0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x10c45da20 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x127cab7b0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12cdb20f8 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 40: row cache parent object: addr=0x1273638f0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12dcfaa48 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12cea1f60 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0xbf7c5170 cid=15(dc_props) conid=1 conuid=1 BUCKET 41: row cache parent object: addr=0x12cd07240 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12db9c838 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x101ba6118 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 42: row cache parent object: addr=0x12c256098 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x130e58590 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 43: row cache parent object: addr=0x130aadb08 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0xff375670 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12d2598a0 cid=15(dc_props) conid=1 conuid=1 BUCKET 44: row cache parent object: addr=0x1307e5688 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x130c381f8 cid=15(dc_props) conid=1 conuid=1 BUCKET 45: row cache parent object: addr=0x12c3aabf8 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12de35bb8 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0xe3d985a0 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 46: row cache parent object: addr=0xb77272f0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12ed256c0 cid=15(dc_props) conid=1 conuid=1 BUCKET 47: row cache parent object: addr=0xfe517b78 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x130c93718 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 48: row cache parent object: addr=0x103f8d5a0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x1087049f0 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0xfe37a358 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 49: row cache parent object: addr=0x1073996f0 cid=15(dc_props) conid=1 conuid=1 BUCKET 50: row cache parent object: addr=0x12761f510 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0xe3463150 cid=15(dc_props) conid=1 conuid=1 BUCKET 52: row cache parent object: addr=0x11cb6a750 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 54: row cache parent object: addr=0x12c137bf0 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 55: row cache parent object: addr=0x12a7ad700 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x1087bf818 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 56: row cache parent object: addr=0x10c252050 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12af5c248 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x7667ca40 cid=15(dc_props) conid=1 conuid=1 BUCKET 57: row cache parent object: addr=0x12b180ae0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x104dcb230 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12c38fe58 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x10478e240 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 58: row cache parent object: addr=0x1066a96c8 cid=15(dc_props) conid=1 conuid=1 BUCKET 59: row cache parent object: addr=0x74ca2b30 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x10377d1f8 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0xbe59c230 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x130f6fe20 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x7b899610 cid=15(dc_props) conid=1 conuid=1 BUCKET 60: row cache parent object: addr=0x12cdcd9e0 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12d93d928 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x12b345970 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 61: row cache parent object: addr=0x12cf27708 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12db2ef40 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x12d16b7e0 cid=15(dc_props) conid=3 conuid=115310104 BUCKET 62: row cache parent object: addr=0x12d195038 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0x1057e8508 cid=15(dc_props) conid=1 conuid=1 BUCKET 64: row cache parent object: addr=0x12accc690 cid=15(dc_props) conid=1 conuid=1 row cache parent object: addr=0x12bf7ccc8 cid=15(dc_props) conid=3 conuid=115310104 row cache parent object: addr=0xdbb94918 cid=15(dc_props) conid=3 conuid=115310104 --//使用64bucket. # grep "cid=15(dc_props)" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_18802.trc | grep conid=1|wc 83 664 6451 # grep "cid=15(dc_props)" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_18802.trc | grep conid=3|wc 60 480 5146 --//60 = 0x3c SYS@192.168.a.b:1521/orclcdb> alter session set events 'immediate trace name row_cache level 0x3c2b'; Session altered. SYS@192.168.a.b:1521/orclcdb> alter session set events 'immediate trace name row_cache off'; Session altered. # egrep "^BUCKET|row cache parent object" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_19930.trc BUCKET 3: row cache parent object: addr=0x12db48578 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 9: row cache parent object: addr=0x107d48e40 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 23: row cache parent object: addr=0x12dfb4290 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 25: row cache parent object: addr=0x11c25fcd0 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 26: row cache parent object: addr=0x127775f68 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 33: row cache parent object: addr=0x1309126f0 cid=60(dc_cdbprops) conid=1 conuid=1 row cache parent object: addr=0x12ab5b3d8 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 35: row cache parent object: addr=0x12e834d60 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 36: row cache parent object: addr=0x12cc8dbe8 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 40: row cache parent object: addr=0x10844ac68 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 56: row cache parent object: addr=0xe326c598 cid=60(dc_cdbprops) conid=1 conuid=1 BUCKET 61: row cache parent object: addr=0x12c498930 cid=60(dc_cdbprops) conid=1 conuid=1 # egrep "^BUCKET|row cache parent object" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_19930.trc | grep "row cache parent object" |wc 12 96 971 3.在19c测试看看: SYS@192.168.ccc.ddd:1521/dyhis> @ prxx ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 19.0.0.0.0 BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. --//该数据库没有采用PDB模式。 SYS@192.168.ccc.ddd:1521/dyhis> @ dc.txt dc_props GETS FASTGETS ---------- ---------- 422989228 0 VALUE -------- ZHS16GBK GETS_DELTA GETS_DELTAF GETS FASTGETS ---------- ----------- ---------- ---------- 90 0 422989318 0 --// 90 SYS@192.168.ccc.ddd:1521/dyhis> @ dc.txt dc_cdbprops GETS FASTGETS ---------- ---------- 34835249 0 VALUE -------- ZHS16GBK GETS_DELTA GETS_DELTAF GETS FASTGETS ---------- ----------- ---------- ---------- 7 0 34835256 0 --//7 --//90+7 = 97 SYS@192.168.ccc.ddd:1521/dyhis> select cache_name, existent, count(*) cnt from v$rowcache_parent where cache_name in ('dc_props','dc_cdbprops') group by cache_name, existent; CACHE_NAME E CNT ------------- - ---------- dc_props Y 41 dc_props N 43 dc_cdbprops N 6 --// 41+43+6 = 90, 也有7次的差距。 SELECT cache# ,TYPE ,subordinate# ,parameter ,COUNT ,usage ,fixed ,gets ,fastgets FROM v$rowcache WHERE parameter IN ('dc_props', 'dc_cdbprops') ; CACHE# TYPE SUBORDINATE# PARAMETER COUNT USAGE FIXED GETS FASTGETS ------ ------ ------------ -------------------- ---------- -------- ---------- ---------- ---------- 15 PARENT dc_props 84 84 0 422990088 0 60 PARENT dc_cdbprops 6 6 0 34835256 0

编辑推荐：

返回列表

[20211031]18c row cache mutext等待事件探究.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推