ITPub博客

首页 > 数据库 > Oracle > [20210903]探究mutex的值.txt

[20210903]探究mutex的值.txt

原创 Oracle 作者:lfree 时间:2021-09-08 08:33:37 0 删除 编辑

[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数量.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2790925/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3031
  • 访问量
    6775249