[20210914]探究mutex的值 5.txt
--//前几天做了做library_cache转储时,显示的mutex结构体里面相关信息的探究.
Bucket: #=102650 Mutex=0x80528f40(0, 19, 0, 6)
--//注：11g 下每个library cache bucket占用16字节，后面跟着mutex，mutex结构占用24字节，这样整个占用40字节。可以参考我前面
--//的测试 [20210524]分析library cache转储 3.txt
--//大概猜测出第2,3数字表示gets,sleeps的数量.后面的dump显示总是6,不知道为什么我感觉应该对应mutex结构体的20~23字节.
--//前面第1个数字,转储总是显示0,如果使用oradebug poke前面0~3,4-7字节非0,dump总是挂起.导致我无法猜测第1个数字表示什么.
oradebug poke 0x0000000080528f40 4 0x00000001
oradebug poke 0x0000000080528f44 4 0x00000002
--//我仔细看了以前我的测试,发现以前在理解上出现一些偏差,这个测试主要目的看看muetx的0-3,4-7到底那个是表示sid,那个是ref count.
--//通过例子说明问题:
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
SYS@book> alter system set session_cached_cursors=0 scope=spfile;
System altered.
--//重启略,设置目的主要保证每次都是软解析,这样都会访问library cache mutex.
--//session 1:
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
295 5 9539 DEDICATED 9540 21 3 alter system kill session '295,5' immediate;
--//sid=295 = 0x127.
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,确定library cache mutex 地址如下,通过转储library cache,过程略.
oradebug setmypid
oradebug dump library_cache 10;
--//检查转储,搜索Bucket: #=102650,发现如下:
Bucket: #=102650 Mutex=0x80528f40(0, 6, 0, 6)
2.测试:
--//session 2:
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000051 0000AE84 000190FA 00000000
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000127
BEFORE: [080528F40, 080528F44) = 00000000
AFTER: [080528F40, 080528F44) = 00000127
SYS@book> oradebug poke 0x0000000080528f44 4 0x00000127
BEFORE: [080528F44, 080528F48) = 00000000
AFTER: [080528F44, 080528F48) = 00000127
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000127 00000127 00000051 0000AE84 000190FA 00000000
--//0x51 = 81
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//你可以发现sid=127并没有阻塞.
--//session 2:
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000127 00000127 00000051 0000AE84 000190FA 00000000
--//0x51 = 81,可以发现并没有增加.
SYS@book> oradebug poke 0x0000000080528f44 4 0x00000000
BEFORE: [080528F44, 080528F48) = 00000127
AFTER: [080528F44, 080528F48) = 00000000
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000127 00000000 00000051 0000AE84 000190FA 00000000
--//session 1:
SCOTT@book> select * from dept where deptno=20;
..
--//这样反而挂起.
--//session 2, 修改回来.
SYS@book> oradebug poke 0x0000000080528f44 4 0x00000127
BEFORE: [080528F44, 080528F48) = 00000000
AFTER: [080528F44, 080528F48) = 00000127
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000127 00000127 00000051 0000AE84 000190FA 00000000
--//还是挂起.
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000000
BEFORE: [080528F40, 080528F44) = 00000127
AFTER: [080528F40, 080528F44) = 00000000
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000127 00000051 0000AE84 000190FA 00000000
--//还是挂起.
SYS@book> oradebug poke 0x0000000080528f44 4 0x00000000
BEFORE: [080528F44, 080528F48) = 00000127
AFTER: [080528F44, 080528F48) = 00000000
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000052 0001819C 000190FA 00000000
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//到底那个表示sid,ref count我还是猜测不出来.
3.继续:
--//打开新会话,执行:
--//session 3:
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
58 85 10235 DEDICATED 10236 28 36 alter system kill session '58,85' immediate;
--//58 = 0x3a
--//session 2:
oradebug poke 0x0000000080528f40 4 0x0000003a
oradebug poke 0x0000000080528f44 4 0x0000003a
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 0000003A 0000003A 0000005B 0001819C 000190FA 00000000
--//session 3:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//session 2:
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000000
BEFORE: [080528F40, 080528F44) = 0000003A
AFTER: [080528F40, 080528F44) = 00000000
SYS@book> oradebug poke 0x0000000080528f44 4 0x0000003a
BEFORE: [080528F44, 080528F48) = 0000003A
AFTER: [080528F44, 080528F48) = 0000003A
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 0000003A 0000005B 0001819C 000190FA 00000000
--//session 3:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//可以正常执行,也就是第4-7字节=58(sid)的情况下可以正常执行.
SYS@book> oradebug poke 0x0000000080528f44 4 0x00000127
BEFORE: [080528F44, 080528F48) = 0000003A
AFTER: [080528F44, 080528F48) = 00000127
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000127 0000005B 0001819C 000190FA 00000000
--//session 3:
SCOTT@book> select * from dept where deptno=20;
--//挂起!!
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//你可以发现在这样的情况下sid=297(0x127)的会话可以正常执行.
--//session 2:
SYS@book> oradebug poke 0x0000000080528f44 4 0x00000000
BEFORE: [080528F44, 080528F48) = 00000127
AFTER: [080528F44, 080528F48) = 00000000
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 0000005B 0001B0F9 000190FA 00000000
--//session 3:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//从这个测试可以看出第4-7字节应该表示sid. 这样0-3 表示ref count.
4.再继续:
--//session 2:
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000001
BEFORE: [080528F40, 080528F44) = 00000000
AFTER: [080528F40, 080528F44) = 00000001
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000001 00000000 00000060 0002CA11 000190FA 00000000
--//session 1:
SCOTT@book> select * from dept where deptno=20;
--//session 3:
SCOTT@book> select * from dept where deptno=20;
--//挂起!!
--//session 2:
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 58 85 31967 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency
00000000000190FA 0000000000000001 000000000000003E 102650 1 62 295 5 16799 library cache: mutex X ACTIVE WAITED SHORT TIME 4 0 Concurrency
SYS@book> @ ev_name "library cache: mutex X"
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
289 1646780882 library cache: mutex X idn value where 3875070507 4 Concurrency
--//P2=1 表示muext的第4-7,0-3字节信息,注意intel系列CPU大小头问题.另外如果修改8个字节,特别注意这个问题,比如:
SYS@book> oradebug poke 0x0000000080528f40 8 0x0000000100000002
BEFORE: [080528F40, 080528F48) = 00000000 00000000
AFTER: [080528F40, 080528F48) = 00000002 00000001
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000002 00000001 00000062 000486F7 000190FA 00000000
--//0x00000002在前,0x00000001在后
--//不知道为什么我一直以为这样的情况如果前面是00000002 00000000时不会阻塞,看来我搞错了.
SYS@book> @ tpt/ash/ash_wait_chains BLOCKING_SESSION||','||BLOCKING_SESSION_SERIAL#||'@'||BLOCKING_INST_ID||'=>'||session_id||','||SESSION_SERIAL#||'@'||inst_id||'=>'||event 1=1 sysdate-1/1440 sysdate
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN
------ ---------- ---------- ----------------------------------------------------------------------
32% 37 .6 -> ,@=>58,85@1=>
28% 32 .5 -> ,@=>295,5@1=>
23% 26 .4 -> ,@=>295,5@1=>library cache: mutex X
17% 19 .3 -> ,@=>58,85@1=>library cache: mutex X
1% 1 0 -> ,@=>44,23@1=>
5.知道这些第4-7字节等于dump会话的sid,应该不会阻塞:
--//从测试可以看出mutex结构体,第0-3字节表示ref count,第4-7字节表示阻塞的持有的sid???
--//如果第3-7字节等于执行会话sid,可以正常执行,有点奇怪为什么是不出现ora-04024错误.也许这里不是cursor的原因,或者mutex类型不一样.
$ oerr ora 04024
04024, 00000, "self-deadlock detected while trying to mutex pin cursor %s"
// *Cause: While trying to mutex pin a cursor, a self-deadlock is detected.
// *Action: Retry the operation later.
SYS@book> oradebug poke 0x0000000080528f40 8 0x0000000000000000
BEFORE: [080528F40, 080528F48) = 00000002 00000001
AFTER: [080528F40, 080528F48) = 00000000 00000000
--//session 2:
SYS@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
44 23 9542 DEDICATED 9543 27 9 alter system kill session '44,23' immediate;
--//44 = 0x2c
SYS@book> oradebug poke 0x0000000080528f40 8 0x0000002c00000000
BEFORE: [080528F40, 080528F48) = 00000000 00000000
AFTER: [080528F40, 080528F48) = 00000000 0000002C
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 0000002C 00000062 000486F7 000190FA 00000000
SYS@book> oradebug dump library_cache 8;
Statement processed.
--//ok,,在sid=第3-7字节内容的情况下,可以dump.
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 0000002C 00000062 000486F7 000190FA 00000000
--//检查转储文件内容.
Bucket: #=102650 Mutex=0x80528f40(0, 98, 296695, 0)
--//98 = 0x62,这样的情况gets计数不增加,是否可以理解为独占持有该mutex.
LibraryHandle: Address=0x7d3c9600 Hash=5ab90fa LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select * from dept where deptno=20
FullHashValue=e8ec445edab00042802d511305ab90fa Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=95129850 OwnerIdn=83
Statistics: InvalidationCount=0 ExecutionCount=97 LoadCount=2 ActiveLocks=1 TotalLockCount=97 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=96 HandleInUse=96 HandleReferenceCount=0
Concurrency: DependencyMutex=0x7d3c96b0(0, 4, 0, 0) Mutex=0x7d3c9740(44, 444, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x7d3c9690[0x7d3c9690,0x7d3c9690]
Pin=0x7d3c9670[0x7d3c9670,0x7d3c9670]
LoadLock=0x7d3c96e8[0x7d3c96e8,0x7d3c96e8]
Timestamp: Current=09-14-2021 09:20:40
HandleReference: Address=0x7d3c97d0 Handle=(nil) Flags=[00]
ReferenceList:
Reference: Address=0x7d06d128 Handle=0x7d6c0cc8 Flags=ROD[21]
LibraryObject: Address=0x7d06ee18 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^5ab90fa pins=0 Change=NONE
Heap=0x7c185b68 Pointer=0x7d06eeb8 Extent=0x7d06ed98 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=2.437500 Size=3.976562 LoadTime=22924574690
ChildTable: size='16'
Child: id='0' Table=0x7d06fcc8 Reference=0x7d06f708 Handle=0x7d5e6c68
NamespaceDump:
Parent Cursor: sql_id=80baj2c2ur47u parent=0x7d06eeb8 maxchild=1 plk=y ppn=n
SYS@book> oradebug poke 0x0000000080528f40 8 0x0000002c00000011
BEFORE: [080528F40, 080528F48) = 00000000 0000002C
AFTER: [080528F40, 080528F48) = 00000011 0000002C
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000011 0000002C 00000062 000486F7 000190FA 00000000
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_9543_0001.trc
SYS@book> oradebug dump library_cache 8;
Statement processed.
--//检查转储文件内容.
Bucket: #=102650 Mutex=0x80528f40(11, 98, 296695, 0)
--//98 = 0x62,
--//11? 嗯这里是16进制吗?正常应该显示17才对啊,oracle怎么这样dump呢?
LibraryHandle: Address=0x7d3c9600 Hash=5ab90fa LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select * from dept where deptno=20
FullHashValue=e8ec445edab00042802d511305ab90fa Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=95129850 OwnerIdn=83
Statistics: InvalidationCount=0 ExecutionCount=97 LoadCount=2 ActiveLocks=1 TotalLockCount=97 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=96 HandleInUse=96 HandleReferenceCount=0
Concurrency: DependencyMutex=0x7d3c96b0(0, 5, 0, 0) Mutex=0x7d3c9740(44, 445, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x7d3c9690[0x7d3c9690,0x7d3c9690]
Pin=0x7d3c9670[0x7d3c9670,0x7d3c9670]
LoadLock=0x7d3c96e8[0x7d3c96e8,0x7d3c96e8]
Timestamp: Current=09-14-2021 09:20:40
HandleReference: Address=0x7d3c97d0 Handle=(nil) Flags=[00]
ReferenceList:
Reference: Address=0x7d06d128 Handle=0x7d6c0cc8 Flags=ROD[21]
LibraryObject: Address=0x7d06ee18 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^5ab90fa pins=0 Change=NONE
Heap=0x7c185b68 Pointer=0x7d06eeb8 Extent=0x7d06ed98 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=2.437500 Size=3.976562 LoadTime=22924574690
ChildTable: size='16'
Child: id='0' Table=0x7d06fcc8 Reference=0x7d06f708 Handle=0x7d5e6c68
NamespaceDump:
Parent Cursor: sql_id=80baj2c2ur47u parent=0x7d06eeb8 maxchild=1 plk=y ppn=n
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_9543_0002.trc
SYS@book> oradebug poke 0x0000000080528f40 4 0x000001ab
BEFORE: [080528F40, 080528F44) = 00000100
AFTER: [080528F40, 080528F44) = 000001AB
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 000001AB 0000002C 00000062 000486F7 000190FA 00000000
SYS@book> oradebug dump library_cache 8;
Statement processed.
--//检查转储文件内容.
Bucket: #=102650 Mutex=0x80528f40(1ab, 98, 296695, 0)
--//oracle这里很奇葩,mutex的值第1个数字竟然是16进制的.
LibraryHandle: Address=0x7d3c9600 Hash=5ab90fa LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select * from dept where deptno=20
FullHashValue=e8ec445edab00042802d511305ab90fa Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=95129850 OwnerIdn=83
Statistics: InvalidationCount=0 ExecutionCount=97 LoadCount=2 ActiveLocks=1 TotalLockCount=97 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=96 HandleInUse=96 HandleReferenceCount=0
Concurrency: DependencyMutex=0x7d3c96b0(0, 7, 0, 0) Mutex=0x7d3c9740(44, 447, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x7d3c9690[0x7d3c9690,0x7d3c9690]
Pin=0x7d3c9670[0x7d3c9670,0x7d3c9670]
LoadLock=0x7d3c96e8[0x7d3c96e8,0x7d3c96e8]
Timestamp: Current=09-14-2021 09:20:40
HandleReference: Address=0x7d3c97d0 Handle=(nil) Flags=[00]
ReferenceList:
Reference: Address=0x7d06d128 Handle=0x7d6c0cc8 Flags=ROD[21]
LibraryObject: Address=0x7d06ee18 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^5ab90fa pins=0 Change=NONE
Heap=0x7c185b68 Pointer=0x7d06eeb8 Extent=0x7d06ed98 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=2.437500 Size=3.976562 LoadTime=22924574690
ChildTable: size='16'
Child: id='0' Table=0x7d06fcc8 Reference=0x7d06f708 Handle=0x7d5e6c68
NamespaceDump:
Parent Cursor: sql_id=80baj2c2ur47u parent=0x7d06eeb8 maxchild=1 plk=y ppn=n
6.总结:
--//至此可以知道dump library cache,mutex的值应该表示mutex结构体的第0-3,第8-11,第12-15,第20-23字节.分别对应ref count,gets,sleep.???.
--//同样道理mutex结构体24字节里面的信息(各占4个字节)分别表示ref_count,sid,gets,sleeps,mutex标识(这里对应bucket值,sql语句可能对应hash_value),???.
--//最后第20-23,我还是不知道表示什么?
[20210914]探究mutex的值 5.txt
-
lfree
2021-09-14 11:10:07 -
Oracle
- 原创