[20260810]简单探究row cache mutex.txt
[20260810]简单探究row cache mutex.txt
--//昨天的测试使用mutexprofz.sql脚本输出有mutex_addr地址，不知道是否巧合2个hash相差1，对row cache mutex做一个简单探究。
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.测试脚本：
$ cat zz6.txt
set verify off
variable v_method varchar2(30);
exec :v_method := '&&2';
declare
v_sid number;
v_d date;
v varchar2(30);
begin
for i in 1 .. &&1 loop
select /*+ &3 */ sid into v_sid from v$mystat where rownum=1;
end loop;
end ;
/
quit
3.测试：
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l '/ as sysdba' @zz6.txt 1e6 test1 Q > /dev/null ; zzdate
trunc(sysdate)+16/24+39/1440+31/86400 1786351171.063864930
...
SYS@book> @mutexprofz idn,maddr,idnhex,hash,loc "ts >= trunc(sysdate)+16/24+39/1440+31/86400 and mutex_type='Row Cache'"
-- MutexProf by Tanel Poder (http://www.tanelpoder.com)
-- Showing profile of top 50 sleeps...
-- column info : id idn hash hash_value=>hash_value ts=>sleep_timestamp
-- req=>requesting_session blk=>blocking_session val=>mutex_value maddr=>mutex_addr
SUM_SLEEPS GETS_DIFF MUTEX_TYPE IDN mutex_addr IDNHEX HASH GET_LOCATION SQL_ID OBJECT_NAME
---------- -------------- --------------- ---------- -------------------- --------- ---------- --------------------------------- ------------- -----------------
25 584508 Row Cache 134224506 00000000778EC518 08001A7A [17] kqrCreateUsingSecondaryKey (name not found)
24 42192 Row Cache 134224506 00000000778EC518 08001A7A [19] kqrpre (name not found)
17 22636895 Row Cache 0 000000006433A370 00000000 [10] kqreqd (name not found)
15 128596 Row Cache 134224505 00000000778EC500 08001A79 [17] kqrCreateUsingSecondaryKey (name not found)
14 110952 Row Cache 134224505 00000000778EC500 08001A79 [19] kqrpre (name not found)
8 7793640 Row Cache 0 00000000646AEDA8 00000000 [10] kqreqd (name not found)
8 15722303 Row Cache 0 000000006455BA80 00000000 [10] kqreqd (name not found)
4 5959059 Row Cache 0 00000000646CF670 00000000 [10] kqreqd (name not found)
1 Row Cache 0 00000000646CF140 00000000 [10] kqreqd (name not found)
9 rows selected.
--//注意看IDN=134224505，134224506转换16进制，对应08001A79，08001A7A(不知道是否巧合2者差1)。
--//猜测前面的0800(反转0008或者就是08)对应cache_id.这样后面 0x1a79，0x1a7a定义hash值。
--//0x1a79 = 6777 0x1a7a = 6778.
--//mutex_addr 记录的00000000778EC500(IDNHEX=08001A79)，00000000778EC518(IDNHEX=08001A7A)
SYS@book> @ fchaz 00000000778EC500
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 00000000778C4998 1 1 row cache mutex 786448 perm 0 0000000077004000 00000000778C4998 00000000779849A8
SYS@book> @ fchaz 00000000778EC518
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 00000000778C4998 1 1 row cache mutex 786448 perm 0 0000000077004000 00000000778C4998 00000000779849A8
--//两者都在同一个chunk里面。
SYS@book> @ calc 0x00000000778EC518 - 0x00000000778EC500
DEC HEX
----------------------------------- --------------------
24.000000 18
--//正好相差24字节。
--//以前分析过library cache bucket mutex
--// 0- 7 字节是muext的值。
--// 8-11 字节是mutex gets的数量。
--//12-15 字节是mutex sleep的数量。
--//16-19 字节是Bucket桶号。
--//20-23 字节是转储看到的6，而opeek看都是0，表示什么不是很清楚。
--//每个mutex结构体占用24字节。
SYS@book> @ opeek 00000000778EC500 24 0
[0778EC500, 0778EC518) = 00000000 00000000 0AE3691C 00013D89 08001A79 00000000
~~~~~~~~
SYS@book> @ opeek 00000000778EC518 24 0
[0778EC518, 0778EC530) = 00000000 00000000 0AE08F70 0001421C 08001A7A 00000000
SYS@book> @ opeek 00000000778EC530 24 0
[0778EC530, 0778EC548) = 00000000 00000000 00000000 00000000 08001A7B 00000000
--//下划线正好等于bucket桶号。
--//KSMCHSIZ=786448,减去段头16字节，(786448-16)/24 = 32768,说明这个chunk容纳32768个bucket。
--//而且可以确定访问dc_objects=8的row cache mutex都是使用该chunk。
--//注：实际上查询v$rowcache_parent看到的cache#=11，说明cache#11也是使用该chunk。
--//补充测试：
SYS@book> select rownum cache# from dual connect by level <=65 minus select cache# from v$rowcache where type='PARENT';
CACHE#
----------
7
42
51
11
SYS@book> select distinct cache#,type,parameter from v$rowcache where cache# in (7,11,42,51);
CACHE# TYPE PARAMETER
---------- ------------------------------ ------------------------------
7 SUBORDINATE dc_users
--//存在 CACHE#=7，TYPE=SUBORDINATE，PARAMETER=dc_users。在type='PARENT'，PARAMETER=dc_users，cache#=10.
SYS@book> select distinct cache#,cache_name from v$rowcache_parent where cache# in (7,11,42,51);
CACHE# CACHE_NAME
---------- --------------
11 dc_objects
--//有一部分CACHE_NAME=dc_objects,cache#=11,似乎这些对象在v$fixed_table以及系统视图的定义。
SYS@book> select distinct cache#,SUBCACHE# ,SUBCACHE_NAME from v$rowcache_subordinate where cache# in (7,11,42,51) or SUBCACHE# in (7,11,42,51);
CACHE# SUBCACHE# SUBCACHE_NAME
---------- ---------- ----------------------------------------------------------------
7 1 dc_user_grants
7 3 user's audit policies
7 5 CONTAINER_DATA_Attributes
########################
SYS@book> SELECT cache# ,type ,parameter ,count FROM v$rowcache where type='PARENT' order by 1;
CACHE# TYPE PARAMETER COUNT
---------- ------- ---------------------------------------- ----------
0 PARENT dc_tablespaces 13
1 PARENT dc_free_extents 0
2 PARENT dc_segments 1315
3 PARENT dc_rollback_segments 77
4 PARENT dc_used_extents 0
5 PARENT dc_tablespace_quotas 0
6 PARENT dc_files 0
8 PARENT dc_objects 2687
9 PARENT qmrc_cache_entries 0
10 PARENT dc_users 9
12 PARENT dc_constraints 0
13 PARENT dc_sequences 7
14 PARENT dc_profiles 1
15 PARENT dc_props 8
16 PARENT dc_histogram_defs 7875
17 PARENT dc_global_oids 91
18 PARENT dc_outlines 0
19 PARENT dc_table_scns 0
20 PARENT rule_info 0
21 PARENT rule_or_piece 0
22 PARENT dc_awr_control 2
23 PARENT dc_qmc_ldap_cache_entries 0
24 PARENT outstanding_alerts 2
25 PARENT SMO rowcache 0
26 PARENT qmtmrctq_cache_entries 0
27 PARENT qmtmrcin_cache_entries 0
28 PARENT qmtmrctn_cache_entries 0
29 PARENT qmtmrcip_cache_entries 0
30 PARENT qmtmrctp_cache_entries 0
31 PARENT qmtmrciq_cache_entries 0
32 PARENT kqlsubheap_object 0
33 PARENT Unused 0
34 PARENT extensible security principal in 0
35 PARENT triton security name to ID 0
36 PARENT Unused 0
37 PARENT Unused 0
38 PARENT XS security class privilege 0
39 PARENT extensible security midtier cach 0
40 PARENT sch_lj_objs 0
41 PARENT sch_lj_oids 10
43 PARENT AV row cache 1 0
44 PARENT AV row cache 2 0
45 PARENT AV row cache 3 0
46 PARENT Rule Set Cache 0
47 PARENT realm cache 0
48 PARENT Command rule cache 0
49 PARENT Realm Object cache 0
50 PARENT qmemod_cache_entries 0
52 PARENT qmc_app_cache_entries 0
53 PARENT qmc_app_cache_entries 0
54 PARENT dc_sql_prs_errors 0
55 PARENT DV Auth Cache 0
56 PARENT dc_cdbfiles 0
57 PARENT dc_cdbservices 0
58 PARENT dc_pdbdba 0
59 PARENT dc_pdbstates 0
60 PARENT dc_cdbprops 0
61 PARENT dc_statistics_status 0
62 PARENT dc_realtime_colst 0
63 PARENT dc_realtime_tabst 0
64 PARENT key_shard 0
65 PARENT dc_column_model_to_tab 0
62 rows selected.
--//有62条记录。65-4+1 = 62，从0开始计数
--//查询x$ksmsp视图：
SYS@book> select a.*,(KSMCHSIZ-16)/24 buckets from x$ksmsp a where KSMCHCOM='row cache mutex' and mod(KSMCHSIZ-16,24)=0 ;
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS
---------------- ------ ---------- ------ ---------- ---------- ----------------- ---------------- ---------- -------- ---------- ---------------- ----------
00007FE70C5A5798 96399 1 1 1 1 row cache mutex 0000000077AD7C78 1572880 perm 0 0000000077004000 65536
00007FE70C5A53F0 96408 1 1 1 1 row cache mutex 00000000779D6D40 3088 perm 0 0000000077004000 128
00007FE70C5A5048 96417 1 1 1 1 row cache mutex 00000000779BA408 98320 perm 0 0000000077004000 4096
00007FE70C5A4CA0 96426 1 1 1 1 row cache mutex 00000000779A6AD0 12304 perm 0 0000000077004000 512
00007FE70C5A48F8 96435 1 1 1 1 row cache mutex 00000000778C4998 786448 perm 0 0000000077004000 32768
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~--//dc_objects
00007FE70C5A4550 96444 1 1 1 1 row cache mutex 0000000077764860 786448 perm 0 0000000077004000 32768
00007FE70C5A41A8 96453 1 1 1 1 row cache mutex 00000000776E4020 1552 perm 0 0000000077004000 64
00007FE70C5A3E00 96462 1 1 1 1 row cache mutex 00000000776E1EE8 6160 perm 0 0000000077004000 256
00007FE70C5A3A58 96471 1 1 1 1 row cache mutex 00000000776E0A30 784 perm 0 0000000077004000 32
00007FE70C5A36B0 96480 1 1 1 1 row cache mutex 00000000776006F8 786448 perm 0 0000000077004000 32768
00007FE70C5A3308 96489 1 1 1 1 row cache mutex 000000007757E9C0 6160 perm 0 0000000077004000 256
00007FE70C5A2F60 96498 1 1 1 1 row cache mutex 000000007757BC88 6160 perm 0 0000000077004000 256
00007FE70C5A2BB8 96507 1 1 1 1 row cache mutex 000000007750AB50 393232 perm 0 0000000077004000 16384
--//KSMCHPAR=0000000077004000,共有13个。
00007FE70C57E9C8 97925 1 1 1 1 row cache mutex 0000000076A463B8 1552 perm 0 0000000076081000 64
00007FE70C57E620 97934 1 1 1 1 row cache mutex 0000000076A45780 1552 perm 0 0000000076081000 64
00007FE70C57E278 97943 1 1 1 1 row cache mutex 0000000076A44B48 1552 perm 0 0000000076081000 64
00007FE70C57DED0 97952 1 1 1 1 row cache mutex 0000000076A43F10 1552 perm 0 0000000076081000 64
00007FE70C57DB28 97961 1 1 1 1 row cache mutex 0000000076A41DD8 6160 perm 0 0000000076081000 256
00007FE70C57D780 97970 1 1 1 1 row cache mutex 0000000076A3F0A0 6160 perm 0 0000000076081000 256
00007FE70C57D3D8 97979 1 1 1 1 row cache mutex 0000000076A3A768 12304 perm 0 0000000076081000 512
00007FE70C57D030 97988 1 1 1 1 row cache mutex 0000000076A38610 40 perm 0 0000000076081000 1
00007FE70C57CC88 97997 1 1 1 1 row cache mutex 0000000076A368C8 6160 perm 0 0000000076081000 256
00007FE70C57C8E0 98006 1 1 1 1 row cache mutex 0000000076A31F90 12304 perm 0 0000000076081000 512
00007FE70C57C538 98015 1 1 1 1 row cache mutex 0000000076A2F058 3088 perm 0 0000000076081000 128
00007FE70C57C190 98024 1 1 1 1 row cache mutex 0000000076A2AF20 12304 perm 0 0000000076081000 512
00007FE70C57BDE8 98033 1 1 1 1 row cache mutex 0000000076A255E8 12304 perm 0 0000000076081000 512
00007FE70C57BA40 98042 1 1 1 1 row cache mutex 0000000076A1FCB0 12304 perm 0 0000000076081000 512
00007FE70C57B698 98051 1 1 1 1 row cache mutex 0000000076A1A378 12304 perm 0 0000000076081000 512
00007FE70C57B2F0 98060 1 1 1 1 row cache mutex 0000000076A14A40 12304 perm 0 0000000076081000 512
00007FE70C57AF48 98069 1 1 1 1 row cache mutex 0000000076A0F108 12304 perm 0 0000000076081000 512
00007FE70C57ABA0 98078 1 1 1 1 row cache mutex 0000000076A097D0 12304 perm 0 0000000076081000 512
00007FE70C57A7F8 98087 1 1 1 1 row cache mutex 0000000076A03E98 12304 perm 0 0000000076081000 512
00007FE70C57A450 98096 1 1 1 1 row cache mutex 00000000769FE560 12304 perm 0 0000000076081000 512
00007FE70C57A0A8 98105 1 1 1 1 row cache mutex 000000007698C428 393232 perm 0 0000000076081000 16384
00007FE70C579D00 98114 1 1 1 1 row cache mutex 00000000768DC2F0 393232 perm 0 0000000076081000 16384
00007FE70C579958 98123 1 1 1 1 row cache mutex 000000007682C1B8 393232 perm 0 0000000076081000 16384
00007FE70C5795B0 98132 1 1 1 1 row cache mutex 000000007670C080 786448 perm 0 0000000076081000 32768
00007FE70C579208 98141 1 1 1 1 row cache mutex 00000000765ABF48 786448 perm 0 0000000076081000 32768
00007FE70C578E60 98150 1 1 1 1 row cache mutex 000000007644BE10 786448 perm 0 0000000076081000 32768
00007FE70C578AB8 98159 1 1 1 1 row cache mutex 00000000762EBCD8 786448 perm 0 0000000076081000 32768
00007FE70C578710 98168 1 1 1 1 row cache mutex 00000000762683A0 12304 perm 0 0000000076081000 512
00007FE70C578368 98177 1 1 1 1 row cache mutex 0000000076264668 6160 perm 0 0000000076081000 256
00007FE70C577FC0 98186 1 1 1 1 row cache mutex 0000000076261930 6160 perm 0 0000000076081000 256
00007FE70C577C18 98195 1 1 1 1 row cache mutex 000000007625EBF8 6160 perm 0 0000000076081000 256
00007FE70C577870 98204 1 1 1 1 row cache mutex 000000007625BEC0 6160 perm 0 0000000076081000 256
00007FE70C5774C8 98213 1 1 1 1 row cache mutex 0000000076257588 12304 perm 0 0000000076081000 512
00007FE70C577120 98222 1 1 1 1 row cache mutex 0000000076253850 6160 perm 0 0000000076081000 256
00007FE70C576D78 98231 1 1 1 1 row cache mutex 0000000076250B18 6160 perm 0 0000000076081000 256
00007FE70C5769D0 98240 1 1 1 1 row cache mutex 000000007624DDE0 6160 perm 0 0000000076081000 256
00007FE70C576628 98249 1 1 1 1 row cache mutex 00000000762494A8 12304 perm 0 0000000076081000 512
00007FE70C576280 98258 1 1 1 1 row cache mutex 0000000076245770 6160 perm 0 0000000076081000 256
00007FE70C575ED8 98267 1 1 1 1 row cache mutex 0000000076242A38 6160 perm 0 0000000076081000 256
00007FE70C575B30 98276 1 1 1 1 row cache mutex 0000000076161900 786448 perm 0 0000000076081000 32768
00007FE70C575788 98285 1 1 1 1 row cache mutex 00000000760DFBC8 6160 perm 0 0000000076081000 256
00007FE70C5753E0 98294 1 1 1 1 row cache mutex 00000000760DCE90 6160 perm 0 0000000076081000 256
00007FE70C575038 98303 1 1 1 1 row cache mutex 00000000760DBB98 400 perm 0 0000000076081000 16
00007FE70C574C90 98312 1 1 1 1 row cache mutex 00000000760DB260 1552 perm 0 0000000076081000 64
00007FE70C5748E8 98321 1 1 1 1 row cache mutex 00000000760DA628 1552 perm 0 0000000076081000 64
00007FE70C574540 98330 1 1 1 1 row cache mutex 00000000760D68F0 12304 perm 0 0000000076081000 512
00007FE70C574198 98339 1 1 1 1 row cache mutex 00000000760D0FB8 12304 perm 0 0000000076081000 512
00007FE70C573DF0 98348 1 1 1 1 row cache mutex 00000000760C7E80 24592 perm 0 0000000076081000 1024
00007FE70C573A48 98357 1 1 1 1 row cache mutex 00000000760C2148 6160 perm 0 0000000076081000 256
62 rows selected.
--//正好62条，与前面执行SELECT cache# ,type ,parameter ,count FROM v$rowcache where type='PARENT' order by 1;返回记录数
--//量正好一致。注意看BUCKETS列正好都是2^N，也就是根据对象的多少选择合适取模的数量。比如dc_objects就是32768。
--//简单拿最后一条记录分析：
SYS@book> @ fchaz 00000000760C2148
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 00000000760C2148 1 1 row cache mutex 6160 perm 0 0000000076081000 00000000760C2148 00000000760C3958
--//有16字节段头。 0x00000000760C2148+16=0x00000000760C2158。
SYS@book> @ opeek 00000000760C2158 24 0
[0760C2158, 0760C2170) = 00000000 00000000 00000000 00000000 36000000 00000000
SYS@book> @ opeek 00000000760C2170 24 0
[0760C2170, 0760C2188) = 00000000 00000000 00000000 00000000 36000001 00000000
--//0x36 = 54，对应的数据字典名称就是dc_sql_prs_errors。
SYS@book> @ fchaz 00000000760C7E80
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 00000000760C7E80 1 1 row cache mutex 24592 perm 0 0000000076081000 00000000760C7E80 00000000760CDE90
SYS@book> @ opeek 00000000760C7E90 24 0
[0760C7E90, 0760C7EA8) = 00000000 00000000 00000000 00000000 20000000 00000000
SYS@book> @ opeek 00000000760C7EA8 24 0
[0760C7EA8, 0760C7EC0) = 00000000 00000000 00000000 00000000 20000001 00000000
--//0x20 = 32,对应的数据字典名称就是kqlsubheap_object。
--//看看是否能对上cache#.
SYS@book> select 'oradebug peek 0x'||KSMCHPTR||' 36 0' c40 from x$ksmsp a where KSMCHCOM='row cache mutex' and mod(KSMCHSIZ-16,24)=0 ;
C40
----------------------------------------
oradebug peek 0x0000000077AD7C78 36 0
oradebug peek 0x00000000779D6D40 36 0
oradebug peek 0x00000000779BA408 36 0
oradebug peek 0x00000000779A6AD0 36 0
oradebug peek 0x00000000778C4998 36 0
oradebug peek 0x0000000077764860 36 0
oradebug peek 0x00000000776E4020 36 0
oradebug peek 0x00000000776E1EE8 36 0
oradebug peek 0x00000000776E0A30 36 0
oradebug peek 0x00000000776006F8 36 0
oradebug peek 0x000000007757E9C0 36 0
oradebug peek 0x000000007757BC88 36 0
oradebug peek 0x000000007750AB50 36 0
oradebug peek 0x0000000076A463B8 36 0
oradebug peek 0x0000000076A45780 36 0
oradebug peek 0x0000000076A44B48 36 0
oradebug peek 0x0000000076A43F10 36 0
oradebug peek 0x0000000076A41DD8 36 0
oradebug peek 0x0000000076A3F0A0 36 0
oradebug peek 0x0000000076A3A768 36 0
oradebug peek 0x0000000076A38610 36 0
oradebug peek 0x0000000076A368C8 36 0
oradebug peek 0x0000000076A31F90 36 0
oradebug peek 0x0000000076A2F058 36 0
oradebug peek 0x0000000076A2AF20 36 0
oradebug peek 0x0000000076A255E8 36 0
oradebug peek 0x0000000076A1FCB0 36 0
oradebug peek 0x0000000076A1A378 36 0
oradebug peek 0x0000000076A14A40 36 0
oradebug peek 0x0000000076A0F108 36 0
oradebug peek 0x0000000076A097D0 36 0
oradebug peek 0x0000000076A03E98 36 0
oradebug peek 0x00000000769FE560 36 0
oradebug peek 0x000000007698C428 36 0
oradebug peek 0x00000000768DC2F0 36 0
oradebug peek 0x000000007682C1B8 36 0
oradebug peek 0x000000007670C080 36 0
oradebug peek 0x00000000765ABF48 36 0
oradebug peek 0x000000007644BE10 36 0
oradebug peek 0x00000000762EBCD8 36 0
oradebug peek 0x00000000762683A0 36 0
oradebug peek 0x0000000076264668 36 0
oradebug peek 0x0000000076261930 36 0
oradebug peek 0x000000007625EBF8 36 0
oradebug peek 0x000000007625BEC0 36 0
oradebug peek 0x0000000076257588 36 0
oradebug peek 0x0000000076253850 36 0
oradebug peek 0x0000000076250B18 36 0
oradebug peek 0x000000007624DDE0 36 0
oradebug peek 0x00000000762494A8 36 0
oradebug peek 0x0000000076245770 36 0
oradebug peek 0x0000000076242A38 36 0
oradebug peek 0x0000000076161900 36 0
oradebug peek 0x00000000760DFBC8 36 0
oradebug peek 0x00000000760DCE90 36 0
oradebug peek 0x00000000760DBB98 36 0
oradebug peek 0x00000000760DB260 36 0
oradebug peek 0x00000000760DA628 36 0
oradebug peek 0x00000000760D68F0 36 0
oradebug peek 0x00000000760D0FB8 36 0
oradebug peek 0x00000000760C7E80 36 0
oradebug peek 0x00000000760C2148 36 0
62 rows selected.
--//执行如下.
spool cache#.txt
oradebug setmypid
--//拷贝粘贴以上内容
...
spool off
$ head cache#.txt
SYS@book> oradebug peek 0x0000000077AD7C78 36 0
[077AD7C78, 077AD7C9C) = 00180011 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 10000000
SYS@book> oradebug peek 0x00000000779D6D40 36 0
[0779D6D40, 0779D6D64) = 00000C11 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 0D000000
SYS@book> oradebug peek 0x00000000779BA408 36 0
[0779BA408, 0779BA42C) = 00018011 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 0C000000
SYS@book> oradebug peek 0x00000000779A6AD0 36 0
[0779A6AD0, 0779A6AF4) = 00003011 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 11000000
SYS@book> oradebug peek 0x00000000778C4998 36 0
[0778C4998, 0778C49BC) = 000C0011 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 08000000
--//最后1个字段就是bucket 0，其前面2个数字对应的就是cache#。
$ grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}' | head -13 | paste -sd" "
16 13 12 17 8 10 6 5 0 2 4 1 3
$ grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}' | paste -sd" "
16 13 12 17 8 10 6 5 0 2 4 1 3 59 58 57 56 41 40 25 22 24 50 9 26 31 30 29 28 27 53 52 23 64 21 20 65 63 62 61 35 45 44 43 39 38 37 36 33 34 55 46 49 48 47 14 60 15 18 19 32 54
$ grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}'| sort -n | paste -sd" "
0 1 2 3 4 5 6 8 9 10 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 43 44 45 46 47 48 49 50 52 53 54 55 56 57 58 59 60 61 62 63 64 65
--//简单说明：
--//grep "^\[" cache#.txt --过滤开头[
--//awk '{print $NF}' --取最后1个字段
--//cut -c1,2 --取前2个字符
--//awk '{print strtonum("0x"$0)}' --转换16进制为10进制
--//sort -n --按照数字排序。
--//paste -sd" " --转换成1行使用空格分开.
$ seq 0 1 65 | egrep -v -e ^7 -e ^11 -e ^42 -e ^51| paste -sd" "
0 1 2 3 4 5 6 8 9 10 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 43 44 45 46 47 48 49 50 52 53 54 55 56 57 58 59 60 61 62 63 64 65
$ diff <(grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}'| sort -n) <(seq 0 1 65 )
7a8
> 7
10a12
> 11
40a43
> 42
48a52
> 51
$ diff <(grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}'| sort -n | paste -sd" ") <(seq 0 1 65 | egrep -v -e ^7 -e ^11 -e ^42 -e ^51| paste -sd" ")
--//没有任何输出,验证我的猜测对的.
4.继续:
--//还是存在62个chunk mod(KSMCHSIZ-16,24)<>0 的情况,混合在一起输出：
SYS@book> select a.*,(KSMCHSIZ-16)/24 buckets ,mod (KSMCHSIZ-16,24) mod from x$ksmsp a where KSMCHCOM='row cache mutex' ;
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS MOD
---------------- ------ ---------- ------ ---------- ---------- ------------------ ---------------- ---------- -------- ---------- ---------------- ---------- ----------
00007FE70C5AECB0 96395 1 1 1 1 row cache mutex 0000000077AD7C78 1572880 perm 0 0000000077004000 65536 0
00007FE70C5AE970 96403 1 1 1 1 row cache mutex 00000000779D7950 528 perm 0 0000000077004000 21.3333333 8
00007FE70C5AE908 96404 1 1 1 1 row cache mutex 00000000779D6D40 3088 perm 0 0000000077004000 128 0
00007FE70C5AE5C8 96412 1 1 1 1 row cache mutex 00000000779D2418 16400 perm 0 0000000077004000 682.666667 16
00007FE70C5AE560 96413 1 1 1 1 row cache mutex 00000000779BA408 98320 perm 0 0000000077004000 4096 0
00007FE70C5AE220 96421 1 1 1 1 row cache mutex 00000000779A9AE0 2064 perm 0 0000000077004000 85.3333333 8
00007FE70C5AE1B8 96422 1 1 1 1 row cache mutex 00000000779A6AD0 12304 perm 0 0000000077004000 512 0
00007FE70C5ADE78 96430 1 1 1 1 row cache mutex 00000000779849A8 131088 perm 0 0000000077004000 5461.33333 8
00007FE70C5ADE10 96431 1 1 1 1 row cache mutex 00000000778C4998 786448 perm 0 0000000077004000 32768 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
00007FE70C5ADAD0 96439 1 1 1 1 row cache mutex 0000000077824870 131088 perm 0 0000000077004000 5461.33333 8
00007FE70C5ADA68 96440 1 1 1 1 row cache mutex 0000000077764860 786448 perm 0 0000000077004000 32768 0
00007FE70C5AD728 96448 1 1 1 1 row cache mutex 00000000776E4630 272 perm 0 0000000077004000 10.6666667 16
00007FE70C5AD6C0 96449 1 1 1 1 row cache mutex 00000000776E4020 1552 perm 0 0000000077004000 64 0
00007FE70C5AD380 96457 1 1 1 1 row cache mutex 00000000776E36F8 1040 perm 0 0000000077004000 42.6666667 16
00007FE70C5AD318 96458 1 1 1 1 row cache mutex 00000000776E1EE8 6160 perm 0 0000000077004000 256 0
00007FE70C5ACFD8 96466 1 1 1 1 row cache mutex 00000000776E0D40 144 perm 0 0000000077004000 5.33333333 8
00007FE70C5ACF70 96467 1 1 1 1 row cache mutex 00000000776E0A30 784 perm 0 0000000077004000 32 0
00007FE70C5ACC30 96475 1 1 1 1 row cache mutex 00000000776C0708 131088 perm 0 0000000077004000 5461.33333 8
00007FE70C5ACBC8 96476 1 1 1 1 row cache mutex 00000000776006F8 786448 perm 0 0000000077004000 32768 0
00007FE70C5AC888 96484 1 1 1 1 row cache mutex 00000000775801D0 1040 perm 0 0000000077004000 42.6666667 16
00007FE70C5AC820 96485 1 1 1 1 row cache mutex 000000007757E9C0 6160 perm 0 0000000077004000 256 0
00007FE70C5AC4E0 96493 1 1 1 1 row cache mutex 000000007757D498 1040 perm 0 0000000077004000 42.6666667 16
00007FE70C5AC478 96494 1 1 1 1 row cache mutex 000000007757BC88 6160 perm 0 0000000077004000 256 0
00007FE70C5AC138 96502 1 1 1 1 row cache mutex 000000007756AB60 65552 perm 0 0000000077004000 2730.66667 16
00007FE70C5AC0D0 96503 1 1 1 1 row cache mutex 000000007750AB50 393232 perm 0 0000000077004000 16384 0
00007FE70C588038 97920 1 1 1 1 row cache mutex 0000000076A469C8 272 perm 0 0000000076081000 10.6666667 16
00007FE70C587FD0 97921 1 1 1 1 row cache mutex 0000000076A463B8 1552 perm 0 0000000076081000 64 0
00007FE70C587C90 97929 1 1 1 1 row cache mutex 0000000076A45D90 272 perm 0 0000000076081000 10.6666667 16
00007FE70C587C28 97930 1 1 1 1 row cache mutex 0000000076A45780 1552 perm 0 0000000076081000 64 0
00007FE70C5878E8 97938 1 1 1 1 row cache mutex 0000000076A45158 272 perm 0 0000000076081000 10.6666667 16
00007FE70C587880 97939 1 1 1 1 row cache mutex 0000000076A44B48 1552 perm 0 0000000076081000 64 0
00007FE70C587540 97947 1 1 1 1 row cache mutex 0000000076A44520 272 perm 0 0000000076081000 10.6666667 16
00007FE70C5874D8 97948 1 1 1 1 row cache mutex 0000000076A43F10 1552 perm 0 0000000076081000 64 0
00007FE70C587198 97956 1 1 1 1 row cache mutex 0000000076A435E8 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C587130 97957 1 1 1 1 row cache mutex 0000000076A41DD8 6160 perm 0 0000000076081000 256 0
00007FE70C586DF0 97965 1 1 1 1 row cache mutex 0000000076A408B0 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C586D88 97966 1 1 1 1 row cache mutex 0000000076A3F0A0 6160 perm 0 0000000076081000 256 0
00007FE70C586A48 97974 1 1 1 1 row cache mutex 0000000076A3D778 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C5869E0 97975 1 1 1 1 row cache mutex 0000000076A3A768 12304 perm 0 0000000076081000 512 0
00007FE70C5866A0 97983 1 1 1 1 row cache mutex 0000000076A38638 24 perm 0 0000000076081000 .333333333 8
00007FE70C586638 97984 1 1 1 1 row cache mutex 0000000076A38610 40 perm 0 0000000076081000 1 0
00007FE70C5862F8 97992 1 1 1 1 row cache mutex 0000000076A380D8 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C586290 97993 1 1 1 1 row cache mutex 0000000076A368C8 6160 perm 0 0000000076081000 256 0
00007FE70C585F50 98001 1 1 1 1 row cache mutex 0000000076A34FA0 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C585EE8 98002 1 1 1 1 row cache mutex 0000000076A31F90 12304 perm 0 0000000076081000 512 0
00007FE70C585BA8 98010 1 1 1 1 row cache mutex 0000000076A2FC68 528 perm 0 0000000076081000 21.3333333 8
00007FE70C585B40 98011 1 1 1 1 row cache mutex 0000000076A2F058 3088 perm 0 0000000076081000 128 0
00007FE70C585800 98019 1 1 1 1 row cache mutex 0000000076A2DF30 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C585798 98020 1 1 1 1 row cache mutex 0000000076A2AF20 12304 perm 0 0000000076081000 512 0
00007FE70C585458 98028 1 1 1 1 row cache mutex 0000000076A285F8 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C5853F0 98029 1 1 1 1 row cache mutex 0000000076A255E8 12304 perm 0 0000000076081000 512 0
00007FE70C5850B0 98037 1 1 1 1 row cache mutex 0000000076A22CC0 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C585048 98038 1 1 1 1 row cache mutex 0000000076A1FCB0 12304 perm 0 0000000076081000 512 0
00007FE70C584D08 98046 1 1 1 1 row cache mutex 0000000076A1D388 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C584CA0 98047 1 1 1 1 row cache mutex 0000000076A1A378 12304 perm 0 0000000076081000 512 0
00007FE70C584960 98055 1 1 1 1 row cache mutex 0000000076A17A50 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C5848F8 98056 1 1 1 1 row cache mutex 0000000076A14A40 12304 perm 0 0000000076081000 512 0
00007FE70C5845B8 98064 1 1 1 1 row cache mutex 0000000076A12118 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C584550 98065 1 1 1 1 row cache mutex 0000000076A0F108 12304 perm 0 0000000076081000 512 0
00007FE70C584210 98073 1 1 1 1 row cache mutex 0000000076A0C7E0 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C5841A8 98074 1 1 1 1 row cache mutex 0000000076A097D0 12304 perm 0 0000000076081000 512 0
00007FE70C583E68 98082 1 1 1 1 row cache mutex 0000000076A06EA8 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C583E00 98083 1 1 1 1 row cache mutex 0000000076A03E98 12304 perm 0 0000000076081000 512 0
00007FE70C583AC0 98091 1 1 1 1 row cache mutex 0000000076A01570 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C583A58 98092 1 1 1 1 row cache mutex 00000000769FE560 12304 perm 0 0000000076081000 512 0
00007FE70C583718 98100 1 1 1 1 row cache mutex 00000000769EC438 65552 perm 0 0000000076081000 2730.66667 16
00007FE70C5836B0 98101 1 1 1 1 row cache mutex 000000007698C428 393232 perm 0 0000000076081000 16384 0
00007FE70C583370 98109 1 1 1 1 row cache mutex 000000007693C300 65552 perm 0 0000000076081000 2730.66667 16
00007FE70C583308 98110 1 1 1 1 row cache mutex 00000000768DC2F0 393232 perm 0 0000000076081000 16384 0
00007FE70C582FC8 98118 1 1 1 1 row cache mutex 000000007688C1C8 65552 perm 0 0000000076081000 2730.66667 16
00007FE70C582F60 98119 1 1 1 1 row cache mutex 000000007682C1B8 393232 perm 0 0000000076081000 16384 0
00007FE70C582C20 98127 1 1 1 1 row cache mutex 00000000767CC090 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C582BB8 98128 1 1 1 1 row cache mutex 000000007670C080 786448 perm 0 0000000076081000 32768 0
00007FE70C582878 98136 1 1 1 1 row cache mutex 000000007666BF58 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C582810 98137 1 1 1 1 row cache mutex 00000000765ABF48 786448 perm 0 0000000076081000 32768 0
00007FE70C5824D0 98145 1 1 1 1 row cache mutex 000000007650BE20 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C582468 98146 1 1 1 1 row cache mutex 000000007644BE10 786448 perm 0 0000000076081000 32768 0
00007FE70C582128 98154 1 1 1 1 row cache mutex 00000000763ABCE8 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C5820C0 98155 1 1 1 1 row cache mutex 00000000762EBCD8 786448 perm 0 0000000076081000 32768 0
00007FE70C581D80 98163 1 1 1 1 row cache mutex 000000007626B3B0 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C581D18 98164 1 1 1 1 row cache mutex 00000000762683A0 12304 perm 0 0000000076081000 512 0
00007FE70C5819D8 98172 1 1 1 1 row cache mutex 0000000076265E78 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C581970 98173 1 1 1 1 row cache mutex 0000000076264668 6160 perm 0 0000000076081000 256 0
00007FE70C581630 98181 1 1 1 1 row cache mutex 0000000076263140 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C5815C8 98182 1 1 1 1 row cache mutex 0000000076261930 6160 perm 0 0000000076081000 256 0
00007FE70C581288 98190 1 1 1 1 row cache mutex 0000000076260408 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C581220 98191 1 1 1 1 row cache mutex 000000007625EBF8 6160 perm 0 0000000076081000 256 0
00007FE70C580EE0 98199 1 1 1 1 row cache mutex 000000007625D6D0 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C580E78 98200 1 1 1 1 row cache mutex 000000007625BEC0 6160 perm 0 0000000076081000 256 0
00007FE70C580B38 98208 1 1 1 1 row cache mutex 000000007625A598 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C580AD0 98209 1 1 1 1 row cache mutex 0000000076257588 12304 perm 0 0000000076081000 512 0
00007FE70C580790 98217 1 1 1 1 row cache mutex 0000000076255060 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C580728 98218 1 1 1 1 row cache mutex 0000000076253850 6160 perm 0 0000000076081000 256 0
00007FE70C5803E8 98226 1 1 1 1 row cache mutex 0000000076252328 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C580380 98227 1 1 1 1 row cache mutex 0000000076250B18 6160 perm 0 0000000076081000 256 0
00007FE70C57FF50 98235 1 1 1 1 row cache mutex 000000007624F5F0 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57FEE8 98236 1 1 1 1 row cache mutex 000000007624DDE0 6160 perm 0 0000000076081000 256 0
00007FE70C57FBA8 98244 1 1 1 1 row cache mutex 000000007624C4B8 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C57FB40 98245 1 1 1 1 row cache mutex 00000000762494A8 12304 perm 0 0000000076081000 512 0
00007FE70C57F800 98253 1 1 1 1 row cache mutex 0000000076246F80 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57F798 98254 1 1 1 1 row cache mutex 0000000076245770 6160 perm 0 0000000076081000 256 0
00007FE70C57F458 98262 1 1 1 1 row cache mutex 0000000076244248 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57F3F0 98263 1 1 1 1 row cache mutex 0000000076242A38 6160 perm 0 0000000076081000 256 0
00007FE70C57F0B0 98271 1 1 1 1 row cache mutex 0000000076221910 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C57F048 98272 1 1 1 1 row cache mutex 0000000076161900 786448 perm 0 0000000076081000 32768 0
00007FE70C57ED08 98280 1 1 1 1 row cache mutex 00000000760E13D8 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57ECA0 98281 1 1 1 1 row cache mutex 00000000760DFBC8 6160 perm 0 0000000076081000 256 0
00007FE70C57E960 98289 1 1 1 1 row cache mutex 00000000760DE6A0 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57E8F8 98290 1 1 1 1 row cache mutex 00000000760DCE90 6160 perm 0 0000000076081000 256 0
00007FE70C57E5B8 98298 1 1 1 1 row cache mutex 00000000760DBD28 80 perm 0 0000000076081000 2.66666667 16
00007FE70C57E550 98299 1 1 1 1 row cache mutex 00000000760DBB98 400 perm 0 0000000076081000 16 0
00007FE70C57E210 98307 1 1 1 1 row cache mutex 00000000760DB870 272 perm 0 0000000076081000 10.6666667 16
00007FE70C57E1A8 98308 1 1 1 1 row cache mutex 00000000760DB260 1552 perm 0 0000000076081000 64 0
00007FE70C57DE68 98316 1 1 1 1 row cache mutex 00000000760DAC38 272 perm 0 0000000076081000 10.6666667 16
00007FE70C57DE00 98317 1 1 1 1 row cache mutex 00000000760DA628 1552 perm 0 0000000076081000 64 0
00007FE70C57DAC0 98325 1 1 1 1 row cache mutex 00000000760D9900 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C57DA58 98326 1 1 1 1 row cache mutex 00000000760D68F0 12304 perm 0 0000000076081000 512 0
00007FE70C57D718 98334 1 1 1 1 row cache mutex 00000000760D3FC8 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C57D6B0 98335 1 1 1 1 row cache mutex 00000000760D0FB8 12304 perm 0 0000000076081000 512 0
00007FE70C57D370 98343 1 1 1 1 row cache mutex 00000000760CDE90 4112 perm 0 0000000076081000 170.666667 16
00007FE70C57D308 98344 1 1 1 1 row cache mutex 00000000760C7E80 24592 perm 0 0000000076081000 1024 0
00007FE70C57CFC8 98352 1 1 1 1 row cache mutex 00000000760C3958 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57CF60 98353 1 1 1 1 row cache mutex 00000000760C2148 6160 perm 0 0000000076081000 256 0
00007FE70C57CC20 98361 1 1 1 1 row cache mutex 0000000076081020 262160 perm 0 0000000076081000 10922.6667 16
124 rows selected.
--//INDX 连在一起的存在一个规律,后者KSMCHSIZ-16 除以 前者KSMCHSIZ-16 = 6,而且仔细看可以发现地址是连续的.
with x1 as (select a.*,(KSMCHSIZ-16)/24 buckets ,mod (KSMCHSIZ-16,24) mod from x$ksmsp a where KSMCHCOM='row cache mutex' )
select x2.*,(x2.KSMCHSIZ-16)/(x1.KSMCHSIZ-16) n10 from x1 ,x1 x2 where x1.INDX+1=x2.indx ;
--//限于篇幅,结果不贴出来了，也就是24/6=4.
--//按照前面hash的计算,KSMCHPTR=00000000779849A8 其hash值对应的地址如下:
0x00000000779849A8+16+6777*4 = 0x7798b39c
0x00000000779849A8+16+6778*4 = 0x7798b3a0
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 05010020 06010034
--//$ zzdate;seq 5 | xargs -IQ -P 5 sqlplus -s -l '/ as sysdba' @zz6.txt 1e7 test1 Q > /dev/null ; zzdate
--//等一会执行:
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 05010028 0601003C
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 7090 6777 000000006234DEB0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7086 6777 00000000657C8380 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7087 6777 0000000065759CD0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7088 6777 0000000065739530 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7089 6777 0000000065739000 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7095 6778 0000000065766108 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7091 6778 00000000657A7CC0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7092 6778 0000000065738AD0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7093 6778 00000000657385A0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7094 6778 0000000065738070 11 dc_objects N 0 0 00 00 0 0 00 00 1
KOTTBX$ 7 0x1a7a 2936 6778 000000006FAF22E8 8 dc_objects Y 0 0 00 00 0 0 00 00 2
11 rows selected.
--//猜测前面05表示有5个对象,06表示6个对象。
--//$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l '/ as sysdba' @zz6.txt 1e7 test1 Q > /dev/null ; zzdate
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 7017 6777 00000000657C8380 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7033 6778 00000000691F3288 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7018 6777 0000000065759CD0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7019 6777 0000000065739530 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7020 6777 0000000065739000 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7021 6777 000000006234DEB0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7022 6777 0000000067E84D90 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7023 6777 00000000676E53E8 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7024 6778 00000000657A7CC0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7025 6778 0000000065738AD0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7026 6778 00000000657385A0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7027 6778 0000000065738070 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7028 6778 0000000065766108 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7029 6778 0000000069321788 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7030 6778 0000000067CC74A8 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7031 6778 0000000067B8B478 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7032 6778 0000000067B8CE08 11 dc_objects N 0 0 00 00 0 0 00 00 1
KOTTBX$ 7 0x1a7a 2797 6778 000000006FAF22E8 8 dc_objects Y 0 0 00 00 0 0 00 00 2
18 rows selected.
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 0701004C 0B010066
--//似乎猜测是对的.
SYS@book> @ opeek 0x7798b39c 24 0
[07798B39C, 07798B3B4) = 08010056 0D010076 00000002 01000022 00000002 00000002
--//还可以猜测第3，4的01表示正在访问吗？还有没有对象都是00000002。
--//hash=6780对应值等于01000022 ,前面的01表示存在1个对象.
SYS@book> @ dc/dc_objects "hash in (-1,6780,6779,6781) "
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 WRH$_SQLSTAT_BL_PK 18 0x1a7c 2287 6780 0000000072480F00 8 dc_objects Y 0 0 00 00 0 0 00 00 1
--//没有对象的对应的都是00000002.
--//中断测试的执行,刷新共享池看看.
SYS@book> alter system flush SHARED_POOL;
System altered.
SYS@book> @ opeek 0x7798b39c 24 0
[07798B39C, 07798B3B4) = 0800006E 0D000098 00000002 0100002C 00000002 00000002
--//第3,4位上的01变成00.
--//剩下5-8位表示什么，不好猜测.
--//剩下一个疑问，如果知道某个对象的hash值 row cache 如何定位呢。
--//以前library cache bucket mutex，每个mutext的前面16字节包含对象的首尾句柄地址，通过链接的形式连接起来。
--//row cache mutex对应的chunk没有包含对象的首尾句柄地址信息。
--//另外写一篇blog分析。
5.附上测试使用代码：
$ cat tpt/mutexprofz.sql
--------------------------------------------------------------------------------
--
-- File name: mutexprof.sql ( Mutex sleep Profiler )
--
-- Purpose: Display KGX mutex sleep history from v$mutex_sleep_history
-- along library cache object names protected by these mutexes.
-- Only top 20 rows are shown by default
--
-- Author: Tanel Poder
-- Copyright: (c) http://www.tanelpoder.com
--
-- Usage: @mutexprof <grouping columns> <filter condition>
--
-- The main grouping (and filtering) columns are:
--
-- id - mutex ID (which is the object hash value for library
-- cache object mutexes)
-- ts - timestamp of mutex sleep beginning
-- loc - code location where the waiter slept for the mutex
-- val - mutex value (shows whether mutex was held in exclusive or
-- shared mode)
-- req - requesting session SID
-- blk - blocking session SID
--
-- The filter condition allows filtering mutex sleep rows based on certain
-- criteria, such:
--
-- 1=1 - show all mutex sleeps (which are still in memory)
-- blk=123 - show only these mutex sleeps where blocking sid was 123
-- hash=2741853041 - show only these sleeps where mutex ID (KGL object hash value)
-- was 2741853041
--
--
-- Its also possible to have multiple "AND" filter conditions, as long as you keep
-- them in double quotes so that sqlplus would recognize them as one parameter
--
-- For example: "name like '%DUAL%' and blk in (115,98)"
--
-- Examples:
--
-- @mutexprof loc 1=1
-- @mutexprof id,loc,req,blk "lower(name) like 'select%from dual%'"
-- @mutexprof loc,val blk=98
-- @mutexprof id,loc,req,blk "blk in (select sid from v$session where username = 'SYS')"
--
-- Other: When the relevant object is aged out you will see (name not found)
-- as object_name.
--
-- On 10.2.0.1 the V$mutex_sleep_history does not have mutex_identifier
-- column externalized. In this case use X$mutex_sleep_history instead
--
--------------------------------------------------------------------------------
col msh_obj_name head OBJECT_NAME for a80 word_wrap
col msh_mutex_type head MUTEX_TYPE for a15 truncate
col loc head GET_LOCATION for a33 truncate
col mutexprof_gets head GETS_DIFF for 9999999999999
col mutexprof_sleeps head SLEEPS for 999999
col mutexprof_p2 head P2 for a16 wrap
col mutexprof_p3 head P3 for a16 wrap
col mutexprof_p4 head P4 for a16 wrap
col mutexprof_p5 head P5 for a20 wrap
col maddr head mutex_addr for a20 wrap
def MSH_NUMROWS=50
select * from (
select /*+ ORDERED USE_NL(o) */
-- TODO the sleep/get counting needs fixing!
--MAX(sleeps) sleeps
sum(sleeps) sum_sleeps
--count(*) sleeps
--, decode(max(sleeps)-min(sleeps),0,to_number(null),max(sleeps)-min(sleeps)) mutexprof_sleeps -- may not be very accurate but give an idea
, decode(max(gets)-min(gets),0,to_number(null),max(gets)-min(gets)) mutexprof_gets -- may not be very accurate but give an idea
-- avg(sleeps) sleeps
--, avg(gets) gets
, mutex_type msh_mutex_type
, &1
, sql_id
, replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') msh_obj_name
--, nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)') msh_obj_name
--, p1raw
--, CASE WHEN p2 < 536870912 THEN TO_CHAR(p2) ELSE TRIM(TO_CHAR(p2, 'XXXXXXXXXXXXXXXX')) END mutexprof_p2
--, CASE WHEN p3 < 536870912 THEN TO_CHAR(p3) ELSE TRIM(TO_CHAR(p3, 'XXXXXXXXXXXXXXXX')) END mutexprof_p3
--, CASE WHEN p4 < 536870912 THEN TO_CHAR(p4) ELSE TRIM(TO_CHAR(p4, 'XXXXXXXXXXXXXXXX')) END mutexprof_p4
--, p5 mutexprof_p5
from
(select
mutex_identifier id
, mutex_identifier idn
, to_char (mutex_identifier, 'fm0XXXXXXX') idnhex
, sleep_timestamp ts
, mutex_type
, gets
, sleeps
, requesting_session req
, blocking_session blk
, location loc
, mutex_value val
, mutex_addr maddr
, p1
, p1raw
, p2
, p3
, p4
, p5
from x$mutex_sleep_history) m
, (select kglnahsh, kglnahsh hash_value, kglnahsh hash,
kglhdpar, kglhdadr, kglnaown, kglnaobj, kglobt03 sql_id,kglhdbid kgl_bucket,
decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj) object_name,
decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj) name
from x$kglob) o
where
m.id = o.kglnahsh (+)
and (o.kglhdadr = o.kglhdpar or (o.kglhdpar is null)) -- only parent KGL objects if still in cache
and &2
group by
mutex_type
, &1
, sql_id
, kglnaown
, kglnaobj
, p1raw
, CASE WHEN p2 < 536870912 THEN TO_CHAR(p2) ELSE TRIM(TO_CHAR(p2, 'XXXXXXXXXXXXXXXX')) END
, CASE WHEN p3 < 536870912 THEN TO_CHAR(p3) ELSE TRIM(TO_CHAR(p3, 'XXXXXXXXXXXXXXXX')) END
, CASE WHEN p4 < 536870912 THEN TO_CHAR(p4) ELSE TRIM(TO_CHAR(p4, 'XXXXXXXXXXXXXXXX')) END
--, p5
order by
1 desc
-- sleeps desc
)
where rownum <= &MSH_NUMROWS
/
$ cat dc/dc_objects.sql
column DC_PROP_NAME format a28
column CACHE_NAME format a20
column EXISTENT format a8
column KEY_OID$ format a32
column dc_obj_name format a32
column dc_obj_name1 format a32
column username format a20
column con_id format 99999
column indx format 99999
column hash format 99999
column key noprint
SELECT *
FROM (SELECT --TO_NUMBER ( (SUBSTR (key, 7, 2) || SUBSTR (key, 5, 2) || SUBSTR (key, 3, 2) || SUBSTR (key, 1, 2)), 'XXXXXXXX') Schema_User_ID ,
(SELECT username
FROM cdb_users
WHERE con_id= v.con_id and user_id =
TO_NUMBER (
(SUBSTR (key, 7, 2) || SUBSTR (key, 5, 2) || SUBSTR (key, 3, 2) || SUBSTR (key, 1, 2))
,'XXXXXXXX'))
username
--,RTRIM (UTL_RAW.cast_to_varchar2 (SUBSTR (v.key, 13)), CHR (0)) dc_obj_name
--,TO_NUMBER (TRIM (BOTH '0' FROM SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2)), 'XXXX') key_str_len
,UTL_RAW.cast_to_varchar2 (SUBSTR (v.key, 13,2*TO_NUMBER ( SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2), 'XXXX'))) dc_obj_name
,TO_NUMBER ( SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2), 'XXXX') key_str_len
,'0x'||to_char(v.hash,'FMxxxxx') hash_hex
,v.*
FROM v$rowcache_parent v
WHERE cache# in (8,11)
)
WHERE (&1)
ORDER BY key;
column key print
column hash clear
$ cat opeek.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- Name: opeek.sql
-- Purpose: oradebug peek
-- Author: lfree
-- Usage:
-- @ opeek <addr> <length> <1|0>
-- argv3 : 1 -- write tracename and display 0 -- display
--------------------------------------------------------------------------------
set term off
col 1 new_value 1
select replace(lower('&&1'),'x','') "1" from dual;
column tt new_value var
select decode(&&3,1,'ti','zero') tt from dual;
oradebug setmypid
set term on
@ &var.
--@ ti
oradebug peek 0x&1 &2 &3
prompt
--//昨天的测试使用mutexprofz.sql脚本输出有mutex_addr地址，不知道是否巧合2个hash相差1，对row cache mutex做一个简单探究。
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.测试脚本：
$ cat zz6.txt
set verify off
variable v_method varchar2(30);
exec :v_method := '&&2';
declare
v_sid number;
v_d date;
v varchar2(30);
begin
for i in 1 .. &&1 loop
select /*+ &3 */ sid into v_sid from v$mystat where rownum=1;
end loop;
end ;
/
quit
3.测试：
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l '/ as sysdba' @zz6.txt 1e6 test1 Q > /dev/null ; zzdate
trunc(sysdate)+16/24+39/1440+31/86400 1786351171.063864930
...
SYS@book> @mutexprofz idn,maddr,idnhex,hash,loc "ts >= trunc(sysdate)+16/24+39/1440+31/86400 and mutex_type='Row Cache'"
-- MutexProf by Tanel Poder (http://www.tanelpoder.com)
-- Showing profile of top 50 sleeps...
-- column info : id idn hash hash_value=>hash_value ts=>sleep_timestamp
-- req=>requesting_session blk=>blocking_session val=>mutex_value maddr=>mutex_addr
SUM_SLEEPS GETS_DIFF MUTEX_TYPE IDN mutex_addr IDNHEX HASH GET_LOCATION SQL_ID OBJECT_NAME
---------- -------------- --------------- ---------- -------------------- --------- ---------- --------------------------------- ------------- -----------------
25 584508 Row Cache 134224506 00000000778EC518 08001A7A [17] kqrCreateUsingSecondaryKey (name not found)
24 42192 Row Cache 134224506 00000000778EC518 08001A7A [19] kqrpre (name not found)
17 22636895 Row Cache 0 000000006433A370 00000000 [10] kqreqd (name not found)
15 128596 Row Cache 134224505 00000000778EC500 08001A79 [17] kqrCreateUsingSecondaryKey (name not found)
14 110952 Row Cache 134224505 00000000778EC500 08001A79 [19] kqrpre (name not found)
8 7793640 Row Cache 0 00000000646AEDA8 00000000 [10] kqreqd (name not found)
8 15722303 Row Cache 0 000000006455BA80 00000000 [10] kqreqd (name not found)
4 5959059 Row Cache 0 00000000646CF670 00000000 [10] kqreqd (name not found)
1 Row Cache 0 00000000646CF140 00000000 [10] kqreqd (name not found)
9 rows selected.
--//注意看IDN=134224505，134224506转换16进制，对应08001A79，08001A7A(不知道是否巧合2者差1)。
--//猜测前面的0800(反转0008或者就是08)对应cache_id.这样后面 0x1a79，0x1a7a定义hash值。
--//0x1a79 = 6777 0x1a7a = 6778.
--//mutex_addr 记录的00000000778EC500(IDNHEX=08001A79)，00000000778EC518(IDNHEX=08001A7A)
SYS@book> @ fchaz 00000000778EC500
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 00000000778C4998 1 1 row cache mutex 786448 perm 0 0000000077004000 00000000778C4998 00000000779849A8
SYS@book> @ fchaz 00000000778EC518
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 00000000778C4998 1 1 row cache mutex 786448 perm 0 0000000077004000 00000000778C4998 00000000779849A8
--//两者都在同一个chunk里面。
SYS@book> @ calc 0x00000000778EC518 - 0x00000000778EC500
DEC HEX
----------------------------------- --------------------
24.000000 18
--//正好相差24字节。
--//以前分析过library cache bucket mutex
--// 0- 7 字节是muext的值。
--// 8-11 字节是mutex gets的数量。
--//12-15 字节是mutex sleep的数量。
--//16-19 字节是Bucket桶号。
--//20-23 字节是转储看到的6，而opeek看都是0，表示什么不是很清楚。
--//每个mutex结构体占用24字节。
SYS@book> @ opeek 00000000778EC500 24 0
[0778EC500, 0778EC518) = 00000000 00000000 0AE3691C 00013D89 08001A79 00000000
~~~~~~~~
SYS@book> @ opeek 00000000778EC518 24 0
[0778EC518, 0778EC530) = 00000000 00000000 0AE08F70 0001421C 08001A7A 00000000
SYS@book> @ opeek 00000000778EC530 24 0
[0778EC530, 0778EC548) = 00000000 00000000 00000000 00000000 08001A7B 00000000
--//下划线正好等于bucket桶号。
--//KSMCHSIZ=786448,减去段头16字节，(786448-16)/24 = 32768,说明这个chunk容纳32768个bucket。
--//而且可以确定访问dc_objects=8的row cache mutex都是使用该chunk。
--//注：实际上查询v$rowcache_parent看到的cache#=11，说明cache#11也是使用该chunk。
--//补充测试：
SYS@book> select rownum cache# from dual connect by level <=65 minus select cache# from v$rowcache where type='PARENT';
CACHE#
----------
7
42
51
11
SYS@book> select distinct cache#,type,parameter from v$rowcache where cache# in (7,11,42,51);
CACHE# TYPE PARAMETER
---------- ------------------------------ ------------------------------
7 SUBORDINATE dc_users
--//存在 CACHE#=7，TYPE=SUBORDINATE，PARAMETER=dc_users。在type='PARENT'，PARAMETER=dc_users，cache#=10.
SYS@book> select distinct cache#,cache_name from v$rowcache_parent where cache# in (7,11,42,51);
CACHE# CACHE_NAME
---------- --------------
11 dc_objects
--//有一部分CACHE_NAME=dc_objects,cache#=11,似乎这些对象在v$fixed_table以及系统视图的定义。
SYS@book> select distinct cache#,SUBCACHE# ,SUBCACHE_NAME from v$rowcache_subordinate where cache# in (7,11,42,51) or SUBCACHE# in (7,11,42,51);
CACHE# SUBCACHE# SUBCACHE_NAME
---------- ---------- ----------------------------------------------------------------
7 1 dc_user_grants
7 3 user's audit policies
7 5 CONTAINER_DATA_Attributes
########################
SYS@book> SELECT cache# ,type ,parameter ,count FROM v$rowcache where type='PARENT' order by 1;
CACHE# TYPE PARAMETER COUNT
---------- ------- ---------------------------------------- ----------
0 PARENT dc_tablespaces 13
1 PARENT dc_free_extents 0
2 PARENT dc_segments 1315
3 PARENT dc_rollback_segments 77
4 PARENT dc_used_extents 0
5 PARENT dc_tablespace_quotas 0
6 PARENT dc_files 0
8 PARENT dc_objects 2687
9 PARENT qmrc_cache_entries 0
10 PARENT dc_users 9
12 PARENT dc_constraints 0
13 PARENT dc_sequences 7
14 PARENT dc_profiles 1
15 PARENT dc_props 8
16 PARENT dc_histogram_defs 7875
17 PARENT dc_global_oids 91
18 PARENT dc_outlines 0
19 PARENT dc_table_scns 0
20 PARENT rule_info 0
21 PARENT rule_or_piece 0
22 PARENT dc_awr_control 2
23 PARENT dc_qmc_ldap_cache_entries 0
24 PARENT outstanding_alerts 2
25 PARENT SMO rowcache 0
26 PARENT qmtmrctq_cache_entries 0
27 PARENT qmtmrcin_cache_entries 0
28 PARENT qmtmrctn_cache_entries 0
29 PARENT qmtmrcip_cache_entries 0
30 PARENT qmtmrctp_cache_entries 0
31 PARENT qmtmrciq_cache_entries 0
32 PARENT kqlsubheap_object 0
33 PARENT Unused 0
34 PARENT extensible security principal in 0
35 PARENT triton security name to ID 0
36 PARENT Unused 0
37 PARENT Unused 0
38 PARENT XS security class privilege 0
39 PARENT extensible security midtier cach 0
40 PARENT sch_lj_objs 0
41 PARENT sch_lj_oids 10
43 PARENT AV row cache 1 0
44 PARENT AV row cache 2 0
45 PARENT AV row cache 3 0
46 PARENT Rule Set Cache 0
47 PARENT realm cache 0
48 PARENT Command rule cache 0
49 PARENT Realm Object cache 0
50 PARENT qmemod_cache_entries 0
52 PARENT qmc_app_cache_entries 0
53 PARENT qmc_app_cache_entries 0
54 PARENT dc_sql_prs_errors 0
55 PARENT DV Auth Cache 0
56 PARENT dc_cdbfiles 0
57 PARENT dc_cdbservices 0
58 PARENT dc_pdbdba 0
59 PARENT dc_pdbstates 0
60 PARENT dc_cdbprops 0
61 PARENT dc_statistics_status 0
62 PARENT dc_realtime_colst 0
63 PARENT dc_realtime_tabst 0
64 PARENT key_shard 0
65 PARENT dc_column_model_to_tab 0
62 rows selected.
--//有62条记录。65-4+1 = 62，从0开始计数
--//查询x$ksmsp视图：
SYS@book> select a.*,(KSMCHSIZ-16)/24 buckets from x$ksmsp a where KSMCHCOM='row cache mutex' and mod(KSMCHSIZ-16,24)=0 ;
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS
---------------- ------ ---------- ------ ---------- ---------- ----------------- ---------------- ---------- -------- ---------- ---------------- ----------
00007FE70C5A5798 96399 1 1 1 1 row cache mutex 0000000077AD7C78 1572880 perm 0 0000000077004000 65536
00007FE70C5A53F0 96408 1 1 1 1 row cache mutex 00000000779D6D40 3088 perm 0 0000000077004000 128
00007FE70C5A5048 96417 1 1 1 1 row cache mutex 00000000779BA408 98320 perm 0 0000000077004000 4096
00007FE70C5A4CA0 96426 1 1 1 1 row cache mutex 00000000779A6AD0 12304 perm 0 0000000077004000 512
00007FE70C5A48F8 96435 1 1 1 1 row cache mutex 00000000778C4998 786448 perm 0 0000000077004000 32768
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~--//dc_objects
00007FE70C5A4550 96444 1 1 1 1 row cache mutex 0000000077764860 786448 perm 0 0000000077004000 32768
00007FE70C5A41A8 96453 1 1 1 1 row cache mutex 00000000776E4020 1552 perm 0 0000000077004000 64
00007FE70C5A3E00 96462 1 1 1 1 row cache mutex 00000000776E1EE8 6160 perm 0 0000000077004000 256
00007FE70C5A3A58 96471 1 1 1 1 row cache mutex 00000000776E0A30 784 perm 0 0000000077004000 32
00007FE70C5A36B0 96480 1 1 1 1 row cache mutex 00000000776006F8 786448 perm 0 0000000077004000 32768
00007FE70C5A3308 96489 1 1 1 1 row cache mutex 000000007757E9C0 6160 perm 0 0000000077004000 256
00007FE70C5A2F60 96498 1 1 1 1 row cache mutex 000000007757BC88 6160 perm 0 0000000077004000 256
00007FE70C5A2BB8 96507 1 1 1 1 row cache mutex 000000007750AB50 393232 perm 0 0000000077004000 16384
--//KSMCHPAR=0000000077004000,共有13个。
00007FE70C57E9C8 97925 1 1 1 1 row cache mutex 0000000076A463B8 1552 perm 0 0000000076081000 64
00007FE70C57E620 97934 1 1 1 1 row cache mutex 0000000076A45780 1552 perm 0 0000000076081000 64
00007FE70C57E278 97943 1 1 1 1 row cache mutex 0000000076A44B48 1552 perm 0 0000000076081000 64
00007FE70C57DED0 97952 1 1 1 1 row cache mutex 0000000076A43F10 1552 perm 0 0000000076081000 64
00007FE70C57DB28 97961 1 1 1 1 row cache mutex 0000000076A41DD8 6160 perm 0 0000000076081000 256
00007FE70C57D780 97970 1 1 1 1 row cache mutex 0000000076A3F0A0 6160 perm 0 0000000076081000 256
00007FE70C57D3D8 97979 1 1 1 1 row cache mutex 0000000076A3A768 12304 perm 0 0000000076081000 512
00007FE70C57D030 97988 1 1 1 1 row cache mutex 0000000076A38610 40 perm 0 0000000076081000 1
00007FE70C57CC88 97997 1 1 1 1 row cache mutex 0000000076A368C8 6160 perm 0 0000000076081000 256
00007FE70C57C8E0 98006 1 1 1 1 row cache mutex 0000000076A31F90 12304 perm 0 0000000076081000 512
00007FE70C57C538 98015 1 1 1 1 row cache mutex 0000000076A2F058 3088 perm 0 0000000076081000 128
00007FE70C57C190 98024 1 1 1 1 row cache mutex 0000000076A2AF20 12304 perm 0 0000000076081000 512
00007FE70C57BDE8 98033 1 1 1 1 row cache mutex 0000000076A255E8 12304 perm 0 0000000076081000 512
00007FE70C57BA40 98042 1 1 1 1 row cache mutex 0000000076A1FCB0 12304 perm 0 0000000076081000 512
00007FE70C57B698 98051 1 1 1 1 row cache mutex 0000000076A1A378 12304 perm 0 0000000076081000 512
00007FE70C57B2F0 98060 1 1 1 1 row cache mutex 0000000076A14A40 12304 perm 0 0000000076081000 512
00007FE70C57AF48 98069 1 1 1 1 row cache mutex 0000000076A0F108 12304 perm 0 0000000076081000 512
00007FE70C57ABA0 98078 1 1 1 1 row cache mutex 0000000076A097D0 12304 perm 0 0000000076081000 512
00007FE70C57A7F8 98087 1 1 1 1 row cache mutex 0000000076A03E98 12304 perm 0 0000000076081000 512
00007FE70C57A450 98096 1 1 1 1 row cache mutex 00000000769FE560 12304 perm 0 0000000076081000 512
00007FE70C57A0A8 98105 1 1 1 1 row cache mutex 000000007698C428 393232 perm 0 0000000076081000 16384
00007FE70C579D00 98114 1 1 1 1 row cache mutex 00000000768DC2F0 393232 perm 0 0000000076081000 16384
00007FE70C579958 98123 1 1 1 1 row cache mutex 000000007682C1B8 393232 perm 0 0000000076081000 16384
00007FE70C5795B0 98132 1 1 1 1 row cache mutex 000000007670C080 786448 perm 0 0000000076081000 32768
00007FE70C579208 98141 1 1 1 1 row cache mutex 00000000765ABF48 786448 perm 0 0000000076081000 32768
00007FE70C578E60 98150 1 1 1 1 row cache mutex 000000007644BE10 786448 perm 0 0000000076081000 32768
00007FE70C578AB8 98159 1 1 1 1 row cache mutex 00000000762EBCD8 786448 perm 0 0000000076081000 32768
00007FE70C578710 98168 1 1 1 1 row cache mutex 00000000762683A0 12304 perm 0 0000000076081000 512
00007FE70C578368 98177 1 1 1 1 row cache mutex 0000000076264668 6160 perm 0 0000000076081000 256
00007FE70C577FC0 98186 1 1 1 1 row cache mutex 0000000076261930 6160 perm 0 0000000076081000 256
00007FE70C577C18 98195 1 1 1 1 row cache mutex 000000007625EBF8 6160 perm 0 0000000076081000 256
00007FE70C577870 98204 1 1 1 1 row cache mutex 000000007625BEC0 6160 perm 0 0000000076081000 256
00007FE70C5774C8 98213 1 1 1 1 row cache mutex 0000000076257588 12304 perm 0 0000000076081000 512
00007FE70C577120 98222 1 1 1 1 row cache mutex 0000000076253850 6160 perm 0 0000000076081000 256
00007FE70C576D78 98231 1 1 1 1 row cache mutex 0000000076250B18 6160 perm 0 0000000076081000 256
00007FE70C5769D0 98240 1 1 1 1 row cache mutex 000000007624DDE0 6160 perm 0 0000000076081000 256
00007FE70C576628 98249 1 1 1 1 row cache mutex 00000000762494A8 12304 perm 0 0000000076081000 512
00007FE70C576280 98258 1 1 1 1 row cache mutex 0000000076245770 6160 perm 0 0000000076081000 256
00007FE70C575ED8 98267 1 1 1 1 row cache mutex 0000000076242A38 6160 perm 0 0000000076081000 256
00007FE70C575B30 98276 1 1 1 1 row cache mutex 0000000076161900 786448 perm 0 0000000076081000 32768
00007FE70C575788 98285 1 1 1 1 row cache mutex 00000000760DFBC8 6160 perm 0 0000000076081000 256
00007FE70C5753E0 98294 1 1 1 1 row cache mutex 00000000760DCE90 6160 perm 0 0000000076081000 256
00007FE70C575038 98303 1 1 1 1 row cache mutex 00000000760DBB98 400 perm 0 0000000076081000 16
00007FE70C574C90 98312 1 1 1 1 row cache mutex 00000000760DB260 1552 perm 0 0000000076081000 64
00007FE70C5748E8 98321 1 1 1 1 row cache mutex 00000000760DA628 1552 perm 0 0000000076081000 64
00007FE70C574540 98330 1 1 1 1 row cache mutex 00000000760D68F0 12304 perm 0 0000000076081000 512
00007FE70C574198 98339 1 1 1 1 row cache mutex 00000000760D0FB8 12304 perm 0 0000000076081000 512
00007FE70C573DF0 98348 1 1 1 1 row cache mutex 00000000760C7E80 24592 perm 0 0000000076081000 1024
00007FE70C573A48 98357 1 1 1 1 row cache mutex 00000000760C2148 6160 perm 0 0000000076081000 256
62 rows selected.
--//正好62条，与前面执行SELECT cache# ,type ,parameter ,count FROM v$rowcache where type='PARENT' order by 1;返回记录数
--//量正好一致。注意看BUCKETS列正好都是2^N，也就是根据对象的多少选择合适取模的数量。比如dc_objects就是32768。
--//简单拿最后一条记录分析：
SYS@book> @ fchaz 00000000760C2148
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 00000000760C2148 1 1 row cache mutex 6160 perm 0 0000000076081000 00000000760C2148 00000000760C3958
--//有16字节段头。 0x00000000760C2148+16=0x00000000760C2158。
SYS@book> @ opeek 00000000760C2158 24 0
[0760C2158, 0760C2170) = 00000000 00000000 00000000 00000000 36000000 00000000
SYS@book> @ opeek 00000000760C2170 24 0
[0760C2170, 0760C2188) = 00000000 00000000 00000000 00000000 36000001 00000000
--//0x36 = 54，对应的数据字典名称就是dc_sql_prs_errors。
SYS@book> @ fchaz 00000000760C7E80
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 00000000760C7E80 1 1 row cache mutex 24592 perm 0 0000000076081000 00000000760C7E80 00000000760CDE90
SYS@book> @ opeek 00000000760C7E90 24 0
[0760C7E90, 0760C7EA8) = 00000000 00000000 00000000 00000000 20000000 00000000
SYS@book> @ opeek 00000000760C7EA8 24 0
[0760C7EA8, 0760C7EC0) = 00000000 00000000 00000000 00000000 20000001 00000000
--//0x20 = 32,对应的数据字典名称就是kqlsubheap_object。
--//看看是否能对上cache#.
SYS@book> select 'oradebug peek 0x'||KSMCHPTR||' 36 0' c40 from x$ksmsp a where KSMCHCOM='row cache mutex' and mod(KSMCHSIZ-16,24)=0 ;
C40
----------------------------------------
oradebug peek 0x0000000077AD7C78 36 0
oradebug peek 0x00000000779D6D40 36 0
oradebug peek 0x00000000779BA408 36 0
oradebug peek 0x00000000779A6AD0 36 0
oradebug peek 0x00000000778C4998 36 0
oradebug peek 0x0000000077764860 36 0
oradebug peek 0x00000000776E4020 36 0
oradebug peek 0x00000000776E1EE8 36 0
oradebug peek 0x00000000776E0A30 36 0
oradebug peek 0x00000000776006F8 36 0
oradebug peek 0x000000007757E9C0 36 0
oradebug peek 0x000000007757BC88 36 0
oradebug peek 0x000000007750AB50 36 0
oradebug peek 0x0000000076A463B8 36 0
oradebug peek 0x0000000076A45780 36 0
oradebug peek 0x0000000076A44B48 36 0
oradebug peek 0x0000000076A43F10 36 0
oradebug peek 0x0000000076A41DD8 36 0
oradebug peek 0x0000000076A3F0A0 36 0
oradebug peek 0x0000000076A3A768 36 0
oradebug peek 0x0000000076A38610 36 0
oradebug peek 0x0000000076A368C8 36 0
oradebug peek 0x0000000076A31F90 36 0
oradebug peek 0x0000000076A2F058 36 0
oradebug peek 0x0000000076A2AF20 36 0
oradebug peek 0x0000000076A255E8 36 0
oradebug peek 0x0000000076A1FCB0 36 0
oradebug peek 0x0000000076A1A378 36 0
oradebug peek 0x0000000076A14A40 36 0
oradebug peek 0x0000000076A0F108 36 0
oradebug peek 0x0000000076A097D0 36 0
oradebug peek 0x0000000076A03E98 36 0
oradebug peek 0x00000000769FE560 36 0
oradebug peek 0x000000007698C428 36 0
oradebug peek 0x00000000768DC2F0 36 0
oradebug peek 0x000000007682C1B8 36 0
oradebug peek 0x000000007670C080 36 0
oradebug peek 0x00000000765ABF48 36 0
oradebug peek 0x000000007644BE10 36 0
oradebug peek 0x00000000762EBCD8 36 0
oradebug peek 0x00000000762683A0 36 0
oradebug peek 0x0000000076264668 36 0
oradebug peek 0x0000000076261930 36 0
oradebug peek 0x000000007625EBF8 36 0
oradebug peek 0x000000007625BEC0 36 0
oradebug peek 0x0000000076257588 36 0
oradebug peek 0x0000000076253850 36 0
oradebug peek 0x0000000076250B18 36 0
oradebug peek 0x000000007624DDE0 36 0
oradebug peek 0x00000000762494A8 36 0
oradebug peek 0x0000000076245770 36 0
oradebug peek 0x0000000076242A38 36 0
oradebug peek 0x0000000076161900 36 0
oradebug peek 0x00000000760DFBC8 36 0
oradebug peek 0x00000000760DCE90 36 0
oradebug peek 0x00000000760DBB98 36 0
oradebug peek 0x00000000760DB260 36 0
oradebug peek 0x00000000760DA628 36 0
oradebug peek 0x00000000760D68F0 36 0
oradebug peek 0x00000000760D0FB8 36 0
oradebug peek 0x00000000760C7E80 36 0
oradebug peek 0x00000000760C2148 36 0
62 rows selected.
--//执行如下.
spool cache#.txt
oradebug setmypid
--//拷贝粘贴以上内容
...
spool off
$ head cache#.txt
SYS@book> oradebug peek 0x0000000077AD7C78 36 0
[077AD7C78, 077AD7C9C) = 00180011 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 10000000
SYS@book> oradebug peek 0x00000000779D6D40 36 0
[0779D6D40, 0779D6D64) = 00000C11 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 0D000000
SYS@book> oradebug peek 0x00000000779BA408 36 0
[0779BA408, 0779BA42C) = 00018011 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 0C000000
SYS@book> oradebug peek 0x00000000779A6AD0 36 0
[0779A6AD0, 0779A6AF4) = 00003011 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 11000000
SYS@book> oradebug peek 0x00000000778C4998 36 0
[0778C4998, 0778C49BC) = 000C0011 00B38F00 16B4E6C3 00000000 00000000 00000000 00000000 00000000 08000000
--//最后1个字段就是bucket 0，其前面2个数字对应的就是cache#。
$ grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}' | head -13 | paste -sd" "
16 13 12 17 8 10 6 5 0 2 4 1 3
$ grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}' | paste -sd" "
16 13 12 17 8 10 6 5 0 2 4 1 3 59 58 57 56 41 40 25 22 24 50 9 26 31 30 29 28 27 53 52 23 64 21 20 65 63 62 61 35 45 44 43 39 38 37 36 33 34 55 46 49 48 47 14 60 15 18 19 32 54
$ grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}'| sort -n | paste -sd" "
0 1 2 3 4 5 6 8 9 10 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 43 44 45 46 47 48 49 50 52 53 54 55 56 57 58 59 60 61 62 63 64 65
--//简单说明：
--//grep "^\[" cache#.txt --过滤开头[
--//awk '{print $NF}' --取最后1个字段
--//cut -c1,2 --取前2个字符
--//awk '{print strtonum("0x"$0)}' --转换16进制为10进制
--//sort -n --按照数字排序。
--//paste -sd" " --转换成1行使用空格分开.
$ seq 0 1 65 | egrep -v -e ^7 -e ^11 -e ^42 -e ^51| paste -sd" "
0 1 2 3 4 5 6 8 9 10 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 43 44 45 46 47 48 49 50 52 53 54 55 56 57 58 59 60 61 62 63 64 65
$ diff <(grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}'| sort -n) <(seq 0 1 65 )
7a8
> 7
10a12
> 11
40a43
> 42
48a52
> 51
$ diff <(grep "^\[" cache#.txt | awk '{print $NF}' | cut -c1,2 | awk '{print strtonum("0x"$0)}'| sort -n | paste -sd" ") <(seq 0 1 65 | egrep -v -e ^7 -e ^11 -e ^42 -e ^51| paste -sd" ")
--//没有任何输出,验证我的猜测对的.
4.继续:
--//还是存在62个chunk mod(KSMCHSIZ-16,24)<>0 的情况,混合在一起输出：
SYS@book> select a.*,(KSMCHSIZ-16)/24 buckets ,mod (KSMCHSIZ-16,24) mod from x$ksmsp a where KSMCHCOM='row cache mutex' ;
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR BUCKETS MOD
---------------- ------ ---------- ------ ---------- ---------- ------------------ ---------------- ---------- -------- ---------- ---------------- ---------- ----------
00007FE70C5AECB0 96395 1 1 1 1 row cache mutex 0000000077AD7C78 1572880 perm 0 0000000077004000 65536 0
00007FE70C5AE970 96403 1 1 1 1 row cache mutex 00000000779D7950 528 perm 0 0000000077004000 21.3333333 8
00007FE70C5AE908 96404 1 1 1 1 row cache mutex 00000000779D6D40 3088 perm 0 0000000077004000 128 0
00007FE70C5AE5C8 96412 1 1 1 1 row cache mutex 00000000779D2418 16400 perm 0 0000000077004000 682.666667 16
00007FE70C5AE560 96413 1 1 1 1 row cache mutex 00000000779BA408 98320 perm 0 0000000077004000 4096 0
00007FE70C5AE220 96421 1 1 1 1 row cache mutex 00000000779A9AE0 2064 perm 0 0000000077004000 85.3333333 8
00007FE70C5AE1B8 96422 1 1 1 1 row cache mutex 00000000779A6AD0 12304 perm 0 0000000077004000 512 0
00007FE70C5ADE78 96430 1 1 1 1 row cache mutex 00000000779849A8 131088 perm 0 0000000077004000 5461.33333 8
00007FE70C5ADE10 96431 1 1 1 1 row cache mutex 00000000778C4998 786448 perm 0 0000000077004000 32768 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
00007FE70C5ADAD0 96439 1 1 1 1 row cache mutex 0000000077824870 131088 perm 0 0000000077004000 5461.33333 8
00007FE70C5ADA68 96440 1 1 1 1 row cache mutex 0000000077764860 786448 perm 0 0000000077004000 32768 0
00007FE70C5AD728 96448 1 1 1 1 row cache mutex 00000000776E4630 272 perm 0 0000000077004000 10.6666667 16
00007FE70C5AD6C0 96449 1 1 1 1 row cache mutex 00000000776E4020 1552 perm 0 0000000077004000 64 0
00007FE70C5AD380 96457 1 1 1 1 row cache mutex 00000000776E36F8 1040 perm 0 0000000077004000 42.6666667 16
00007FE70C5AD318 96458 1 1 1 1 row cache mutex 00000000776E1EE8 6160 perm 0 0000000077004000 256 0
00007FE70C5ACFD8 96466 1 1 1 1 row cache mutex 00000000776E0D40 144 perm 0 0000000077004000 5.33333333 8
00007FE70C5ACF70 96467 1 1 1 1 row cache mutex 00000000776E0A30 784 perm 0 0000000077004000 32 0
00007FE70C5ACC30 96475 1 1 1 1 row cache mutex 00000000776C0708 131088 perm 0 0000000077004000 5461.33333 8
00007FE70C5ACBC8 96476 1 1 1 1 row cache mutex 00000000776006F8 786448 perm 0 0000000077004000 32768 0
00007FE70C5AC888 96484 1 1 1 1 row cache mutex 00000000775801D0 1040 perm 0 0000000077004000 42.6666667 16
00007FE70C5AC820 96485 1 1 1 1 row cache mutex 000000007757E9C0 6160 perm 0 0000000077004000 256 0
00007FE70C5AC4E0 96493 1 1 1 1 row cache mutex 000000007757D498 1040 perm 0 0000000077004000 42.6666667 16
00007FE70C5AC478 96494 1 1 1 1 row cache mutex 000000007757BC88 6160 perm 0 0000000077004000 256 0
00007FE70C5AC138 96502 1 1 1 1 row cache mutex 000000007756AB60 65552 perm 0 0000000077004000 2730.66667 16
00007FE70C5AC0D0 96503 1 1 1 1 row cache mutex 000000007750AB50 393232 perm 0 0000000077004000 16384 0
00007FE70C588038 97920 1 1 1 1 row cache mutex 0000000076A469C8 272 perm 0 0000000076081000 10.6666667 16
00007FE70C587FD0 97921 1 1 1 1 row cache mutex 0000000076A463B8 1552 perm 0 0000000076081000 64 0
00007FE70C587C90 97929 1 1 1 1 row cache mutex 0000000076A45D90 272 perm 0 0000000076081000 10.6666667 16
00007FE70C587C28 97930 1 1 1 1 row cache mutex 0000000076A45780 1552 perm 0 0000000076081000 64 0
00007FE70C5878E8 97938 1 1 1 1 row cache mutex 0000000076A45158 272 perm 0 0000000076081000 10.6666667 16
00007FE70C587880 97939 1 1 1 1 row cache mutex 0000000076A44B48 1552 perm 0 0000000076081000 64 0
00007FE70C587540 97947 1 1 1 1 row cache mutex 0000000076A44520 272 perm 0 0000000076081000 10.6666667 16
00007FE70C5874D8 97948 1 1 1 1 row cache mutex 0000000076A43F10 1552 perm 0 0000000076081000 64 0
00007FE70C587198 97956 1 1 1 1 row cache mutex 0000000076A435E8 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C587130 97957 1 1 1 1 row cache mutex 0000000076A41DD8 6160 perm 0 0000000076081000 256 0
00007FE70C586DF0 97965 1 1 1 1 row cache mutex 0000000076A408B0 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C586D88 97966 1 1 1 1 row cache mutex 0000000076A3F0A0 6160 perm 0 0000000076081000 256 0
00007FE70C586A48 97974 1 1 1 1 row cache mutex 0000000076A3D778 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C5869E0 97975 1 1 1 1 row cache mutex 0000000076A3A768 12304 perm 0 0000000076081000 512 0
00007FE70C5866A0 97983 1 1 1 1 row cache mutex 0000000076A38638 24 perm 0 0000000076081000 .333333333 8
00007FE70C586638 97984 1 1 1 1 row cache mutex 0000000076A38610 40 perm 0 0000000076081000 1 0
00007FE70C5862F8 97992 1 1 1 1 row cache mutex 0000000076A380D8 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C586290 97993 1 1 1 1 row cache mutex 0000000076A368C8 6160 perm 0 0000000076081000 256 0
00007FE70C585F50 98001 1 1 1 1 row cache mutex 0000000076A34FA0 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C585EE8 98002 1 1 1 1 row cache mutex 0000000076A31F90 12304 perm 0 0000000076081000 512 0
00007FE70C585BA8 98010 1 1 1 1 row cache mutex 0000000076A2FC68 528 perm 0 0000000076081000 21.3333333 8
00007FE70C585B40 98011 1 1 1 1 row cache mutex 0000000076A2F058 3088 perm 0 0000000076081000 128 0
00007FE70C585800 98019 1 1 1 1 row cache mutex 0000000076A2DF30 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C585798 98020 1 1 1 1 row cache mutex 0000000076A2AF20 12304 perm 0 0000000076081000 512 0
00007FE70C585458 98028 1 1 1 1 row cache mutex 0000000076A285F8 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C5853F0 98029 1 1 1 1 row cache mutex 0000000076A255E8 12304 perm 0 0000000076081000 512 0
00007FE70C5850B0 98037 1 1 1 1 row cache mutex 0000000076A22CC0 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C585048 98038 1 1 1 1 row cache mutex 0000000076A1FCB0 12304 perm 0 0000000076081000 512 0
00007FE70C584D08 98046 1 1 1 1 row cache mutex 0000000076A1D388 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C584CA0 98047 1 1 1 1 row cache mutex 0000000076A1A378 12304 perm 0 0000000076081000 512 0
00007FE70C584960 98055 1 1 1 1 row cache mutex 0000000076A17A50 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C5848F8 98056 1 1 1 1 row cache mutex 0000000076A14A40 12304 perm 0 0000000076081000 512 0
00007FE70C5845B8 98064 1 1 1 1 row cache mutex 0000000076A12118 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C584550 98065 1 1 1 1 row cache mutex 0000000076A0F108 12304 perm 0 0000000076081000 512 0
00007FE70C584210 98073 1 1 1 1 row cache mutex 0000000076A0C7E0 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C5841A8 98074 1 1 1 1 row cache mutex 0000000076A097D0 12304 perm 0 0000000076081000 512 0
00007FE70C583E68 98082 1 1 1 1 row cache mutex 0000000076A06EA8 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C583E00 98083 1 1 1 1 row cache mutex 0000000076A03E98 12304 perm 0 0000000076081000 512 0
00007FE70C583AC0 98091 1 1 1 1 row cache mutex 0000000076A01570 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C583A58 98092 1 1 1 1 row cache mutex 00000000769FE560 12304 perm 0 0000000076081000 512 0
00007FE70C583718 98100 1 1 1 1 row cache mutex 00000000769EC438 65552 perm 0 0000000076081000 2730.66667 16
00007FE70C5836B0 98101 1 1 1 1 row cache mutex 000000007698C428 393232 perm 0 0000000076081000 16384 0
00007FE70C583370 98109 1 1 1 1 row cache mutex 000000007693C300 65552 perm 0 0000000076081000 2730.66667 16
00007FE70C583308 98110 1 1 1 1 row cache mutex 00000000768DC2F0 393232 perm 0 0000000076081000 16384 0
00007FE70C582FC8 98118 1 1 1 1 row cache mutex 000000007688C1C8 65552 perm 0 0000000076081000 2730.66667 16
00007FE70C582F60 98119 1 1 1 1 row cache mutex 000000007682C1B8 393232 perm 0 0000000076081000 16384 0
00007FE70C582C20 98127 1 1 1 1 row cache mutex 00000000767CC090 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C582BB8 98128 1 1 1 1 row cache mutex 000000007670C080 786448 perm 0 0000000076081000 32768 0
00007FE70C582878 98136 1 1 1 1 row cache mutex 000000007666BF58 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C582810 98137 1 1 1 1 row cache mutex 00000000765ABF48 786448 perm 0 0000000076081000 32768 0
00007FE70C5824D0 98145 1 1 1 1 row cache mutex 000000007650BE20 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C582468 98146 1 1 1 1 row cache mutex 000000007644BE10 786448 perm 0 0000000076081000 32768 0
00007FE70C582128 98154 1 1 1 1 row cache mutex 00000000763ABCE8 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C5820C0 98155 1 1 1 1 row cache mutex 00000000762EBCD8 786448 perm 0 0000000076081000 32768 0
00007FE70C581D80 98163 1 1 1 1 row cache mutex 000000007626B3B0 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C581D18 98164 1 1 1 1 row cache mutex 00000000762683A0 12304 perm 0 0000000076081000 512 0
00007FE70C5819D8 98172 1 1 1 1 row cache mutex 0000000076265E78 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C581970 98173 1 1 1 1 row cache mutex 0000000076264668 6160 perm 0 0000000076081000 256 0
00007FE70C581630 98181 1 1 1 1 row cache mutex 0000000076263140 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C5815C8 98182 1 1 1 1 row cache mutex 0000000076261930 6160 perm 0 0000000076081000 256 0
00007FE70C581288 98190 1 1 1 1 row cache mutex 0000000076260408 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C581220 98191 1 1 1 1 row cache mutex 000000007625EBF8 6160 perm 0 0000000076081000 256 0
00007FE70C580EE0 98199 1 1 1 1 row cache mutex 000000007625D6D0 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C580E78 98200 1 1 1 1 row cache mutex 000000007625BEC0 6160 perm 0 0000000076081000 256 0
00007FE70C580B38 98208 1 1 1 1 row cache mutex 000000007625A598 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C580AD0 98209 1 1 1 1 row cache mutex 0000000076257588 12304 perm 0 0000000076081000 512 0
00007FE70C580790 98217 1 1 1 1 row cache mutex 0000000076255060 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C580728 98218 1 1 1 1 row cache mutex 0000000076253850 6160 perm 0 0000000076081000 256 0
00007FE70C5803E8 98226 1 1 1 1 row cache mutex 0000000076252328 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C580380 98227 1 1 1 1 row cache mutex 0000000076250B18 6160 perm 0 0000000076081000 256 0
00007FE70C57FF50 98235 1 1 1 1 row cache mutex 000000007624F5F0 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57FEE8 98236 1 1 1 1 row cache mutex 000000007624DDE0 6160 perm 0 0000000076081000 256 0
00007FE70C57FBA8 98244 1 1 1 1 row cache mutex 000000007624C4B8 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C57FB40 98245 1 1 1 1 row cache mutex 00000000762494A8 12304 perm 0 0000000076081000 512 0
00007FE70C57F800 98253 1 1 1 1 row cache mutex 0000000076246F80 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57F798 98254 1 1 1 1 row cache mutex 0000000076245770 6160 perm 0 0000000076081000 256 0
00007FE70C57F458 98262 1 1 1 1 row cache mutex 0000000076244248 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57F3F0 98263 1 1 1 1 row cache mutex 0000000076242A38 6160 perm 0 0000000076081000 256 0
00007FE70C57F0B0 98271 1 1 1 1 row cache mutex 0000000076221910 131088 perm 0 0000000076081000 5461.33333 8
00007FE70C57F048 98272 1 1 1 1 row cache mutex 0000000076161900 786448 perm 0 0000000076081000 32768 0
00007FE70C57ED08 98280 1 1 1 1 row cache mutex 00000000760E13D8 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57ECA0 98281 1 1 1 1 row cache mutex 00000000760DFBC8 6160 perm 0 0000000076081000 256 0
00007FE70C57E960 98289 1 1 1 1 row cache mutex 00000000760DE6A0 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57E8F8 98290 1 1 1 1 row cache mutex 00000000760DCE90 6160 perm 0 0000000076081000 256 0
00007FE70C57E5B8 98298 1 1 1 1 row cache mutex 00000000760DBD28 80 perm 0 0000000076081000 2.66666667 16
00007FE70C57E550 98299 1 1 1 1 row cache mutex 00000000760DBB98 400 perm 0 0000000076081000 16 0
00007FE70C57E210 98307 1 1 1 1 row cache mutex 00000000760DB870 272 perm 0 0000000076081000 10.6666667 16
00007FE70C57E1A8 98308 1 1 1 1 row cache mutex 00000000760DB260 1552 perm 0 0000000076081000 64 0
00007FE70C57DE68 98316 1 1 1 1 row cache mutex 00000000760DAC38 272 perm 0 0000000076081000 10.6666667 16
00007FE70C57DE00 98317 1 1 1 1 row cache mutex 00000000760DA628 1552 perm 0 0000000076081000 64 0
00007FE70C57DAC0 98325 1 1 1 1 row cache mutex 00000000760D9900 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C57DA58 98326 1 1 1 1 row cache mutex 00000000760D68F0 12304 perm 0 0000000076081000 512 0
00007FE70C57D718 98334 1 1 1 1 row cache mutex 00000000760D3FC8 2064 perm 0 0000000076081000 85.3333333 8
00007FE70C57D6B0 98335 1 1 1 1 row cache mutex 00000000760D0FB8 12304 perm 0 0000000076081000 512 0
00007FE70C57D370 98343 1 1 1 1 row cache mutex 00000000760CDE90 4112 perm 0 0000000076081000 170.666667 16
00007FE70C57D308 98344 1 1 1 1 row cache mutex 00000000760C7E80 24592 perm 0 0000000076081000 1024 0
00007FE70C57CFC8 98352 1 1 1 1 row cache mutex 00000000760C3958 1040 perm 0 0000000076081000 42.6666667 16
00007FE70C57CF60 98353 1 1 1 1 row cache mutex 00000000760C2148 6160 perm 0 0000000076081000 256 0
00007FE70C57CC20 98361 1 1 1 1 row cache mutex 0000000076081020 262160 perm 0 0000000076081000 10922.6667 16
124 rows selected.
--//INDX 连在一起的存在一个规律,后者KSMCHSIZ-16 除以 前者KSMCHSIZ-16 = 6,而且仔细看可以发现地址是连续的.
with x1 as (select a.*,(KSMCHSIZ-16)/24 buckets ,mod (KSMCHSIZ-16,24) mod from x$ksmsp a where KSMCHCOM='row cache mutex' )
select x2.*,(x2.KSMCHSIZ-16)/(x1.KSMCHSIZ-16) n10 from x1 ,x1 x2 where x1.INDX+1=x2.indx ;
--//限于篇幅,结果不贴出来了，也就是24/6=4.
--//按照前面hash的计算,KSMCHPTR=00000000779849A8 其hash值对应的地址如下:
0x00000000779849A8+16+6777*4 = 0x7798b39c
0x00000000779849A8+16+6778*4 = 0x7798b3a0
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 05010020 06010034
--//$ zzdate;seq 5 | xargs -IQ -P 5 sqlplus -s -l '/ as sysdba' @zz6.txt 1e7 test1 Q > /dev/null ; zzdate
--//等一会执行:
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 05010028 0601003C
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 7090 6777 000000006234DEB0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7086 6777 00000000657C8380 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7087 6777 0000000065759CD0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7088 6777 0000000065739530 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7089 6777 0000000065739000 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7095 6778 0000000065766108 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7091 6778 00000000657A7CC0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7092 6778 0000000065738AD0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7093 6778 00000000657385A0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7094 6778 0000000065738070 11 dc_objects N 0 0 00 00 0 0 00 00 1
KOTTBX$ 7 0x1a7a 2936 6778 000000006FAF22E8 8 dc_objects Y 0 0 00 00 0 0 00 00 2
11 rows selected.
--//猜测前面05表示有5个对象,06表示6个对象。
--//$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l '/ as sysdba' @zz6.txt 1e7 test1 Q > /dev/null ; zzdate
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 7017 6777 00000000657C8380 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7033 6778 00000000691F3288 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7018 6777 0000000065759CD0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7019 6777 0000000065739530 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7020 6777 0000000065739000 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7021 6777 000000006234DEB0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7022 6777 0000000067E84D90 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a79 7023 6777 00000000676E53E8 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7024 6778 00000000657A7CC0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7025 6778 0000000065738AD0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7026 6778 00000000657385A0 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7027 6778 0000000065738070 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7028 6778 0000000065766108 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7029 6778 0000000069321788 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7030 6778 0000000067CC74A8 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7031 6778 0000000067B8B478 11 dc_objects N 0 0 00 00 0 0 00 00 1
SYS 0 0x1a7a 7032 6778 0000000067B8CE08 11 dc_objects N 0 0 00 00 0 0 00 00 1
KOTTBX$ 7 0x1a7a 2797 6778 000000006FAF22E8 8 dc_objects Y 0 0 00 00 0 0 00 00 2
18 rows selected.
SYS@book> @ opeek 0x7798b39c 8 0
[07798B39C, 07798B3A4) = 0701004C 0B010066
--//似乎猜测是对的.
SYS@book> @ opeek 0x7798b39c 24 0
[07798B39C, 07798B3B4) = 08010056 0D010076 00000002 01000022 00000002 00000002
--//还可以猜测第3，4的01表示正在访问吗？还有没有对象都是00000002。
--//hash=6780对应值等于01000022 ,前面的01表示存在1个对象.
SYS@book> @ dc/dc_objects "hash in (-1,6780,6779,6781) "
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 WRH$_SQLSTAT_BL_PK 18 0x1a7c 2287 6780 0000000072480F00 8 dc_objects Y 0 0 00 00 0 0 00 00 1
--//没有对象的对应的都是00000002.
--//中断测试的执行,刷新共享池看看.
SYS@book> alter system flush SHARED_POOL;
System altered.
SYS@book> @ opeek 0x7798b39c 24 0
[07798B39C, 07798B3B4) = 0800006E 0D000098 00000002 0100002C 00000002 00000002
--//第3,4位上的01变成00.
--//剩下5-8位表示什么，不好猜测.
--//剩下一个疑问，如果知道某个对象的hash值 row cache 如何定位呢。
--//以前library cache bucket mutex，每个mutext的前面16字节包含对象的首尾句柄地址，通过链接的形式连接起来。
--//row cache mutex对应的chunk没有包含对象的首尾句柄地址信息。
--//另外写一篇blog分析。
5.附上测试使用代码：
$ cat tpt/mutexprofz.sql
--------------------------------------------------------------------------------
--
-- File name: mutexprof.sql ( Mutex sleep Profiler )
--
-- Purpose: Display KGX mutex sleep history from v$mutex_sleep_history
-- along library cache object names protected by these mutexes.
-- Only top 20 rows are shown by default
--
-- Author: Tanel Poder
-- Copyright: (c) http://www.tanelpoder.com
--
-- Usage: @mutexprof <grouping columns> <filter condition>
--
-- The main grouping (and filtering) columns are:
--
-- id - mutex ID (which is the object hash value for library
-- cache object mutexes)
-- ts - timestamp of mutex sleep beginning
-- loc - code location where the waiter slept for the mutex
-- val - mutex value (shows whether mutex was held in exclusive or
-- shared mode)
-- req - requesting session SID
-- blk - blocking session SID
--
-- The filter condition allows filtering mutex sleep rows based on certain
-- criteria, such:
--
-- 1=1 - show all mutex sleeps (which are still in memory)
-- blk=123 - show only these mutex sleeps where blocking sid was 123
-- hash=2741853041 - show only these sleeps where mutex ID (KGL object hash value)
-- was 2741853041
--
--
-- Its also possible to have multiple "AND" filter conditions, as long as you keep
-- them in double quotes so that sqlplus would recognize them as one parameter
--
-- For example: "name like '%DUAL%' and blk in (115,98)"
--
-- Examples:
--
-- @mutexprof loc 1=1
-- @mutexprof id,loc,req,blk "lower(name) like 'select%from dual%'"
-- @mutexprof loc,val blk=98
-- @mutexprof id,loc,req,blk "blk in (select sid from v$session where username = 'SYS')"
--
-- Other: When the relevant object is aged out you will see (name not found)
-- as object_name.
--
-- On 10.2.0.1 the V$mutex_sleep_history does not have mutex_identifier
-- column externalized. In this case use X$mutex_sleep_history instead
--
--------------------------------------------------------------------------------
col msh_obj_name head OBJECT_NAME for a80 word_wrap
col msh_mutex_type head MUTEX_TYPE for a15 truncate
col loc head GET_LOCATION for a33 truncate
col mutexprof_gets head GETS_DIFF for 9999999999999
col mutexprof_sleeps head SLEEPS for 999999
col mutexprof_p2 head P2 for a16 wrap
col mutexprof_p3 head P3 for a16 wrap
col mutexprof_p4 head P4 for a16 wrap
col mutexprof_p5 head P5 for a20 wrap
col maddr head mutex_addr for a20 wrap
def MSH_NUMROWS=50
select * from (
select /*+ ORDERED USE_NL(o) */
-- TODO the sleep/get counting needs fixing!
--MAX(sleeps) sleeps
sum(sleeps) sum_sleeps
--count(*) sleeps
--, decode(max(sleeps)-min(sleeps),0,to_number(null),max(sleeps)-min(sleeps)) mutexprof_sleeps -- may not be very accurate but give an idea
, decode(max(gets)-min(gets),0,to_number(null),max(gets)-min(gets)) mutexprof_gets -- may not be very accurate but give an idea
-- avg(sleeps) sleeps
--, avg(gets) gets
, mutex_type msh_mutex_type
, &1
, sql_id
, replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') msh_obj_name
--, nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)') msh_obj_name
--, p1raw
--, CASE WHEN p2 < 536870912 THEN TO_CHAR(p2) ELSE TRIM(TO_CHAR(p2, 'XXXXXXXXXXXXXXXX')) END mutexprof_p2
--, CASE WHEN p3 < 536870912 THEN TO_CHAR(p3) ELSE TRIM(TO_CHAR(p3, 'XXXXXXXXXXXXXXXX')) END mutexprof_p3
--, CASE WHEN p4 < 536870912 THEN TO_CHAR(p4) ELSE TRIM(TO_CHAR(p4, 'XXXXXXXXXXXXXXXX')) END mutexprof_p4
--, p5 mutexprof_p5
from
(select
mutex_identifier id
, mutex_identifier idn
, to_char (mutex_identifier, 'fm0XXXXXXX') idnhex
, sleep_timestamp ts
, mutex_type
, gets
, sleeps
, requesting_session req
, blocking_session blk
, location loc
, mutex_value val
, mutex_addr maddr
, p1
, p1raw
, p2
, p3
, p4
, p5
from x$mutex_sleep_history) m
, (select kglnahsh, kglnahsh hash_value, kglnahsh hash,
kglhdpar, kglhdadr, kglnaown, kglnaobj, kglobt03 sql_id,kglhdbid kgl_bucket,
decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj) object_name,
decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj) name
from x$kglob) o
where
m.id = o.kglnahsh (+)
and (o.kglhdadr = o.kglhdpar or (o.kglhdpar is null)) -- only parent KGL objects if still in cache
and &2
group by
mutex_type
, &1
, sql_id
, kglnaown
, kglnaobj
, p1raw
, CASE WHEN p2 < 536870912 THEN TO_CHAR(p2) ELSE TRIM(TO_CHAR(p2, 'XXXXXXXXXXXXXXXX')) END
, CASE WHEN p3 < 536870912 THEN TO_CHAR(p3) ELSE TRIM(TO_CHAR(p3, 'XXXXXXXXXXXXXXXX')) END
, CASE WHEN p4 < 536870912 THEN TO_CHAR(p4) ELSE TRIM(TO_CHAR(p4, 'XXXXXXXXXXXXXXXX')) END
--, p5
order by
1 desc
-- sleeps desc
)
where rownum <= &MSH_NUMROWS
/
$ cat dc/dc_objects.sql
column DC_PROP_NAME format a28
column CACHE_NAME format a20
column EXISTENT format a8
column KEY_OID$ format a32
column dc_obj_name format a32
column dc_obj_name1 format a32
column username format a20
column con_id format 99999
column indx format 99999
column hash format 99999
column key noprint
SELECT *
FROM (SELECT --TO_NUMBER ( (SUBSTR (key, 7, 2) || SUBSTR (key, 5, 2) || SUBSTR (key, 3, 2) || SUBSTR (key, 1, 2)), 'XXXXXXXX') Schema_User_ID ,
(SELECT username
FROM cdb_users
WHERE con_id= v.con_id and user_id =
TO_NUMBER (
(SUBSTR (key, 7, 2) || SUBSTR (key, 5, 2) || SUBSTR (key, 3, 2) || SUBSTR (key, 1, 2))
,'XXXXXXXX'))
username
--,RTRIM (UTL_RAW.cast_to_varchar2 (SUBSTR (v.key, 13)), CHR (0)) dc_obj_name
--,TO_NUMBER (TRIM (BOTH '0' FROM SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2)), 'XXXX') key_str_len
,UTL_RAW.cast_to_varchar2 (SUBSTR (v.key, 13,2*TO_NUMBER ( SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2), 'XXXX'))) dc_obj_name
,TO_NUMBER ( SUBSTR (key, 11, 2) || SUBSTR (key, 9, 2), 'XXXX') key_str_len
,'0x'||to_char(v.hash,'FMxxxxx') hash_hex
,v.*
FROM v$rowcache_parent v
WHERE cache# in (8,11)
)
WHERE (&1)
ORDER BY key;
column key print
column hash clear
$ cat opeek.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- Name: opeek.sql
-- Purpose: oradebug peek
-- Author: lfree
-- Usage:
-- @ opeek <addr> <length> <1|0>
-- argv3 : 1 -- write tracename and display 0 -- display
--------------------------------------------------------------------------------
set term off
col 1 new_value 1
select replace(lower('&&1'),'x','') "1" from dual;
column tt new_value var
select decode(&&3,1,'ti','zero') tt from dual;
oradebug setmypid
set term on
@ &var.
--@ ti
oradebug peek 0x&1 &2 &3
prompt
浙公网安备 33010602011771号