[20210903]探究mutex的值.txt
--//前一段时间做library_cache转储时，在bucket后面跟着Mutex，类似信息如下。
Bucket: #=102650 Mutex=0x80528f40(0, 19, 0, 6)
--//注：11g 下每个library cache bucket占用16字节，后面跟着mutex，mutex结构占用24字节，这样整个占用40字节。可以参考我前面
--//的测试 [20210524]分析library cache转储 3.txt
--//我可以通过oradebug poke相应地址，然后dump确定mutex地址后面括号的值来自那里。
--//在测试前先贴一个以前的oradebug peek的内容：
SYS@book> oradebug peek 0x80528f40 40
[080528F40, 080528F68) = 00000001 00000000 0000092B 00042180 000190FA 00000006 80528F58 00000000 80528F58 00000000
--//我当时推断0000092B=>表示get, 00042180=>表示sleep,000190FA=102650=>表示bucket值,最后00000006 不知道.
--//前面括号里面的内容猜测应该对应00000001,0000092B,00042180,00000006,也就是0~3,8~11,12~15,20~23字节.
--//我记忆里0-3=>表示mutex阻塞的sid,4~7=>表示并发访问的数量。
--//通过例子验证看看.
1.环境：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//执行5次以上。
SCOTT@book> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
95129850 80baj2c2ur47u 0 5ab90fa
--//95129850%131072 = 102650
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_27978_0001.trc
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump library_cache 10;
Statement processed.
2.测试：
--//首先我不知道为什么最后1个值总是6，可以简单通过如下命令验证。
$ awk '/^Bucket:/{print $6}' book_ora_27978_0001.trc | uniq -c
4322 6)
--//以前的测试我就知道这样的情况,为什么是6我不是很清楚，表示什么更加不清楚。
$ awk '/^Bucket:/{print $3}' book_ora_27978_0001.trc | cut -d"(" -f2|uniq -c
4322 0,
--//第1个总是0.我估计表示阻塞会话的sid.
--//检索转储文件.查询字串Bucket: #=102650,发现如下:
Bucket: #=102650 Mutex=0x80528f40(0, 5, 0, 6)
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000005 00000000 000190FA 00000000
--//执行如下:
oradebug poke 0x0000000080528f40 4 0x00000000
oradebug poke 0x0000000080528f44 4 0x00000000
oradebug poke 0x0000000080528f48 4 0x00000030
oradebug poke 0x0000000080528f4c 4 0x00000004
oradebug poke 0x0000000080528f50 4 0x000190FA
oradebug poke 0x0000000080528f54 4 0x00000007
--//注:我反复测试多次,前面两个poke如果不是0,dump library_cache时挂起!!
SYS@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
44 33 27977 DEDICATED 27978 27 11 alter system kill session '44,33' immediate;
--//打开新会话执行如下:
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS CLIENT_INFO
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- --------------------
0000000000019000 0000000200000001 000000000000001F 102400 8589934593 31 44 33 83 library cache: mutex X INACTIVE WAITING 2633137 3 Concurrency
--//8589934593 = /2^16 %2^16 (Type | Mode) = 131072,1 = 0x200000001(与我poke的值一样)
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000030 00000004 00019000 00000007
#############################################
--//小插曲,前面有一次计算地址错误,修改oradebug poke 0x0000000080528f4d 4 0x00000004,导致修改错误.
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000030 00000400 000190FA 00000007
~~~~~~~~
--//注意看下划!!!!
#############################################
SYS@book> oradebug poke 0x0000000080528f4c 4 0x00000004
BEFORE: [080528F4C, 080528F50) = 00000004
AFTER: [080528F4C, 080528F50) = 00000004
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000030 00000004 000190FA 00000007
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_27978_0003.trc
SYS@book> oradebug dump library_cache 10;
Statement processed.
--//检索转储文件.查询字串Bucket: #=102650,发现如下:
Bucket: #=102650 Mutex=0x80528f40(0, 50, 4, 6)
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000036 00000004 000190FA 00000000
--// 0x36 = 54
3.继续重来:
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 0000003B 00004890 000190FA 00000000
oradebug poke 0x0000000080528f44 4 0x00000002
oradebug poke 0x0000000080528f48 4 0x00000055
oradebug poke 0x0000000080528f4c 4 0x00200004
oradebug poke 0x0000000080528f50 4 0x000190FA
oradebug poke 0x0000000080528f54 4 0x00000077
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000002 00000055 00200004 000190FA 00000077
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_28288_0001.trc
SYS@book> oradebug dump library_cache 8;
--//挂起.
--//打开新session.
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS CLIENT_INFO
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- --------------------
00000000000190FA 0000000200000000 000000000000001F 102650 8589934592 31 44 35 78 library cache: mutex X INACTIVE WAITING 20259070 20 Concurrency
SYS@book> select * from v$mutex_sleep_history where mutex_identifier=102650
2 @ prxx
==============================
MUTEX_IDENTIFIER : 102650
SLEEP_TIMESTAMP : 2021-09-07 10:10:24.439911
MUTEX_TYPE : Library Cache
GETS : 85 --//85 = 0x55
SLEEPS : 1589412 --//1589412 = 0x1840a4
REQUESTING_SESSION : 44
BLOCKING_SESSION : 2
LOCATION : kgldmc1 31 --//我总感觉这个location不熟悉缩略写,很难猜测问题在那里.
MUTEX_VALUE : 0000000200000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
P1 : 0
P1RAW : 00
P2 : 0
P3 : 0
P4 : 0
P5 :
--//在打开新会话,执行如下:
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug poke 0x0000000080528f44 4 0x00000000
BEFORE: [080528F44, 080528F48) = 00000002
AFTER: [080528F44, 080528F48) = 00000000
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000056 0020225A 000190FA 00000000
--//检索转储文件.查询字串Bucket: #=102650,发现如下:
Bucket: #=102650 Mutex=0x80528f40(0, 86, 2105946, 6)
--//86 = 0x56
--//2105946 = 0x20225a
--//可以基本确定8~11,12~15 对应gets,sleep3.似乎转储后最后4位会变成0.我估计后面6应该就是对应20~23字节.
--//基本可以确定mutex括号里面的内容猜测应该对应第一位不知道,8~11,12~15,20~23字节.
SCOTT@book> select * from v$mutex_sleep_history where mutex_identifier=102650
2 @ prxx
==============================
MUTEX_IDENTIFIER : 102650
SLEEP_TIMESTAMP : 2021-09-07 09:47:26.770924
MUTEX_TYPE : Library Cache
GETS : 57
SLEEPS : 1580510
REQUESTING_SESSION : 44
BLOCKING_SESSION : 2
LOCATION : kgldmc1 31
MUTEX_VALUE : 0000000200000000
P1 : 0
P1RAW : 00
P2 : 0
P3 : 0
P4 : 0
P5 :
PL/SQL procedure successfully completed.
4.继续:
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000059 00202A39 000190FA 00000000
SYS@book> oradebug poke 0x0000000080528f40 4 0x0000001
BEFORE: [080528F40, 080528F44) = 00000000
AFTER: [080528F40, 080528F44) = 00000001
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000001 00000000 00000059 00202A39 000190FA 00000000
--//59 = 89
--//00202A39 = 2107961
--//打开新session:
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
295 11 28515 DEDICATED 28516 21 6 alter system kill session '295,11' immediate;
SCOTT@book> select * from dept where deptno=20;
--//挂起.
SYS@book> select * from v$mutex_sleep_history where mutex_identifier=102650
2 @ prxx
MUTEX_IDENTIFIER : 102650
SLEEP_TIMESTAMP : 2021-09-07 10:33:43.614413
MUTEX_TYPE : Library Cache
GETS : 89
SLEEPS : 2077854
REQUESTING_SESSION : 295
BLOCKING_SESSION : 0
LOCATION : kglhdgn1 62
MUTEX_VALUE : 0000000000000001
P1 : 0
P1RAW : 00
P2 : 0
P3 : 0
P4 : 0
P5 :
PL/SQL procedure successfully completed.
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS CLIENT_INFO
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- --------------------
00000000000190FA 0000000000000001 000000000000003E 102650 1 62 295 11 63427 library cache: mutex X ACTIVE WAITING 666 0 Concurrency
--//放弃,探究有点乱.
5.总结:
--//前面两个poke如果不是0,dump library_cache时总是挂起!!
--//基本确定8~11,12~15 对应gets,sleep3,也就是例如:Bucket: #=102650 Mutex=0x80528f40(0, 19, 0, 6),第2,3数字表示gets,sleeps数量.
[20210903]探究mutex的值.txt
-
lfree
2021-09-08 08:33:37 -
Oracle
- 原创