[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

相關文章