[20260811]简单探究row cache mutex的定位问题.txt
[20260811]简单探究row cache mutex的定位问题.txt
--//如果知道某个对象hash值，在row cache如何定位呢？
--//首先说明这些分析讲的不好听就是瞎猜,对不对我自己都不是很清楚.
1.环境：
SYS@book> @ 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.
2.分析:
SYS@book> select distinct KSMCHCOM from x$ksmsp where KSMCHCOM like 'row cache%';
KSMCHCOM
----------------
row cache mutex
row cache hash
row cache
--//在共享池里chunk类型与row cache相关有row cache mutex，row cache hash，row cache。自然row cache定位与row cache hash相
--//关。
SYS@book> select count(*) from x$ksmsp where KSMCHCOM= 'row cache hash';
COUNT(*)
----------
62
--//又是一个巧合，正好62个,正好与前天的测试执行如下:
--//select a.*,(KSMCHSIZ-16)/24 buckets from x$ksmsp a where KSMCHCOM='row cache mutex' and mod(KSMCHSIZ-16,24)=0 ;
--//返回也是62条.
--//可以猜测KSMCHCOM= 'row cache hash'正好与前面KSMCHCOM='row cache mutex'的对应.
--//如果KSMCHCOM= 'row cache hash'类型chunk里面保存对象地址的话,每个地址占8位,首尾2个地址占16位.
SYS@book> select a.*,(KSMCHSIZ-16)/16 buckets from x$ksmsp a where KSMCHCOM= 'row cache hash';
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS
---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ----------
00007FD9EB781AA8 97188 1 1 1 1 row cache hash 00000000779D7C68 1048592 perm 0 0000000077004000 65536
00007FD9EB781700 97197 1 1 1 1 row cache hash 00000000779D6530 2064 perm 0 0000000077004000 128
00007FD9EB781358 97206 1 1 1 1 row cache hash 00000000779AA3F8 65552 perm 0 0000000077004000 4096
00007FD9EB780FB0 97215 1 1 1 1 row cache hash 00000000779A4AC0 8208 perm 0 0000000077004000 512
00007FD9EB780C08 97224 1 1 1 1 row cache hash 0000000077844988 524304 perm 0 0000000077004000 32768
00007FD9EB780860 97233 1 1 1 1 row cache hash 00000000776E4850 524304 perm 0 0000000077004000 32768
00007FD9EB7804B8 97242 1 1 1 1 row cache hash 00000000776E3C10 1040 perm 0 0000000077004000 64
00007FD9EB780110 97251 1 1 1 1 row cache hash 00000000776E0ED8 4112 perm 0 0000000077004000 256
00007FD9EB77FC78 97260 1 1 1 1 row cache hash 00000000776E0820 528 perm 0 0000000077004000 32
00007FD9EB77F8D0 97269 1 1 1 1 row cache hash 00000000775806E8 524304 perm 0 0000000077004000 32768
00007FD9EB77F528 97278 1 1 1 1 row cache hash 000000007757D9B0 4112 perm 0 0000000077004000 256
00007FD9EB77F180 97287 1 1 1 1 row cache hash 000000007757AC78 4112 perm 0 0000000077004000 256
00007FD9EB77EDD8 97296 1 1 1 1 row cache hash 00000000774CAB40 262160 perm 0 0000000077004000 16384
00007FD9EB75AC70 98715 1 1 1 1 row cache hash 0000000076A45FA8 1040 perm 0 0000000076081000 64
00007FD9EB75A8C8 98724 1 1 1 1 row cache hash 0000000076A45370 1040 perm 0 0000000076081000 64
00007FD9EB75A520 98733 1 1 1 1 row cache hash 0000000076A44738 1040 perm 0 0000000076081000 64
00007FD9EB75A178 98742 1 1 1 1 row cache hash 0000000076A43B00 1040 perm 0 0000000076081000 64
00007FD9EB759DD0 98751 1 1 1 1 row cache hash 0000000076A40DC8 4112 perm 0 0000000076081000 256
00007FD9EB759A28 98760 1 1 1 1 row cache hash 0000000076A3E090 4112 perm 0 0000000076081000 256
00007FD9EB759680 98769 1 1 1 1 row cache hash 0000000076A38758 8208 perm 0 0000000076081000 512
00007FD9EB7592D8 98778 1 1 1 1 row cache hash 0000000076A385F0 32 perm 0 0000000076081000 1
00007FD9EB758F30 98787 1 1 1 1 row cache hash 0000000076A358B8 4112 perm 0 0000000076081000 256
00007FD9EB758B88 98796 1 1 1 1 row cache hash 0000000076A2FF80 8208 perm 0 0000000076081000 512
00007FD9EB7587E0 98805 1 1 1 1 row cache hash 0000000076A2E848 2064 perm 0 0000000076081000 128
00007FD9EB758438 98814 1 1 1 1 row cache hash 0000000076A28F10 8208 perm 0 0000000076081000 512
00007FD9EB758090 98823 1 1 1 1 row cache hash 0000000076A235D8 8208 perm 0 0000000076081000 512
00007FD9EB757CE8 98832 1 1 1 1 row cache hash 0000000076A1DCA0 8208 perm 0 0000000076081000 512
00007FD9EB757940 98841 1 1 1 1 row cache hash 0000000076A18368 8208 perm 0 0000000076081000 512
00007FD9EB757598 98850 1 1 1 1 row cache hash 0000000076A12A30 8208 perm 0 0000000076081000 512
00007FD9EB7571F0 98859 1 1 1 1 row cache hash 0000000076A0D0F8 8208 perm 0 0000000076081000 512
00007FD9EB756E48 98868 1 1 1 1 row cache hash 0000000076A077C0 8208 perm 0 0000000076081000 512
00007FD9EB756AA0 98877 1 1 1 1 row cache hash 0000000076A01E88 8208 perm 0 0000000076081000 512
00007FD9EB7566F8 98886 1 1 1 1 row cache hash 00000000769FC550 8208 perm 0 0000000076081000 512
00007FD9EB756350 98895 1 1 1 1 row cache hash 000000007694C418 262160 perm 0 0000000076081000 16384
00007FD9EB755FA8 98904 1 1 1 1 row cache hash 000000007689C2E0 262160 perm 0 0000000076081000 16384
00007FD9EB755C00 98913 1 1 1 1 row cache hash 00000000767EC1A8 262160 perm 0 0000000076081000 16384
00007FD9EB755858 98922 1 1 1 1 row cache hash 000000007668C070 524304 perm 0 0000000076081000 32768
00007FD9EB7554B0 98931 1 1 1 1 row cache hash 000000007652BF38 524304 perm 0 0000000076081000 32768
00007FD9EB755108 98940 1 1 1 1 row cache hash 00000000763CBE00 524304 perm 0 0000000076081000 32768
00007FD9EB754D60 98949 1 1 1 1 row cache hash 000000007626BCC8 524304 perm 0 0000000076081000 32768
00007FD9EB7549B8 98958 1 1 1 1 row cache hash 0000000076266390 8208 perm 0 0000000076081000 512
00007FD9EB754610 98967 1 1 1 1 row cache hash 0000000076263658 4112 perm 0 0000000076081000 256
00007FD9EB754268 98976 1 1 1 1 row cache hash 0000000076260920 4112 perm 0 0000000076081000 256
00007FD9EB753EC0 98985 1 1 1 1 row cache hash 000000007625DBE8 4112 perm 0 0000000076081000 256
00007FD9EB753B18 98994 1 1 1 1 row cache hash 000000007625AEB0 4112 perm 0 0000000076081000 256
00007FD9EB753770 99003 1 1 1 1 row cache hash 0000000076255578 8208 perm 0 0000000076081000 512
00007FD9EB7533C8 99012 1 1 1 1 row cache hash 0000000076252840 4112 perm 0 0000000076081000 256
00007FD9EB753020 99021 1 1 1 1 row cache hash 000000007624FB08 4112 perm 0 0000000076081000 256
00007FD9EB752C78 99030 1 1 1 1 row cache hash 000000007624CDD0 4112 perm 0 0000000076081000 256
00007FD9EB7528D0 99039 1 1 1 1 row cache hash 0000000076247498 8208 perm 0 0000000076081000 512
00007FD9EB752528 99048 1 1 1 1 row cache hash 0000000076244760 4112 perm 0 0000000076081000 256
00007FD9EB752180 99057 1 1 1 1 row cache hash 0000000076241A28 4112 perm 0 0000000076081000 256
00007FD9EB751DD8 99066 1 1 1 1 row cache hash 00000000760E18F0 524304 perm 0 0000000076081000 32768
00007FD9EB751A30 99075 1 1 1 1 row cache hash 00000000760DEBB8 4112 perm 0 0000000076081000 256
00007FD9EB751688 99084 1 1 1 1 row cache hash 00000000760DBE80 4112 perm 0 0000000076081000 256
00007FD9EB7512E0 99093 1 1 1 1 row cache hash 00000000760DBA88 272 perm 0 0000000076081000 16
00007FD9EB750F38 99102 1 1 1 1 row cache hash 00000000760DAE50 1040 perm 0 0000000076081000 64
00007FD9EB750B90 99111 1 1 1 1 row cache hash 00000000760DA218 1040 perm 0 0000000076081000 64
00007FD9EB7507E8 99120 1 1 1 1 row cache hash 00000000760D48E0 8208 perm 0 0000000076081000 512
00007FD9EB750440 99129 1 1 1 1 row cache hash 00000000760CEFA8 8208 perm 0 0000000076081000 512
00007FD9EB750098 99138 1 1 1 1 row cache hash 00000000760C3E70 16400 perm 0 0000000076081000 1024
00007FD9EB74FCF0 99147 1 1 1 1 row cache hash 00000000760C1138 4112 perm 0 0000000076081000 256
62 rows selected.
--//如果仔细对比最后1列与以下语句执行的最后1列完成能对上.
select a.*,(KSMCHSIZ-16)/24 buckets from x$ksmsp a where KSMCHCOM='row cache mutex' and mod(KSMCHSIZ-16,24)=0 ;
--//写一个混合在一起的输出:
SELECT a.*
,CASE
WHEN ksmchcom = 'row cache mutex' AND MOD (KSMCHSIZ - 16, 24) = 0 THEN (KSMCHSIZ - 16) / 24
WHEN ksmchcom = 'row cache mutex' AND MOD (KSMCHSIZ - 16, 24) <> 0 THEN (KSMCHSIZ - 16) / 4
WHEN ksmchcom = 'row cache hash' AND MOD (KSMCHSIZ - 16, 16) = 0 THEN (KSMCHSIZ - 16) / 16
END
buckets
FROM x$ksmsp a
WHERE KSMCHCOM like 'row cache %' ;
SYS@book> /
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS
---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ----------
00007FD9EB7346E0 97906 1 1 1 1 row cache mutex 0000000077AD7C78 1572880 perm 0 0000000077004000 65536
00007FD9EB734678 97907 1 1 1 1 row cache hash 00000000779D7C68 1048592 perm 0 0000000077004000 65536
00007FD9EB7343A0 97914 1 1 1 1 row cache mutex 00000000779D7950 528 perm 0 0000000077004000 128
00007FD9EB734338 97915 1 1 1 1 row cache mutex 00000000779D6D40 3088 perm 0 0000000077004000 128
00007FD9EB7342D0 97916 1 1 1 1 row cache hash 00000000779D6530 2064 perm 0 0000000077004000 128
00007FD9EB733FF8 97923 1 1 1 1 row cache mutex 00000000779D2418 16400 perm 0 0000000077004000 4096
00007FD9EB733F90 97924 1 1 1 1 row cache mutex 00000000779BA408 98320 perm 0 0000000077004000 4096
00007FD9EB733F28 97925 1 1 1 1 row cache hash 00000000779AA3F8 65552 perm 0 0000000077004000 4096
00007FD9EB733C50 97932 1 1 1 1 row cache mutex 00000000779A9AE0 2064 perm 0 0000000077004000 512
00007FD9EB733BE8 97933 1 1 1 1 row cache mutex 00000000779A6AD0 12304 perm 0 0000000077004000 512
00007FD9EB733B80 97934 1 1 1 1 row cache hash 00000000779A4AC0 8208 perm 0 0000000077004000 512
00007FD9EB7338A8 97941 1 1 1 1 row cache mutex 00000000779849A8 131088 perm 0 0000000077004000 32768
00007FD9EB733840 97942 1 1 1 1 row cache mutex 00000000778C4998 786448 perm 0 0000000077004000 32768
00007FD9EB7337D8 97943 1 1 1 1 row cache hash 0000000077844988 524304 perm 0 0000000077004000 32768
00007FD9EB733500 97950 1 1 1 1 row cache mutex 0000000077824870 131088 perm 0 0000000077004000 32768
00007FD9EB733498 97951 1 1 1 1 row cache mutex 0000000077764860 786448 perm 0 0000000077004000 32768
00007FD9EB733430 97952 1 1 1 1 row cache hash 00000000776E4850 524304 perm 0 0000000077004000 32768
00007FD9EB733158 97959 1 1 1 1 row cache mutex 00000000776E4630 272 perm 0 0000000077004000 64
00007FD9EB7330F0 97960 1 1 1 1 row cache mutex 00000000776E4020 1552 perm 0 0000000077004000 64
00007FD9EB733088 97961 1 1 1 1 row cache hash 00000000776E3C10 1040 perm 0 0000000077004000 64
00007FD9EB732DB0 97968 1 1 1 1 row cache mutex 00000000776E36F8 1040 perm 0 0000000077004000 256
00007FD9EB732D48 97969 1 1 1 1 row cache mutex 00000000776E1EE8 6160 perm 0 0000000077004000 256
00007FD9EB732CE0 97970 1 1 1 1 row cache hash 00000000776E0ED8 4112 perm 0 0000000077004000 256
00007FD9EB732A08 97977 1 1 1 1 row cache mutex 00000000776E0D40 144 perm 0 0000000077004000 32
00007FD9EB7329A0 97978 1 1 1 1 row cache mutex 00000000776E0A30 784 perm 0 0000000077004000 32
00007FD9EB732938 97979 1 1 1 1 row cache hash 00000000776E0820 528 perm 0 0000000077004000 32
00007FD9EB732660 97986 1 1 1 1 row cache mutex 00000000776C0708 131088 perm 0 0000000077004000 32768
00007FD9EB7325F8 97987 1 1 1 1 row cache mutex 00000000776006F8 786448 perm 0 0000000077004000 32768
00007FD9EB732590 97988 1 1 1 1 row cache hash 00000000775806E8 524304 perm 0 0000000077004000 32768
00007FD9EB7322B8 97995 1 1 1 1 row cache mutex 00000000775801D0 1040 perm 0 0000000077004000 256
00007FD9EB732250 97996 1 1 1 1 row cache mutex 000000007757E9C0 6160 perm 0 0000000077004000 256
00007FD9EB7321E8 97997 1 1 1 1 row cache hash 000000007757D9B0 4112 perm 0 0000000077004000 256
00007FD9EB731F10 98004 1 1 1 1 row cache mutex 000000007757D498 1040 perm 0 0000000077004000 256
00007FD9EB731EA8 98005 1 1 1 1 row cache mutex 000000007757BC88 6160 perm 0 0000000077004000 256
00007FD9EB731E40 98006 1 1 1 1 row cache hash 000000007757AC78 4112 perm 0 0000000077004000 256
00007FD9EB731B68 98013 1 1 1 1 row cache mutex 000000007756AB60 65552 perm 0 0000000077004000 16384
00007FD9EB731B00 98014 1 1 1 1 row cache mutex 000000007750AB50 393232 perm 0 0000000077004000 16384
00007FD9EB731A98 98015 1 1 1 1 row cache hash 00000000774CAB40 262160 perm 0 0000000077004000 16384
00007FD9EB70DA00 99432 1 1 1 1 row cache mutex 0000000076A469C8 272 perm 0 0000000076081000 64
00007FD9EB70D998 99433 1 1 1 1 row cache mutex 0000000076A463B8 1552 perm 0 0000000076081000 64
00007FD9EB70D930 99434 1 1 1 1 row cache hash 0000000076A45FA8 1040 perm 0 0000000076081000 64
00007FD9EB70D658 99441 1 1 1 1 row cache mutex 0000000076A45D90 272 perm 0 0000000076081000 64
00007FD9EB70D5F0 99442 1 1 1 1 row cache mutex 0000000076A45780 1552 perm 0 0000000076081000 64
00007FD9EB70D588 99443 1 1 1 1 row cache hash 0000000076A45370 1040 perm 0 0000000076081000 64
00007FD9EB70D2B0 99450 1 1 1 1 row cache mutex 0000000076A45158 272 perm 0 0000000076081000 64
00007FD9EB70D248 99451 1 1 1 1 row cache mutex 0000000076A44B48 1552 perm 0 0000000076081000 64
00007FD9EB70D1E0 99452 1 1 1 1 row cache hash 0000000076A44738 1040 perm 0 0000000076081000 64
00007FD9EB70CF08 99459 1 1 1 1 row cache mutex 0000000076A44520 272 perm 0 0000000076081000 64
00007FD9EB70CEA0 99460 1 1 1 1 row cache mutex 0000000076A43F10 1552 perm 0 0000000076081000 64
00007FD9EB70CE38 99461 1 1 1 1 row cache hash 0000000076A43B00 1040 perm 0 0000000076081000 64
00007FD9EB70CB60 99468 1 1 1 1 row cache mutex 0000000076A435E8 1040 perm 0 0000000076081000 256
00007FD9EB70CAF8 99469 1 1 1 1 row cache mutex 0000000076A41DD8 6160 perm 0 0000000076081000 256
00007FD9EB70CA90 99470 1 1 1 1 row cache hash 0000000076A40DC8 4112 perm 0 0000000076081000 256
00007FD9EB70C7B8 99477 1 1 1 1 row cache mutex 0000000076A408B0 1040 perm 0 0000000076081000 256
00007FD9EB70C750 99478 1 1 1 1 row cache mutex 0000000076A3F0A0 6160 perm 0 0000000076081000 256
00007FD9EB70C6E8 99479 1 1 1 1 row cache hash 0000000076A3E090 4112 perm 0 0000000076081000 256
00007FD9EB70C410 99486 1 1 1 1 row cache mutex 0000000076A3D778 2064 perm 0 0000000076081000 512
00007FD9EB70C3A8 99487 1 1 1 1 row cache mutex 0000000076A3A768 12304 perm 0 0000000076081000 512
00007FD9EB70C340 99488 1 1 1 1 row cache hash 0000000076A38758 8208 perm 0 0000000076081000 512
00007FD9EB70C068 99495 1 1 1 1 row cache mutex 0000000076A38638 24 perm 0 0000000076081000 2
00007FD9EB70C000 99496 1 1 1 1 row cache mutex 0000000076A38610 40 perm 0 0000000076081000 1
00007FD9EB70BF98 99497 1 1 1 1 row cache hash 0000000076A385F0 32 perm 0 0000000076081000 1
00007FD9EB70BCC0 99504 1 1 1 1 row cache mutex 0000000076A380D8 1040 perm 0 0000000076081000 256
00007FD9EB70BC58 99505 1 1 1 1 row cache mutex 0000000076A368C8 6160 perm 0 0000000076081000 256
00007FD9EB70BBF0 99506 1 1 1 1 row cache hash 0000000076A358B8 4112 perm 0 0000000076081000 256
00007FD9EB70B918 99513 1 1 1 1 row cache mutex 0000000076A34FA0 2064 perm 0 0000000076081000 512
00007FD9EB70B8B0 99514 1 1 1 1 row cache mutex 0000000076A31F90 12304 perm 0 0000000076081000 512
00007FD9EB70B848 99515 1 1 1 1 row cache hash 0000000076A2FF80 8208 perm 0 0000000076081000 512
00007FD9EB70B570 99522 1 1 1 1 row cache mutex 0000000076A2FC68 528 perm 0 0000000076081000 128
00007FD9EB70B508 99523 1 1 1 1 row cache mutex 0000000076A2F058 3088 perm 0 0000000076081000 128
00007FD9EB70B4A0 99524 1 1 1 1 row cache hash 0000000076A2E848 2064 perm 0 0000000076081000 128
00007FD9EB70B1C8 99531 1 1 1 1 row cache mutex 0000000076A2DF30 2064 perm 0 0000000076081000 512
00007FD9EB70B160 99532 1 1 1 1 row cache mutex 0000000076A2AF20 12304 perm 0 0000000076081000 512
00007FD9EB70B0F8 99533 1 1 1 1 row cache hash 0000000076A28F10 8208 perm 0 0000000076081000 512
00007FD9EB70AE20 99540 1 1 1 1 row cache mutex 0000000076A285F8 2064 perm 0 0000000076081000 512
00007FD9EB70ADB8 99541 1 1 1 1 row cache mutex 0000000076A255E8 12304 perm 0 0000000076081000 512
00007FD9EB70AD50 99542 1 1 1 1 row cache hash 0000000076A235D8 8208 perm 0 0000000076081000 512
00007FD9EB70AA78 99549 1 1 1 1 row cache mutex 0000000076A22CC0 2064 perm 0 0000000076081000 512
00007FD9EB70AA10 99550 1 1 1 1 row cache mutex 0000000076A1FCB0 12304 perm 0 0000000076081000 512
00007FD9EB70A9A8 99551 1 1 1 1 row cache hash 0000000076A1DCA0 8208 perm 0 0000000076081000 512
00007FD9EB70A6D0 99558 1 1 1 1 row cache mutex 0000000076A1D388 2064 perm 0 0000000076081000 512
00007FD9EB70A668 99559 1 1 1 1 row cache mutex 0000000076A1A378 12304 perm 0 0000000076081000 512
00007FD9EB70A600 99560 1 1 1 1 row cache hash 0000000076A18368 8208 perm 0 0000000076081000 512
00007FD9EB70A328 99567 1 1 1 1 row cache mutex 0000000076A17A50 2064 perm 0 0000000076081000 512
00007FD9EB70A2C0 99568 1 1 1 1 row cache mutex 0000000076A14A40 12304 perm 0 0000000076081000 512
00007FD9EB70A258 99569 1 1 1 1 row cache hash 0000000076A12A30 8208 perm 0 0000000076081000 512
00007FD9EB709F80 99576 1 1 1 1 row cache mutex 0000000076A12118 2064 perm 0 0000000076081000 512
00007FD9EB709F18 99577 1 1 1 1 row cache mutex 0000000076A0F108 12304 perm 0 0000000076081000 512
00007FD9EB709EB0 99578 1 1 1 1 row cache hash 0000000076A0D0F8 8208 perm 0 0000000076081000 512
00007FD9EB709BD8 99585 1 1 1 1 row cache mutex 0000000076A0C7E0 2064 perm 0 0000000076081000 512
00007FD9EB709B70 99586 1 1 1 1 row cache mutex 0000000076A097D0 12304 perm 0 0000000076081000 512
00007FD9EB709B08 99587 1 1 1 1 row cache hash 0000000076A077C0 8208 perm 0 0000000076081000 512
00007FD9EB709830 99594 1 1 1 1 row cache mutex 0000000076A06EA8 2064 perm 0 0000000076081000 512
00007FD9EB7097C8 99595 1 1 1 1 row cache mutex 0000000076A03E98 12304 perm 0 0000000076081000 512
00007FD9EB709760 99596 1 1 1 1 row cache hash 0000000076A01E88 8208 perm 0 0000000076081000 512
00007FD9EB709488 99603 1 1 1 1 row cache mutex 0000000076A01570 2064 perm 0 0000000076081000 512
00007FD9EB709420 99604 1 1 1 1 row cache mutex 00000000769FE560 12304 perm 0 0000000076081000 512
00007FD9EB7093B8 99605 1 1 1 1 row cache hash 00000000769FC550 8208 perm 0 0000000076081000 512
00007FD9EB7090E0 99612 1 1 1 1 row cache mutex 00000000769EC438 65552 perm 0 0000000076081000 16384
00007FD9EB709078 99613 1 1 1 1 row cache mutex 000000007698C428 393232 perm 0 0000000076081000 16384
00007FD9EB709010 99614 1 1 1 1 row cache hash 000000007694C418 262160 perm 0 0000000076081000 16384
00007FD9EB708D38 99621 1 1 1 1 row cache mutex 000000007693C300 65552 perm 0 0000000076081000 16384
00007FD9EB708CD0 99622 1 1 1 1 row cache mutex 00000000768DC2F0 393232 perm 0 0000000076081000 16384
00007FD9EB708C68 99623 1 1 1 1 row cache hash 000000007689C2E0 262160 perm 0 0000000076081000 16384
00007FD9EB708990 99630 1 1 1 1 row cache mutex 000000007688C1C8 65552 perm 0 0000000076081000 16384
00007FD9EB708928 99631 1 1 1 1 row cache mutex 000000007682C1B8 393232 perm 0 0000000076081000 16384
00007FD9EB7088C0 99632 1 1 1 1 row cache hash 00000000767EC1A8 262160 perm 0 0000000076081000 16384
00007FD9EB7085E8 99639 1 1 1 1 row cache mutex 00000000767CC090 131088 perm 0 0000000076081000 32768
00007FD9EB708580 99640 1 1 1 1 row cache mutex 000000007670C080 786448 perm 0 0000000076081000 32768
00007FD9EB708518 99641 1 1 1 1 row cache hash 000000007668C070 524304 perm 0 0000000076081000 32768
00007FD9EB708240 99648 1 1 1 1 row cache mutex 000000007666BF58 131088 perm 0 0000000076081000 32768
00007FD9EB7081D8 99649 1 1 1 1 row cache mutex 00000000765ABF48 786448 perm 0 0000000076081000 32768
00007FD9EB708170 99650 1 1 1 1 row cache hash 000000007652BF38 524304 perm 0 0000000076081000 32768
00007FD9EB707E98 99657 1 1 1 1 row cache mutex 000000007650BE20 131088 perm 0 0000000076081000 32768
00007FD9EB707E30 99658 1 1 1 1 row cache mutex 000000007644BE10 786448 perm 0 0000000076081000 32768
00007FD9EB707DC8 99659 1 1 1 1 row cache hash 00000000763CBE00 524304 perm 0 0000000076081000 32768
00007FD9EB707AF0 99666 1 1 1 1 row cache mutex 00000000763ABCE8 131088 perm 0 0000000076081000 32768
00007FD9EB707A88 99667 1 1 1 1 row cache mutex 00000000762EBCD8 786448 perm 0 0000000076081000 32768
00007FD9EB707A20 99668 1 1 1 1 row cache hash 000000007626BCC8 524304 perm 0 0000000076081000 32768
00007FD9EB707748 99675 1 1 1 1 row cache mutex 000000007626B3B0 2064 perm 0 0000000076081000 512
00007FD9EB7076E0 99676 1 1 1 1 row cache mutex 00000000762683A0 12304 perm 0 0000000076081000 512
00007FD9EB707678 99677 1 1 1 1 row cache hash 0000000076266390 8208 perm 0 0000000076081000 512
00007FD9EB7073A0 99684 1 1 1 1 row cache mutex 0000000076265E78 1040 perm 0 0000000076081000 256
00007FD9EB707338 99685 1 1 1 1 row cache mutex 0000000076264668 6160 perm 0 0000000076081000 256
00007FD9EB7072D0 99686 1 1 1 1 row cache hash 0000000076263658 4112 perm 0 0000000076081000 256
00007FD9EB706FF8 99693 1 1 1 1 row cache mutex 0000000076263140 1040 perm 0 0000000076081000 256
00007FD9EB706F90 99694 1 1 1 1 row cache mutex 0000000076261930 6160 perm 0 0000000076081000 256
00007FD9EB706F28 99695 1 1 1 1 row cache hash 0000000076260920 4112 perm 0 0000000076081000 256
00007FD9EB706C50 99702 1 1 1 1 row cache mutex 0000000076260408 1040 perm 0 0000000076081000 256
00007FD9EB706BE8 99703 1 1 1 1 row cache mutex 000000007625EBF8 6160 perm 0 0000000076081000 256
00007FD9EB706B80 99704 1 1 1 1 row cache hash 000000007625DBE8 4112 perm 0 0000000076081000 256
00007FD9EB7068A8 99711 1 1 1 1 row cache mutex 000000007625D6D0 1040 perm 0 0000000076081000 256
00007FD9EB706840 99712 1 1 1 1 row cache mutex 000000007625BEC0 6160 perm 0 0000000076081000 256
00007FD9EB7067D8 99713 1 1 1 1 row cache hash 000000007625AEB0 4112 perm 0 0000000076081000 256
00007FD9EB706500 99720 1 1 1 1 row cache mutex 000000007625A598 2064 perm 0 0000000076081000 512
00007FD9EB706498 99721 1 1 1 1 row cache mutex 0000000076257588 12304 perm 0 0000000076081000 512
00007FD9EB706430 99722 1 1 1 1 row cache hash 0000000076255578 8208 perm 0 0000000076081000 512
00007FD9EB706158 99729 1 1 1 1 row cache mutex 0000000076255060 1040 perm 0 0000000076081000 256
00007FD9EB7060F0 99730 1 1 1 1 row cache mutex 0000000076253850 6160 perm 0 0000000076081000 256
00007FD9EB706088 99731 1 1 1 1 row cache hash 0000000076252840 4112 perm 0 0000000076081000 256
00007FD9EB705DB0 99738 1 1 1 1 row cache mutex 0000000076252328 1040 perm 0 0000000076081000 256
00007FD9EB705D48 99739 1 1 1 1 row cache mutex 0000000076250B18 6160 perm 0 0000000076081000 256
00007FD9EB705CE0 99740 1 1 1 1 row cache hash 000000007624FB08 4112 perm 0 0000000076081000 256
00007FD9EB705A08 99747 1 1 1 1 row cache mutex 000000007624F5F0 1040 perm 0 0000000076081000 256
00007FD9EB7059A0 99748 1 1 1 1 row cache mutex 000000007624DDE0 6160 perm 0 0000000076081000 256
00007FD9EB705938 99749 1 1 1 1 row cache hash 000000007624CDD0 4112 perm 0 0000000076081000 256
00007FD9EB705660 99756 1 1 1 1 row cache mutex 000000007624C4B8 2064 perm 0 0000000076081000 512
00007FD9EB7055F8 99757 1 1 1 1 row cache mutex 00000000762494A8 12304 perm 0 0000000076081000 512
00007FD9EB705590 99758 1 1 1 1 row cache hash 0000000076247498 8208 perm 0 0000000076081000 512
00007FD9EB7052B8 99765 1 1 1 1 row cache mutex 0000000076246F80 1040 perm 0 0000000076081000 256
00007FD9EB705250 99766 1 1 1 1 row cache mutex 0000000076245770 6160 perm 0 0000000076081000 256
00007FD9EB7051E8 99767 1 1 1 1 row cache hash 0000000076244760 4112 perm 0 0000000076081000 256
00007FD9EB704F10 99774 1 1 1 1 row cache mutex 0000000076244248 1040 perm 0 0000000076081000 256
00007FD9EB704EA8 99775 1 1 1 1 row cache mutex 0000000076242A38 6160 perm 0 0000000076081000 256
00007FD9EB704E40 99776 1 1 1 1 row cache hash 0000000076241A28 4112 perm 0 0000000076081000 256
00007FD9EB704B68 99783 1 1 1 1 row cache mutex 0000000076221910 131088 perm 0 0000000076081000 32768
00007FD9EB704B00 99784 1 1 1 1 row cache mutex 0000000076161900 786448 perm 0 0000000076081000 32768
00007FD9EB704A98 99785 1 1 1 1 row cache hash 00000000760E18F0 524304 perm 0 0000000076081000 32768
00007FD9EB7047C0 99792 1 1 1 1 row cache mutex 00000000760E13D8 1040 perm 0 0000000076081000 256
00007FD9EB704758 99793 1 1 1 1 row cache mutex 00000000760DFBC8 6160 perm 0 0000000076081000 256
00007FD9EB7046F0 99794 1 1 1 1 row cache hash 00000000760DEBB8 4112 perm 0 0000000076081000 256
00007FD9EB704418 99801 1 1 1 1 row cache mutex 00000000760DE6A0 1040 perm 0 0000000076081000 256
00007FD9EB7043B0 99802 1 1 1 1 row cache mutex 00000000760DCE90 6160 perm 0 0000000076081000 256
00007FD9EB704348 99803 1 1 1 1 row cache hash 00000000760DBE80 4112 perm 0 0000000076081000 256
00007FD9EB704070 99810 1 1 1 1 row cache mutex 00000000760DBD28 80 perm 0 0000000076081000 16
00007FD9EB704008 99811 1 1 1 1 row cache mutex 00000000760DBB98 400 perm 0 0000000076081000 16
00007FD9EB703FA0 99812 1 1 1 1 row cache hash 00000000760DBA88 272 perm 0 0000000076081000 16
00007FD9EB703CC8 99819 1 1 1 1 row cache mutex 00000000760DB870 272 perm 0 0000000076081000 64
00007FD9EB703C60 99820 1 1 1 1 row cache mutex 00000000760DB260 1552 perm 0 0000000076081000 64
00007FD9EB703BF8 99821 1 1 1 1 row cache hash 00000000760DAE50 1040 perm 0 0000000076081000 64
00007FD9EB703920 99828 1 1 1 1 row cache mutex 00000000760DAC38 272 perm 0 0000000076081000 64
00007FD9EB7038B8 99829 1 1 1 1 row cache mutex 00000000760DA628 1552 perm 0 0000000076081000 64
00007FD9EB703850 99830 1 1 1 1 row cache hash 00000000760DA218 1040 perm 0 0000000076081000 64
00007FD9EB703578 99837 1 1 1 1 row cache mutex 00000000760D9900 2064 perm 0 0000000076081000 512
00007FD9EB703510 99838 1 1 1 1 row cache mutex 00000000760D68F0 12304 perm 0 0000000076081000 512
00007FD9EB7034A8 99839 1 1 1 1 row cache hash 00000000760D48E0 8208 perm 0 0000000076081000 512
00007FD9EB7031D0 99846 1 1 1 1 row cache mutex 00000000760D3FC8 2064 perm 0 0000000076081000 512
00007FD9EB703168 99847 1 1 1 1 row cache mutex 00000000760D0FB8 12304 perm 0 0000000076081000 512
00007FD9EB703100 99848 1 1 1 1 row cache hash 00000000760CEFA8 8208 perm 0 0000000076081000 512
00007FD9EB702E28 99855 1 1 1 1 row cache mutex 00000000760CDE90 4112 perm 0 0000000076081000 1024
00007FD9EB702DC0 99856 1 1 1 1 row cache mutex 00000000760C7E80 24592 perm 0 0000000076081000 1024
00007FD9EB702D58 99857 1 1 1 1 row cache hash 00000000760C3E70 16400 perm 0 0000000076081000 1024
00007FD9EB702A80 99864 1 1 1 1 row cache mutex 00000000760C3958 1040 perm 0 0000000076081000 256
00007FD9EB702A18 99865 1 1 1 1 row cache mutex 00000000760C2148 6160 perm 0 0000000076081000 256
00007FD9EB7029B0 99866 1 1 1 1 row cache hash 00000000760C1138 4112 perm 0 0000000076081000 256
00007FD9EB7026D8 99873 1 1 1 1 row cache mutex 0000000076081020 262160 perm 0 0000000076081000 65536
186 rows selected.
--//INDX从97914开始,3个一组,最后1列BUCKETS都是一样.
--//INDX= 97906, 97907 与最后1条记录输出(INDX=99873)，最后1列BUCKETS=65536.
--//单独拿cache_name='dc_objects' chunk看看，注我的测试环境每次重启该地址不会变化的，我没有修改sga相关配置:
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS
---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ----------
00007FD9EB7338A8 97941 1 1 1 1 row cache mutex 00000000779849A8 131088 perm 0 0000000077004000 32768
00007FD9EB733840 97942 1 1 1 1 row cache mutex 00000000778C4998 786448 perm 0 0000000077004000 32768
00007FD9EB7337D8 97943 1 1 1 1 row cache hash 0000000077844988 524304 perm 0 0000000077004000 32768
--//以hash=6777,6778为例定位地址:
--//row cache mutex:
0x00000000779849A8+16+4*6777 = 0x7798b39c
0x00000000778C4998+16+24*6777 = 0x778ec500
0x00000000778C4998+16+24*6778 = 0x778ec518
--//row cache hash:
0x0000000077844988+16+16*6777 = 0x7785f128
0x0000000077844988+16+16*6778 = 0x7785f138
SYS@book> @ dc/dc_objects "hash in (6777,6778) "
USERNAME DC_OBJ_NAME KEY_STR_LEN HASH_HEX INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT LOCK_MODE LOCK_REQUEST TXN SADDR INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID
-------- ----------- ----------- -------- ------ ------ ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ---------------- ----------------- ----------------- -- -------- -------- ------
KOTTBX$ 7 0x1a7a 2789 6778 000000006FB7A870 8 dc_objects Y 0 0 00 00 0 0 00 00 2
--//当前hash=6778有1个对象，不理解这个对象CON_ID=2，CON_NAME=PDB$SEED ，为什么启动时会访问加载。
--//username显示null 的原因是cdb_users视图没有con_id=2的记录。
--//使用odadebug peek查看:
SYS@book> @ opeek 00000000778EC500 24 0
[0778EC500, 0778EC518) = 00000000 00000000 00000000 00000000 08001A79 00000000
SYS@book> @ opeek 00000000778EC518 24 0
[0778EC518, 0778EC530) = 00000000 00000000 0000000B 00000000 08001A7A 00000000
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 00000002 0100000E
~~~~~~~~
--//当前有1个对象在hash=6778.
SYS@book> @ opeek 7785f128 16 0
[07785F128, 07785F138) = 7785F128 00000000 7785F128 00000000
--//首尾地址0x7785F128，等于opeek的地址,也就是hash=6777的没有任何对象。
SYS@book> @ opeek 7785f138 16 0
[07785F138, 07785F148) = 6FB7AC60 00000000 6FB7AC60 00000000
--//hash=6778有1个对象，row cache hash该位置记录的信息是0x6FB7AC60。
SYS@book> @ fchaz 6FB7AC60
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006FB7A840 1 1 KQR PO 1328 recr 584 00 000000006FB7A840 000000006FB7AD70
--//偏移0x6FB7AC60-0x6FB7A840 = 0x420 = 1056
--//有点意外的时row cache hash chunk里面保存的不是row cache对象的句柄地址。与开头偏移0x420，与句柄地址偏移0x420-0x30 = 0x3f0 = 1008
SYS@book> @ dc/dc_objects "hash in (6777,6778) "
USERNAME DC_OBJ_NAME KEY_STR_LEN HASH_HEX INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT LOCK_MODE LOCK_REQUEST TXN SADDR INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID
-------- ----------- ----------- -------- ------ ------ ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ---------------- ----------------- ----------------- -- -------- -------- ------
KOTTBX$ 7 0x1a7a 2795 6778 000000006FB7A870 8 dc_objects Y 0 0 00 00 0 0 00 00 2
--//ADDRESS=000000006FB7A870
SYS@book> @ fchaz 000000006FB7A870
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006FB7A840 1 1 KQR PO 1328 recr 584 00 000000006FB7A840 000000006FB7AD70
--//正好在相同chunk里面。
--//0x000000006FB7A870-0x000000006FB7A840 = 0x30,句柄偏移开始位置正好48字节。
SYS@book> @ opeek 6FB7AC60 48 0
[06FB7AC60, 06FB7AC90) = 7785F138 00000000 7785F138 00000000 00000001 6A0C42D5 6FB7A870 00000000 778876E8 00000000 778876E8 00000000
~~~~~~~~~~~~~~~~~ +++++++++++++++++
--//查看地址0x6FB7AC60开头的16字节，0x7785F138不是正好指向hash=6778的row cache hash的对应地址吗？这样正好形成一个环。
3.深入分析：
--//执行调用zz6.txt脚本前的情况：
SYS@book> @ opeek 7785f138 16 0
[07785F138, 07785F148) = 6FB7AC60 00000000 6FB7AC60 00000000
--//如果存在多个对象呢？
$ zzdate;seq 1 | xargs -IQ -P 1 sqlplus -s -l '/ as sysdba' @zz6.txt 1e7 test1 Q > /dev/null ; zzdate
trunc(sysdate)+09/24+25/1440+48/86400 1786497948.773474937
...
--//这样根据前面测试在hash=6777，6778分别增加1个对象。
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 01000002 02000012
--//hash=6777有1个对象，hash=6778有2个对象.
SYS@book> @ dc/dc_objects "hash in (6777,6778) "
USERNAME DC_OBJ_NAME KEY_STR_LEN HASH_HEX INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT LOCK_MODE LOCK_REQUEST TXN SADDR INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID
-------------------- -------------------------------- ----------- -------- ------ ------ ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ---------------- ----------------- ----------------- -- -------- -------- ------
SYS 0 0x1a79 6444 6777 0000000062E1B668 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 6445 6778 0000000062E0E988 11 dc_objects N 0 0 00 00 0 0 00 00 1
KOTTBX$ 7 0x1a7a 2795 6778 000000006FB7A870 8 dc_objects Y 0 0 00 00 0 0 00 00 2
--//再看看看hash=6778的情况：
--//执行调用zz6.txt脚本后的情况：
SYS@book> @ opeek 7785f138 16 0
[07785F138, 07785F148) = 62E0ED98 00000000 6FB7AC60 00000000
~~~~~~~~~~~~~~~~~
--//相当于增加一个对象的地址。对应就是@ dc/dc_objects "hash in (6777,6778) " 的ADDRESS=0000000062E0E988的对象，不展开分析。
--//顺着0x62E0ED98地址查看：
SYS@book> @ opeek 62E0ED98 16 0
[062E0ED98, 062E0EDB8) = 6FB7AC60 00000000 7785F138 00000000
SYS@book> @ opeek 6FB7AC60 16 0
[06FB7AC60, 06FB7AC80) = 7785F138 00000000 62E0ED98 00000000
SYS@book> @ opeek 7785F138 16 0
[07785F138, 07785F148) = 62E0ED98 00000000 6FB7AC60 00000000
--//正好形成1个环。
--//再看看看hash=6777的情况：
SYS@book> @ opeek 7785f128 16 0
[07785F128, 07785F138) = 62E1BA78 00000000 62E1BA78 00000000
SYS@book> @ opeek 62E1BA78 16 0
[062E1BA78, 062E1BA88) = 7785F128 00000000 7785F128 00000000
--//正好也是形成1个环。
4.疑问：
--//剩下还有一个问题无法解决，假设一个表scott.dept，对象类型dc_objects，oracle如何确定那些chunk上寻找row cache mutex以及
--//row cache hash呢? 必须还有chunk记录这些信息.
--//另外写一篇blog分析.
--//如果知道某个对象hash值，在row cache如何定位呢？
--//首先说明这些分析讲的不好听就是瞎猜,对不对我自己都不是很清楚.
1.环境：
SYS@book> @ 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.
2.分析:
SYS@book> select distinct KSMCHCOM from x$ksmsp where KSMCHCOM like 'row cache%';
KSMCHCOM
----------------
row cache mutex
row cache hash
row cache
--//在共享池里chunk类型与row cache相关有row cache mutex，row cache hash，row cache。自然row cache定位与row cache hash相
--//关。
SYS@book> select count(*) from x$ksmsp where KSMCHCOM= 'row cache hash';
COUNT(*)
----------
62
--//又是一个巧合，正好62个,正好与前天的测试执行如下:
--//select a.*,(KSMCHSIZ-16)/24 buckets from x$ksmsp a where KSMCHCOM='row cache mutex' and mod(KSMCHSIZ-16,24)=0 ;
--//返回也是62条.
--//可以猜测KSMCHCOM= 'row cache hash'正好与前面KSMCHCOM='row cache mutex'的对应.
--//如果KSMCHCOM= 'row cache hash'类型chunk里面保存对象地址的话,每个地址占8位,首尾2个地址占16位.
SYS@book> select a.*,(KSMCHSIZ-16)/16 buckets from x$ksmsp a where KSMCHCOM= 'row cache hash';
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS
---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ----------
00007FD9EB781AA8 97188 1 1 1 1 row cache hash 00000000779D7C68 1048592 perm 0 0000000077004000 65536
00007FD9EB781700 97197 1 1 1 1 row cache hash 00000000779D6530 2064 perm 0 0000000077004000 128
00007FD9EB781358 97206 1 1 1 1 row cache hash 00000000779AA3F8 65552 perm 0 0000000077004000 4096
00007FD9EB780FB0 97215 1 1 1 1 row cache hash 00000000779A4AC0 8208 perm 0 0000000077004000 512
00007FD9EB780C08 97224 1 1 1 1 row cache hash 0000000077844988 524304 perm 0 0000000077004000 32768
00007FD9EB780860 97233 1 1 1 1 row cache hash 00000000776E4850 524304 perm 0 0000000077004000 32768
00007FD9EB7804B8 97242 1 1 1 1 row cache hash 00000000776E3C10 1040 perm 0 0000000077004000 64
00007FD9EB780110 97251 1 1 1 1 row cache hash 00000000776E0ED8 4112 perm 0 0000000077004000 256
00007FD9EB77FC78 97260 1 1 1 1 row cache hash 00000000776E0820 528 perm 0 0000000077004000 32
00007FD9EB77F8D0 97269 1 1 1 1 row cache hash 00000000775806E8 524304 perm 0 0000000077004000 32768
00007FD9EB77F528 97278 1 1 1 1 row cache hash 000000007757D9B0 4112 perm 0 0000000077004000 256
00007FD9EB77F180 97287 1 1 1 1 row cache hash 000000007757AC78 4112 perm 0 0000000077004000 256
00007FD9EB77EDD8 97296 1 1 1 1 row cache hash 00000000774CAB40 262160 perm 0 0000000077004000 16384
00007FD9EB75AC70 98715 1 1 1 1 row cache hash 0000000076A45FA8 1040 perm 0 0000000076081000 64
00007FD9EB75A8C8 98724 1 1 1 1 row cache hash 0000000076A45370 1040 perm 0 0000000076081000 64
00007FD9EB75A520 98733 1 1 1 1 row cache hash 0000000076A44738 1040 perm 0 0000000076081000 64
00007FD9EB75A178 98742 1 1 1 1 row cache hash 0000000076A43B00 1040 perm 0 0000000076081000 64
00007FD9EB759DD0 98751 1 1 1 1 row cache hash 0000000076A40DC8 4112 perm 0 0000000076081000 256
00007FD9EB759A28 98760 1 1 1 1 row cache hash 0000000076A3E090 4112 perm 0 0000000076081000 256
00007FD9EB759680 98769 1 1 1 1 row cache hash 0000000076A38758 8208 perm 0 0000000076081000 512
00007FD9EB7592D8 98778 1 1 1 1 row cache hash 0000000076A385F0 32 perm 0 0000000076081000 1
00007FD9EB758F30 98787 1 1 1 1 row cache hash 0000000076A358B8 4112 perm 0 0000000076081000 256
00007FD9EB758B88 98796 1 1 1 1 row cache hash 0000000076A2FF80 8208 perm 0 0000000076081000 512
00007FD9EB7587E0 98805 1 1 1 1 row cache hash 0000000076A2E848 2064 perm 0 0000000076081000 128
00007FD9EB758438 98814 1 1 1 1 row cache hash 0000000076A28F10 8208 perm 0 0000000076081000 512
00007FD9EB758090 98823 1 1 1 1 row cache hash 0000000076A235D8 8208 perm 0 0000000076081000 512
00007FD9EB757CE8 98832 1 1 1 1 row cache hash 0000000076A1DCA0 8208 perm 0 0000000076081000 512
00007FD9EB757940 98841 1 1 1 1 row cache hash 0000000076A18368 8208 perm 0 0000000076081000 512
00007FD9EB757598 98850 1 1 1 1 row cache hash 0000000076A12A30 8208 perm 0 0000000076081000 512
00007FD9EB7571F0 98859 1 1 1 1 row cache hash 0000000076A0D0F8 8208 perm 0 0000000076081000 512
00007FD9EB756E48 98868 1 1 1 1 row cache hash 0000000076A077C0 8208 perm 0 0000000076081000 512
00007FD9EB756AA0 98877 1 1 1 1 row cache hash 0000000076A01E88 8208 perm 0 0000000076081000 512
00007FD9EB7566F8 98886 1 1 1 1 row cache hash 00000000769FC550 8208 perm 0 0000000076081000 512
00007FD9EB756350 98895 1 1 1 1 row cache hash 000000007694C418 262160 perm 0 0000000076081000 16384
00007FD9EB755FA8 98904 1 1 1 1 row cache hash 000000007689C2E0 262160 perm 0 0000000076081000 16384
00007FD9EB755C00 98913 1 1 1 1 row cache hash 00000000767EC1A8 262160 perm 0 0000000076081000 16384
00007FD9EB755858 98922 1 1 1 1 row cache hash 000000007668C070 524304 perm 0 0000000076081000 32768
00007FD9EB7554B0 98931 1 1 1 1 row cache hash 000000007652BF38 524304 perm 0 0000000076081000 32768
00007FD9EB755108 98940 1 1 1 1 row cache hash 00000000763CBE00 524304 perm 0 0000000076081000 32768
00007FD9EB754D60 98949 1 1 1 1 row cache hash 000000007626BCC8 524304 perm 0 0000000076081000 32768
00007FD9EB7549B8 98958 1 1 1 1 row cache hash 0000000076266390 8208 perm 0 0000000076081000 512
00007FD9EB754610 98967 1 1 1 1 row cache hash 0000000076263658 4112 perm 0 0000000076081000 256
00007FD9EB754268 98976 1 1 1 1 row cache hash 0000000076260920 4112 perm 0 0000000076081000 256
00007FD9EB753EC0 98985 1 1 1 1 row cache hash 000000007625DBE8 4112 perm 0 0000000076081000 256
00007FD9EB753B18 98994 1 1 1 1 row cache hash 000000007625AEB0 4112 perm 0 0000000076081000 256
00007FD9EB753770 99003 1 1 1 1 row cache hash 0000000076255578 8208 perm 0 0000000076081000 512
00007FD9EB7533C8 99012 1 1 1 1 row cache hash 0000000076252840 4112 perm 0 0000000076081000 256
00007FD9EB753020 99021 1 1 1 1 row cache hash 000000007624FB08 4112 perm 0 0000000076081000 256
00007FD9EB752C78 99030 1 1 1 1 row cache hash 000000007624CDD0 4112 perm 0 0000000076081000 256
00007FD9EB7528D0 99039 1 1 1 1 row cache hash 0000000076247498 8208 perm 0 0000000076081000 512
00007FD9EB752528 99048 1 1 1 1 row cache hash 0000000076244760 4112 perm 0 0000000076081000 256
00007FD9EB752180 99057 1 1 1 1 row cache hash 0000000076241A28 4112 perm 0 0000000076081000 256
00007FD9EB751DD8 99066 1 1 1 1 row cache hash 00000000760E18F0 524304 perm 0 0000000076081000 32768
00007FD9EB751A30 99075 1 1 1 1 row cache hash 00000000760DEBB8 4112 perm 0 0000000076081000 256
00007FD9EB751688 99084 1 1 1 1 row cache hash 00000000760DBE80 4112 perm 0 0000000076081000 256
00007FD9EB7512E0 99093 1 1 1 1 row cache hash 00000000760DBA88 272 perm 0 0000000076081000 16
00007FD9EB750F38 99102 1 1 1 1 row cache hash 00000000760DAE50 1040 perm 0 0000000076081000 64
00007FD9EB750B90 99111 1 1 1 1 row cache hash 00000000760DA218 1040 perm 0 0000000076081000 64
00007FD9EB7507E8 99120 1 1 1 1 row cache hash 00000000760D48E0 8208 perm 0 0000000076081000 512
00007FD9EB750440 99129 1 1 1 1 row cache hash 00000000760CEFA8 8208 perm 0 0000000076081000 512
00007FD9EB750098 99138 1 1 1 1 row cache hash 00000000760C3E70 16400 perm 0 0000000076081000 1024
00007FD9EB74FCF0 99147 1 1 1 1 row cache hash 00000000760C1138 4112 perm 0 0000000076081000 256
62 rows selected.
--//如果仔细对比最后1列与以下语句执行的最后1列完成能对上.
select a.*,(KSMCHSIZ-16)/24 buckets from x$ksmsp a where KSMCHCOM='row cache mutex' and mod(KSMCHSIZ-16,24)=0 ;
--//写一个混合在一起的输出:
SELECT a.*
,CASE
WHEN ksmchcom = 'row cache mutex' AND MOD (KSMCHSIZ - 16, 24) = 0 THEN (KSMCHSIZ - 16) / 24
WHEN ksmchcom = 'row cache mutex' AND MOD (KSMCHSIZ - 16, 24) <> 0 THEN (KSMCHSIZ - 16) / 4
WHEN ksmchcom = 'row cache hash' AND MOD (KSMCHSIZ - 16, 16) = 0 THEN (KSMCHSIZ - 16) / 16
END
buckets
FROM x$ksmsp a
WHERE KSMCHCOM like 'row cache %' ;
SYS@book> /
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS
---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ----------
00007FD9EB7346E0 97906 1 1 1 1 row cache mutex 0000000077AD7C78 1572880 perm 0 0000000077004000 65536
00007FD9EB734678 97907 1 1 1 1 row cache hash 00000000779D7C68 1048592 perm 0 0000000077004000 65536
00007FD9EB7343A0 97914 1 1 1 1 row cache mutex 00000000779D7950 528 perm 0 0000000077004000 128
00007FD9EB734338 97915 1 1 1 1 row cache mutex 00000000779D6D40 3088 perm 0 0000000077004000 128
00007FD9EB7342D0 97916 1 1 1 1 row cache hash 00000000779D6530 2064 perm 0 0000000077004000 128
00007FD9EB733FF8 97923 1 1 1 1 row cache mutex 00000000779D2418 16400 perm 0 0000000077004000 4096
00007FD9EB733F90 97924 1 1 1 1 row cache mutex 00000000779BA408 98320 perm 0 0000000077004000 4096
00007FD9EB733F28 97925 1 1 1 1 row cache hash 00000000779AA3F8 65552 perm 0 0000000077004000 4096
00007FD9EB733C50 97932 1 1 1 1 row cache mutex 00000000779A9AE0 2064 perm 0 0000000077004000 512
00007FD9EB733BE8 97933 1 1 1 1 row cache mutex 00000000779A6AD0 12304 perm 0 0000000077004000 512
00007FD9EB733B80 97934 1 1 1 1 row cache hash 00000000779A4AC0 8208 perm 0 0000000077004000 512
00007FD9EB7338A8 97941 1 1 1 1 row cache mutex 00000000779849A8 131088 perm 0 0000000077004000 32768
00007FD9EB733840 97942 1 1 1 1 row cache mutex 00000000778C4998 786448 perm 0 0000000077004000 32768
00007FD9EB7337D8 97943 1 1 1 1 row cache hash 0000000077844988 524304 perm 0 0000000077004000 32768
00007FD9EB733500 97950 1 1 1 1 row cache mutex 0000000077824870 131088 perm 0 0000000077004000 32768
00007FD9EB733498 97951 1 1 1 1 row cache mutex 0000000077764860 786448 perm 0 0000000077004000 32768
00007FD9EB733430 97952 1 1 1 1 row cache hash 00000000776E4850 524304 perm 0 0000000077004000 32768
00007FD9EB733158 97959 1 1 1 1 row cache mutex 00000000776E4630 272 perm 0 0000000077004000 64
00007FD9EB7330F0 97960 1 1 1 1 row cache mutex 00000000776E4020 1552 perm 0 0000000077004000 64
00007FD9EB733088 97961 1 1 1 1 row cache hash 00000000776E3C10 1040 perm 0 0000000077004000 64
00007FD9EB732DB0 97968 1 1 1 1 row cache mutex 00000000776E36F8 1040 perm 0 0000000077004000 256
00007FD9EB732D48 97969 1 1 1 1 row cache mutex 00000000776E1EE8 6160 perm 0 0000000077004000 256
00007FD9EB732CE0 97970 1 1 1 1 row cache hash 00000000776E0ED8 4112 perm 0 0000000077004000 256
00007FD9EB732A08 97977 1 1 1 1 row cache mutex 00000000776E0D40 144 perm 0 0000000077004000 32
00007FD9EB7329A0 97978 1 1 1 1 row cache mutex 00000000776E0A30 784 perm 0 0000000077004000 32
00007FD9EB732938 97979 1 1 1 1 row cache hash 00000000776E0820 528 perm 0 0000000077004000 32
00007FD9EB732660 97986 1 1 1 1 row cache mutex 00000000776C0708 131088 perm 0 0000000077004000 32768
00007FD9EB7325F8 97987 1 1 1 1 row cache mutex 00000000776006F8 786448 perm 0 0000000077004000 32768
00007FD9EB732590 97988 1 1 1 1 row cache hash 00000000775806E8 524304 perm 0 0000000077004000 32768
00007FD9EB7322B8 97995 1 1 1 1 row cache mutex 00000000775801D0 1040 perm 0 0000000077004000 256
00007FD9EB732250 97996 1 1 1 1 row cache mutex 000000007757E9C0 6160 perm 0 0000000077004000 256
00007FD9EB7321E8 97997 1 1 1 1 row cache hash 000000007757D9B0 4112 perm 0 0000000077004000 256
00007FD9EB731F10 98004 1 1 1 1 row cache mutex 000000007757D498 1040 perm 0 0000000077004000 256
00007FD9EB731EA8 98005 1 1 1 1 row cache mutex 000000007757BC88 6160 perm 0 0000000077004000 256
00007FD9EB731E40 98006 1 1 1 1 row cache hash 000000007757AC78 4112 perm 0 0000000077004000 256
00007FD9EB731B68 98013 1 1 1 1 row cache mutex 000000007756AB60 65552 perm 0 0000000077004000 16384
00007FD9EB731B00 98014 1 1 1 1 row cache mutex 000000007750AB50 393232 perm 0 0000000077004000 16384
00007FD9EB731A98 98015 1 1 1 1 row cache hash 00000000774CAB40 262160 perm 0 0000000077004000 16384
00007FD9EB70DA00 99432 1 1 1 1 row cache mutex 0000000076A469C8 272 perm 0 0000000076081000 64
00007FD9EB70D998 99433 1 1 1 1 row cache mutex 0000000076A463B8 1552 perm 0 0000000076081000 64
00007FD9EB70D930 99434 1 1 1 1 row cache hash 0000000076A45FA8 1040 perm 0 0000000076081000 64
00007FD9EB70D658 99441 1 1 1 1 row cache mutex 0000000076A45D90 272 perm 0 0000000076081000 64
00007FD9EB70D5F0 99442 1 1 1 1 row cache mutex 0000000076A45780 1552 perm 0 0000000076081000 64
00007FD9EB70D588 99443 1 1 1 1 row cache hash 0000000076A45370 1040 perm 0 0000000076081000 64
00007FD9EB70D2B0 99450 1 1 1 1 row cache mutex 0000000076A45158 272 perm 0 0000000076081000 64
00007FD9EB70D248 99451 1 1 1 1 row cache mutex 0000000076A44B48 1552 perm 0 0000000076081000 64
00007FD9EB70D1E0 99452 1 1 1 1 row cache hash 0000000076A44738 1040 perm 0 0000000076081000 64
00007FD9EB70CF08 99459 1 1 1 1 row cache mutex 0000000076A44520 272 perm 0 0000000076081000 64
00007FD9EB70CEA0 99460 1 1 1 1 row cache mutex 0000000076A43F10 1552 perm 0 0000000076081000 64
00007FD9EB70CE38 99461 1 1 1 1 row cache hash 0000000076A43B00 1040 perm 0 0000000076081000 64
00007FD9EB70CB60 99468 1 1 1 1 row cache mutex 0000000076A435E8 1040 perm 0 0000000076081000 256
00007FD9EB70CAF8 99469 1 1 1 1 row cache mutex 0000000076A41DD8 6160 perm 0 0000000076081000 256
00007FD9EB70CA90 99470 1 1 1 1 row cache hash 0000000076A40DC8 4112 perm 0 0000000076081000 256
00007FD9EB70C7B8 99477 1 1 1 1 row cache mutex 0000000076A408B0 1040 perm 0 0000000076081000 256
00007FD9EB70C750 99478 1 1 1 1 row cache mutex 0000000076A3F0A0 6160 perm 0 0000000076081000 256
00007FD9EB70C6E8 99479 1 1 1 1 row cache hash 0000000076A3E090 4112 perm 0 0000000076081000 256
00007FD9EB70C410 99486 1 1 1 1 row cache mutex 0000000076A3D778 2064 perm 0 0000000076081000 512
00007FD9EB70C3A8 99487 1 1 1 1 row cache mutex 0000000076A3A768 12304 perm 0 0000000076081000 512
00007FD9EB70C340 99488 1 1 1 1 row cache hash 0000000076A38758 8208 perm 0 0000000076081000 512
00007FD9EB70C068 99495 1 1 1 1 row cache mutex 0000000076A38638 24 perm 0 0000000076081000 2
00007FD9EB70C000 99496 1 1 1 1 row cache mutex 0000000076A38610 40 perm 0 0000000076081000 1
00007FD9EB70BF98 99497 1 1 1 1 row cache hash 0000000076A385F0 32 perm 0 0000000076081000 1
00007FD9EB70BCC0 99504 1 1 1 1 row cache mutex 0000000076A380D8 1040 perm 0 0000000076081000 256
00007FD9EB70BC58 99505 1 1 1 1 row cache mutex 0000000076A368C8 6160 perm 0 0000000076081000 256
00007FD9EB70BBF0 99506 1 1 1 1 row cache hash 0000000076A358B8 4112 perm 0 0000000076081000 256
00007FD9EB70B918 99513 1 1 1 1 row cache mutex 0000000076A34FA0 2064 perm 0 0000000076081000 512
00007FD9EB70B8B0 99514 1 1 1 1 row cache mutex 0000000076A31F90 12304 perm 0 0000000076081000 512
00007FD9EB70B848 99515 1 1 1 1 row cache hash 0000000076A2FF80 8208 perm 0 0000000076081000 512
00007FD9EB70B570 99522 1 1 1 1 row cache mutex 0000000076A2FC68 528 perm 0 0000000076081000 128
00007FD9EB70B508 99523 1 1 1 1 row cache mutex 0000000076A2F058 3088 perm 0 0000000076081000 128
00007FD9EB70B4A0 99524 1 1 1 1 row cache hash 0000000076A2E848 2064 perm 0 0000000076081000 128
00007FD9EB70B1C8 99531 1 1 1 1 row cache mutex 0000000076A2DF30 2064 perm 0 0000000076081000 512
00007FD9EB70B160 99532 1 1 1 1 row cache mutex 0000000076A2AF20 12304 perm 0 0000000076081000 512
00007FD9EB70B0F8 99533 1 1 1 1 row cache hash 0000000076A28F10 8208 perm 0 0000000076081000 512
00007FD9EB70AE20 99540 1 1 1 1 row cache mutex 0000000076A285F8 2064 perm 0 0000000076081000 512
00007FD9EB70ADB8 99541 1 1 1 1 row cache mutex 0000000076A255E8 12304 perm 0 0000000076081000 512
00007FD9EB70AD50 99542 1 1 1 1 row cache hash 0000000076A235D8 8208 perm 0 0000000076081000 512
00007FD9EB70AA78 99549 1 1 1 1 row cache mutex 0000000076A22CC0 2064 perm 0 0000000076081000 512
00007FD9EB70AA10 99550 1 1 1 1 row cache mutex 0000000076A1FCB0 12304 perm 0 0000000076081000 512
00007FD9EB70A9A8 99551 1 1 1 1 row cache hash 0000000076A1DCA0 8208 perm 0 0000000076081000 512
00007FD9EB70A6D0 99558 1 1 1 1 row cache mutex 0000000076A1D388 2064 perm 0 0000000076081000 512
00007FD9EB70A668 99559 1 1 1 1 row cache mutex 0000000076A1A378 12304 perm 0 0000000076081000 512
00007FD9EB70A600 99560 1 1 1 1 row cache hash 0000000076A18368 8208 perm 0 0000000076081000 512
00007FD9EB70A328 99567 1 1 1 1 row cache mutex 0000000076A17A50 2064 perm 0 0000000076081000 512
00007FD9EB70A2C0 99568 1 1 1 1 row cache mutex 0000000076A14A40 12304 perm 0 0000000076081000 512
00007FD9EB70A258 99569 1 1 1 1 row cache hash 0000000076A12A30 8208 perm 0 0000000076081000 512
00007FD9EB709F80 99576 1 1 1 1 row cache mutex 0000000076A12118 2064 perm 0 0000000076081000 512
00007FD9EB709F18 99577 1 1 1 1 row cache mutex 0000000076A0F108 12304 perm 0 0000000076081000 512
00007FD9EB709EB0 99578 1 1 1 1 row cache hash 0000000076A0D0F8 8208 perm 0 0000000076081000 512
00007FD9EB709BD8 99585 1 1 1 1 row cache mutex 0000000076A0C7E0 2064 perm 0 0000000076081000 512
00007FD9EB709B70 99586 1 1 1 1 row cache mutex 0000000076A097D0 12304 perm 0 0000000076081000 512
00007FD9EB709B08 99587 1 1 1 1 row cache hash 0000000076A077C0 8208 perm 0 0000000076081000 512
00007FD9EB709830 99594 1 1 1 1 row cache mutex 0000000076A06EA8 2064 perm 0 0000000076081000 512
00007FD9EB7097C8 99595 1 1 1 1 row cache mutex 0000000076A03E98 12304 perm 0 0000000076081000 512
00007FD9EB709760 99596 1 1 1 1 row cache hash 0000000076A01E88 8208 perm 0 0000000076081000 512
00007FD9EB709488 99603 1 1 1 1 row cache mutex 0000000076A01570 2064 perm 0 0000000076081000 512
00007FD9EB709420 99604 1 1 1 1 row cache mutex 00000000769FE560 12304 perm 0 0000000076081000 512
00007FD9EB7093B8 99605 1 1 1 1 row cache hash 00000000769FC550 8208 perm 0 0000000076081000 512
00007FD9EB7090E0 99612 1 1 1 1 row cache mutex 00000000769EC438 65552 perm 0 0000000076081000 16384
00007FD9EB709078 99613 1 1 1 1 row cache mutex 000000007698C428 393232 perm 0 0000000076081000 16384
00007FD9EB709010 99614 1 1 1 1 row cache hash 000000007694C418 262160 perm 0 0000000076081000 16384
00007FD9EB708D38 99621 1 1 1 1 row cache mutex 000000007693C300 65552 perm 0 0000000076081000 16384
00007FD9EB708CD0 99622 1 1 1 1 row cache mutex 00000000768DC2F0 393232 perm 0 0000000076081000 16384
00007FD9EB708C68 99623 1 1 1 1 row cache hash 000000007689C2E0 262160 perm 0 0000000076081000 16384
00007FD9EB708990 99630 1 1 1 1 row cache mutex 000000007688C1C8 65552 perm 0 0000000076081000 16384
00007FD9EB708928 99631 1 1 1 1 row cache mutex 000000007682C1B8 393232 perm 0 0000000076081000 16384
00007FD9EB7088C0 99632 1 1 1 1 row cache hash 00000000767EC1A8 262160 perm 0 0000000076081000 16384
00007FD9EB7085E8 99639 1 1 1 1 row cache mutex 00000000767CC090 131088 perm 0 0000000076081000 32768
00007FD9EB708580 99640 1 1 1 1 row cache mutex 000000007670C080 786448 perm 0 0000000076081000 32768
00007FD9EB708518 99641 1 1 1 1 row cache hash 000000007668C070 524304 perm 0 0000000076081000 32768
00007FD9EB708240 99648 1 1 1 1 row cache mutex 000000007666BF58 131088 perm 0 0000000076081000 32768
00007FD9EB7081D8 99649 1 1 1 1 row cache mutex 00000000765ABF48 786448 perm 0 0000000076081000 32768
00007FD9EB708170 99650 1 1 1 1 row cache hash 000000007652BF38 524304 perm 0 0000000076081000 32768
00007FD9EB707E98 99657 1 1 1 1 row cache mutex 000000007650BE20 131088 perm 0 0000000076081000 32768
00007FD9EB707E30 99658 1 1 1 1 row cache mutex 000000007644BE10 786448 perm 0 0000000076081000 32768
00007FD9EB707DC8 99659 1 1 1 1 row cache hash 00000000763CBE00 524304 perm 0 0000000076081000 32768
00007FD9EB707AF0 99666 1 1 1 1 row cache mutex 00000000763ABCE8 131088 perm 0 0000000076081000 32768
00007FD9EB707A88 99667 1 1 1 1 row cache mutex 00000000762EBCD8 786448 perm 0 0000000076081000 32768
00007FD9EB707A20 99668 1 1 1 1 row cache hash 000000007626BCC8 524304 perm 0 0000000076081000 32768
00007FD9EB707748 99675 1 1 1 1 row cache mutex 000000007626B3B0 2064 perm 0 0000000076081000 512
00007FD9EB7076E0 99676 1 1 1 1 row cache mutex 00000000762683A0 12304 perm 0 0000000076081000 512
00007FD9EB707678 99677 1 1 1 1 row cache hash 0000000076266390 8208 perm 0 0000000076081000 512
00007FD9EB7073A0 99684 1 1 1 1 row cache mutex 0000000076265E78 1040 perm 0 0000000076081000 256
00007FD9EB707338 99685 1 1 1 1 row cache mutex 0000000076264668 6160 perm 0 0000000076081000 256
00007FD9EB7072D0 99686 1 1 1 1 row cache hash 0000000076263658 4112 perm 0 0000000076081000 256
00007FD9EB706FF8 99693 1 1 1 1 row cache mutex 0000000076263140 1040 perm 0 0000000076081000 256
00007FD9EB706F90 99694 1 1 1 1 row cache mutex 0000000076261930 6160 perm 0 0000000076081000 256
00007FD9EB706F28 99695 1 1 1 1 row cache hash 0000000076260920 4112 perm 0 0000000076081000 256
00007FD9EB706C50 99702 1 1 1 1 row cache mutex 0000000076260408 1040 perm 0 0000000076081000 256
00007FD9EB706BE8 99703 1 1 1 1 row cache mutex 000000007625EBF8 6160 perm 0 0000000076081000 256
00007FD9EB706B80 99704 1 1 1 1 row cache hash 000000007625DBE8 4112 perm 0 0000000076081000 256
00007FD9EB7068A8 99711 1 1 1 1 row cache mutex 000000007625D6D0 1040 perm 0 0000000076081000 256
00007FD9EB706840 99712 1 1 1 1 row cache mutex 000000007625BEC0 6160 perm 0 0000000076081000 256
00007FD9EB7067D8 99713 1 1 1 1 row cache hash 000000007625AEB0 4112 perm 0 0000000076081000 256
00007FD9EB706500 99720 1 1 1 1 row cache mutex 000000007625A598 2064 perm 0 0000000076081000 512
00007FD9EB706498 99721 1 1 1 1 row cache mutex 0000000076257588 12304 perm 0 0000000076081000 512
00007FD9EB706430 99722 1 1 1 1 row cache hash 0000000076255578 8208 perm 0 0000000076081000 512
00007FD9EB706158 99729 1 1 1 1 row cache mutex 0000000076255060 1040 perm 0 0000000076081000 256
00007FD9EB7060F0 99730 1 1 1 1 row cache mutex 0000000076253850 6160 perm 0 0000000076081000 256
00007FD9EB706088 99731 1 1 1 1 row cache hash 0000000076252840 4112 perm 0 0000000076081000 256
00007FD9EB705DB0 99738 1 1 1 1 row cache mutex 0000000076252328 1040 perm 0 0000000076081000 256
00007FD9EB705D48 99739 1 1 1 1 row cache mutex 0000000076250B18 6160 perm 0 0000000076081000 256
00007FD9EB705CE0 99740 1 1 1 1 row cache hash 000000007624FB08 4112 perm 0 0000000076081000 256
00007FD9EB705A08 99747 1 1 1 1 row cache mutex 000000007624F5F0 1040 perm 0 0000000076081000 256
00007FD9EB7059A0 99748 1 1 1 1 row cache mutex 000000007624DDE0 6160 perm 0 0000000076081000 256
00007FD9EB705938 99749 1 1 1 1 row cache hash 000000007624CDD0 4112 perm 0 0000000076081000 256
00007FD9EB705660 99756 1 1 1 1 row cache mutex 000000007624C4B8 2064 perm 0 0000000076081000 512
00007FD9EB7055F8 99757 1 1 1 1 row cache mutex 00000000762494A8 12304 perm 0 0000000076081000 512
00007FD9EB705590 99758 1 1 1 1 row cache hash 0000000076247498 8208 perm 0 0000000076081000 512
00007FD9EB7052B8 99765 1 1 1 1 row cache mutex 0000000076246F80 1040 perm 0 0000000076081000 256
00007FD9EB705250 99766 1 1 1 1 row cache mutex 0000000076245770 6160 perm 0 0000000076081000 256
00007FD9EB7051E8 99767 1 1 1 1 row cache hash 0000000076244760 4112 perm 0 0000000076081000 256
00007FD9EB704F10 99774 1 1 1 1 row cache mutex 0000000076244248 1040 perm 0 0000000076081000 256
00007FD9EB704EA8 99775 1 1 1 1 row cache mutex 0000000076242A38 6160 perm 0 0000000076081000 256
00007FD9EB704E40 99776 1 1 1 1 row cache hash 0000000076241A28 4112 perm 0 0000000076081000 256
00007FD9EB704B68 99783 1 1 1 1 row cache mutex 0000000076221910 131088 perm 0 0000000076081000 32768
00007FD9EB704B00 99784 1 1 1 1 row cache mutex 0000000076161900 786448 perm 0 0000000076081000 32768
00007FD9EB704A98 99785 1 1 1 1 row cache hash 00000000760E18F0 524304 perm 0 0000000076081000 32768
00007FD9EB7047C0 99792 1 1 1 1 row cache mutex 00000000760E13D8 1040 perm 0 0000000076081000 256
00007FD9EB704758 99793 1 1 1 1 row cache mutex 00000000760DFBC8 6160 perm 0 0000000076081000 256
00007FD9EB7046F0 99794 1 1 1 1 row cache hash 00000000760DEBB8 4112 perm 0 0000000076081000 256
00007FD9EB704418 99801 1 1 1 1 row cache mutex 00000000760DE6A0 1040 perm 0 0000000076081000 256
00007FD9EB7043B0 99802 1 1 1 1 row cache mutex 00000000760DCE90 6160 perm 0 0000000076081000 256
00007FD9EB704348 99803 1 1 1 1 row cache hash 00000000760DBE80 4112 perm 0 0000000076081000 256
00007FD9EB704070 99810 1 1 1 1 row cache mutex 00000000760DBD28 80 perm 0 0000000076081000 16
00007FD9EB704008 99811 1 1 1 1 row cache mutex 00000000760DBB98 400 perm 0 0000000076081000 16
00007FD9EB703FA0 99812 1 1 1 1 row cache hash 00000000760DBA88 272 perm 0 0000000076081000 16
00007FD9EB703CC8 99819 1 1 1 1 row cache mutex 00000000760DB870 272 perm 0 0000000076081000 64
00007FD9EB703C60 99820 1 1 1 1 row cache mutex 00000000760DB260 1552 perm 0 0000000076081000 64
00007FD9EB703BF8 99821 1 1 1 1 row cache hash 00000000760DAE50 1040 perm 0 0000000076081000 64
00007FD9EB703920 99828 1 1 1 1 row cache mutex 00000000760DAC38 272 perm 0 0000000076081000 64
00007FD9EB7038B8 99829 1 1 1 1 row cache mutex 00000000760DA628 1552 perm 0 0000000076081000 64
00007FD9EB703850 99830 1 1 1 1 row cache hash 00000000760DA218 1040 perm 0 0000000076081000 64
00007FD9EB703578 99837 1 1 1 1 row cache mutex 00000000760D9900 2064 perm 0 0000000076081000 512
00007FD9EB703510 99838 1 1 1 1 row cache mutex 00000000760D68F0 12304 perm 0 0000000076081000 512
00007FD9EB7034A8 99839 1 1 1 1 row cache hash 00000000760D48E0 8208 perm 0 0000000076081000 512
00007FD9EB7031D0 99846 1 1 1 1 row cache mutex 00000000760D3FC8 2064 perm 0 0000000076081000 512
00007FD9EB703168 99847 1 1 1 1 row cache mutex 00000000760D0FB8 12304 perm 0 0000000076081000 512
00007FD9EB703100 99848 1 1 1 1 row cache hash 00000000760CEFA8 8208 perm 0 0000000076081000 512
00007FD9EB702E28 99855 1 1 1 1 row cache mutex 00000000760CDE90 4112 perm 0 0000000076081000 1024
00007FD9EB702DC0 99856 1 1 1 1 row cache mutex 00000000760C7E80 24592 perm 0 0000000076081000 1024
00007FD9EB702D58 99857 1 1 1 1 row cache hash 00000000760C3E70 16400 perm 0 0000000076081000 1024
00007FD9EB702A80 99864 1 1 1 1 row cache mutex 00000000760C3958 1040 perm 0 0000000076081000 256
00007FD9EB702A18 99865 1 1 1 1 row cache mutex 00000000760C2148 6160 perm 0 0000000076081000 256
00007FD9EB7029B0 99866 1 1 1 1 row cache hash 00000000760C1138 4112 perm 0 0000000076081000 256
00007FD9EB7026D8 99873 1 1 1 1 row cache mutex 0000000076081020 262160 perm 0 0000000076081000 65536
186 rows selected.
--//INDX从97914开始,3个一组,最后1列BUCKETS都是一样.
--//INDX= 97906, 97907 与最后1条记录输出(INDX=99873)，最后1列BUCKETS=65536.
--//单独拿cache_name='dc_objects' chunk看看，注我的测试环境每次重启该地址不会变化的，我没有修改sga相关配置:
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS
---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ----------
00007FD9EB7338A8 97941 1 1 1 1 row cache mutex 00000000779849A8 131088 perm 0 0000000077004000 32768
00007FD9EB733840 97942 1 1 1 1 row cache mutex 00000000778C4998 786448 perm 0 0000000077004000 32768
00007FD9EB7337D8 97943 1 1 1 1 row cache hash 0000000077844988 524304 perm 0 0000000077004000 32768
--//以hash=6777,6778为例定位地址:
--//row cache mutex:
0x00000000779849A8+16+4*6777 = 0x7798b39c
0x00000000778C4998+16+24*6777 = 0x778ec500
0x00000000778C4998+16+24*6778 = 0x778ec518
--//row cache hash:
0x0000000077844988+16+16*6777 = 0x7785f128
0x0000000077844988+16+16*6778 = 0x7785f138
SYS@book> @ dc/dc_objects "hash in (6777,6778) "
USERNAME DC_OBJ_NAME KEY_STR_LEN HASH_HEX INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT LOCK_MODE LOCK_REQUEST TXN SADDR INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID
-------- ----------- ----------- -------- ------ ------ ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ---------------- ----------------- ----------------- -- -------- -------- ------
KOTTBX$ 7 0x1a7a 2789 6778 000000006FB7A870 8 dc_objects Y 0 0 00 00 0 0 00 00 2
--//当前hash=6778有1个对象，不理解这个对象CON_ID=2，CON_NAME=PDB$SEED ，为什么启动时会访问加载。
--//username显示null 的原因是cdb_users视图没有con_id=2的记录。
--//使用odadebug peek查看:
SYS@book> @ opeek 00000000778EC500 24 0
[0778EC500, 0778EC518) = 00000000 00000000 00000000 00000000 08001A79 00000000
SYS@book> @ opeek 00000000778EC518 24 0
[0778EC518, 0778EC530) = 00000000 00000000 0000000B 00000000 08001A7A 00000000
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 00000002 0100000E
~~~~~~~~
--//当前有1个对象在hash=6778.
SYS@book> @ opeek 7785f128 16 0
[07785F128, 07785F138) = 7785F128 00000000 7785F128 00000000
--//首尾地址0x7785F128，等于opeek的地址,也就是hash=6777的没有任何对象。
SYS@book> @ opeek 7785f138 16 0
[07785F138, 07785F148) = 6FB7AC60 00000000 6FB7AC60 00000000
--//hash=6778有1个对象，row cache hash该位置记录的信息是0x6FB7AC60。
SYS@book> @ fchaz 6FB7AC60
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006FB7A840 1 1 KQR PO 1328 recr 584 00 000000006FB7A840 000000006FB7AD70
--//偏移0x6FB7AC60-0x6FB7A840 = 0x420 = 1056
--//有点意外的时row cache hash chunk里面保存的不是row cache对象的句柄地址。与开头偏移0x420，与句柄地址偏移0x420-0x30 = 0x3f0 = 1008
SYS@book> @ dc/dc_objects "hash in (6777,6778) "
USERNAME DC_OBJ_NAME KEY_STR_LEN HASH_HEX INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT LOCK_MODE LOCK_REQUEST TXN SADDR INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID
-------- ----------- ----------- -------- ------ ------ ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ---------------- ----------------- ----------------- -- -------- -------- ------
KOTTBX$ 7 0x1a7a 2795 6778 000000006FB7A870 8 dc_objects Y 0 0 00 00 0 0 00 00 2
--//ADDRESS=000000006FB7A870
SYS@book> @ fchaz 000000006FB7A870
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006FB7A840 1 1 KQR PO 1328 recr 584 00 000000006FB7A840 000000006FB7AD70
--//正好在相同chunk里面。
--//0x000000006FB7A870-0x000000006FB7A840 = 0x30,句柄偏移开始位置正好48字节。
SYS@book> @ opeek 6FB7AC60 48 0
[06FB7AC60, 06FB7AC90) = 7785F138 00000000 7785F138 00000000 00000001 6A0C42D5 6FB7A870 00000000 778876E8 00000000 778876E8 00000000
~~~~~~~~~~~~~~~~~ +++++++++++++++++
--//查看地址0x6FB7AC60开头的16字节，0x7785F138不是正好指向hash=6778的row cache hash的对应地址吗？这样正好形成一个环。
3.深入分析：
--//执行调用zz6.txt脚本前的情况：
SYS@book> @ opeek 7785f138 16 0
[07785F138, 07785F148) = 6FB7AC60 00000000 6FB7AC60 00000000
--//如果存在多个对象呢？
$ zzdate;seq 1 | xargs -IQ -P 1 sqlplus -s -l '/ as sysdba' @zz6.txt 1e7 test1 Q > /dev/null ; zzdate
trunc(sysdate)+09/24+25/1440+48/86400 1786497948.773474937
...
--//这样根据前面测试在hash=6777，6778分别增加1个对象。
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 01000002 02000012
--//hash=6777有1个对象，hash=6778有2个对象.
SYS@book> @ dc/dc_objects "hash in (6777,6778) "
USERNAME DC_OBJ_NAME KEY_STR_LEN HASH_HEX INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT LOCK_MODE LOCK_REQUEST TXN SADDR INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID
-------------------- -------------------------------- ----------- -------- ------ ------ ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ---------------- ----------------- ----------------- -- -------- -------- ------
SYS 0 0x1a79 6444 6777 0000000062E1B668 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 6445 6778 0000000062E0E988 11 dc_objects N 0 0 00 00 0 0 00 00 1
KOTTBX$ 7 0x1a7a 2795 6778 000000006FB7A870 8 dc_objects Y 0 0 00 00 0 0 00 00 2
--//再看看看hash=6778的情况：
--//执行调用zz6.txt脚本后的情况：
SYS@book> @ opeek 7785f138 16 0
[07785F138, 07785F148) = 62E0ED98 00000000 6FB7AC60 00000000
~~~~~~~~~~~~~~~~~
--//相当于增加一个对象的地址。对应就是@ dc/dc_objects "hash in (6777,6778) " 的ADDRESS=0000000062E0E988的对象，不展开分析。
--//顺着0x62E0ED98地址查看：
SYS@book> @ opeek 62E0ED98 16 0
[062E0ED98, 062E0EDB8) = 6FB7AC60 00000000 7785F138 00000000
SYS@book> @ opeek 6FB7AC60 16 0
[06FB7AC60, 06FB7AC80) = 7785F138 00000000 62E0ED98 00000000
SYS@book> @ opeek 7785F138 16 0
[07785F138, 07785F148) = 62E0ED98 00000000 6FB7AC60 00000000
--//正好形成1个环。
--//再看看看hash=6777的情况：
SYS@book> @ opeek 7785f128 16 0
[07785F128, 07785F138) = 62E1BA78 00000000 62E1BA78 00000000
SYS@book> @ opeek 62E1BA78 16 0
[062E1BA78, 062E1BA88) = 7785F128 00000000 7785F128 00000000
--//正好也是形成1个环。
4.疑问：
--//剩下还有一个问题无法解决，假设一个表scott.dept，对象类型dc_objects，oracle如何确定那些chunk上寻找row cache mutex以及
--//row cache hash呢? 必须还有chunk记录这些信息.
--//另外写一篇blog分析.
浙公网安备 33010602011771号