[20210914]探究mutex的值 5.txt

来源：这里教程网时间：2026-03-03 16:56:10 作者：

[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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推