[20211031]18c row cache mutext等待事件探究.txt
[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2840020/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 等待事件之Row Cache Lock事件
- oracle 'row cache objects' 等待事件解釋OracleObject事件
- 由row cache lock等待事件引起的效能問題事件
- [20211026]關於18c row cache mutex.txtMutex
- hang了,嚴重的row cache lock 等待事件--就因大sql文字事件SQL
- [ORACLE 11G]ROW CACHE LOCK 等待Oracle
- 當刪除oracle資料庫user時發生row cache lock 等待事件Oracle資料庫事件
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- LIBRARY CACHE LOCK 等待事件事件
- library cache pin 等待事件事件
- Cache Buffer Chain Latch等待事件AI事件
- 等待事件enq: TX - row lock contention事件ENQ
- 【等待事件】-enq: TX - row lock contention事件ENQ
- [20191125]探究等待事件的本源.txt事件
- [20191127]探究等待事件的本源4.txt事件
- latch:library cache lock等待事件事件
- cache buffer lru chain latch等待事件AI事件
- 等待事件enq TX row lock contention分析事件ENQ
- 解決library cache pin等待事件事件
- enq：Library cache lock/pin等待事件ENQ事件
- ORACLE等待事件latch: cache buffers chainsOracle事件AI
- 等待事件_cache_buffers_chains_latch事件AI
- 等待事件_cache_buffers_lru_chain_latch事件AI
- enq: TX - row lock contention等待事件處理ENQ事件
- latch 相關效能問題診斷： latch: row cache objects等待事件導致CPU負載高Object事件負載
- [20191126]探究等待事件的本源2.txt事件
- How to Match a Row Cache Object Child Latch to its Row CacheObject
- library cache pin等待事件的模擬事件
- 【等待事件】global cache cr request/gc current request事件GC
- buffer cache與相關的latch等待事件事件
- 模擬cache buffers chains與library cache pin等待事件AI事件
- Metlink:How to Match a Row Cache Object Child Latch to its Row CacheObject
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- 尋找 library cache lock 等待事件的session事件Session
- 'library cache lock'等待事件的處理方法事件
- 俺也談談 library cache lock 等待事件事件
- [20240827]分析為什麼出現library cache lock等待事件2.txt事件