[20210914]探究mutex的值 5.txt

lfree 發表於 2021-09-14

[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.環境:
[email protected]> @ 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

[email protected]> alter system set session_cached_cursors=0 scope=spfile;
System altered.

--//重啟略,設定目的主要保證每次都是軟解析,這樣都會訪問library cache mutex.
--//session 1:
[email protected]> @ 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.

[email protected]> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
--//執行5次以上。

[email protected]> @ 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:
[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000051 0000AE84 000190FA 00000000

[email protected]> oradebug poke 0x0000000080528f40 4 0x00000127
BEFORE: [080528F40, 080528F44) = 00000000
AFTER:  [080528F40, 080528F44) = 00000127

[email protected]> oradebug poke 0x0000000080528f44 4 0x00000127
BEFORE: [080528F44, 080528F48) = 00000000
AFTER:  [080528F44, 080528F48) = 00000127

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000127 00000127 00000051 0000AE84 000190FA 00000000
--//0x51 = 81

--//session 1:
[email protected]> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

--//你可以發現sid=127並沒有阻塞.

--//session 2:
[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000127 00000127 00000051 0000AE84 000190FA 00000000
--//0x51 = 81,可以發現並沒有增加.

[email protected]> oradebug poke 0x0000000080528f44 4 0x00000000
BEFORE: [080528F44, 080528F48) = 00000127
AFTER:  [080528F44, 080528F48) = 00000000

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000127 00000000 00000051 0000AE84 000190FA 00000000

--//session 1:
[email protected]> select * from dept where deptno=20;
..

--//這樣反而掛起.

--//session 2, 修改回來.
[email protected]> oradebug poke 0x0000000080528f44 4 0x00000127
BEFORE: [080528F44, 080528F48) = 00000000
AFTER:  [080528F44, 080528F48) = 00000127
[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000127 00000127 00000051 0000AE84 000190FA 00000000

--//還是掛起.
[email protected]> oradebug poke 0x0000000080528f40 4 0x00000000
BEFORE: [080528F40, 080528F44) = 00000127
AFTER:  [080528F40, 080528F44) = 00000000

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000127 00000051 0000AE84 000190FA 00000000
--//還是掛起.

[email protected]> oradebug poke 0x0000000080528f44 4 0x00000000
BEFORE: [080528F44, 080528F48) = 00000127
AFTER:  [080528F44, 080528F48) = 00000000

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000052 0001819C 000190FA 00000000

--//session 1:
[email protected]> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

--//到底那個表示sid,ref count我還是猜測不出來.

3.繼續:
--//開啟新會話,執行:
--//session 3:
[email protected]> @ 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

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 0000003A 0000003A 0000005B 0001819C 000190FA 00000000

--//session 3:
[email protected]> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

--//session 2:
[email protected]> oradebug poke 0x0000000080528f40 4 0x00000000
BEFORE: [080528F40, 080528F44) = 0000003A
AFTER:  [080528F40, 080528F44) = 00000000
[email protected]> oradebug poke 0x0000000080528f44 4 0x0000003a
BEFORE: [080528F44, 080528F48) = 0000003A
AFTER:  [080528F44, 080528F48) = 0000003A
[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 0000003A 0000005B 0001819C 000190FA 00000000

--//session 3:
[email protected]> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

--//可以正常執行,也就是第4-7位元組=58(sid)的情況下可以正常執行.

[email protected]> oradebug poke 0x0000000080528f44 4 0x00000127
BEFORE: [080528F44, 080528F48) = 0000003A
AFTER:  [080528F44, 080528F48) = 00000127

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000127 0000005B 0001819C 000190FA 00000000

--//session 3:
[email protected]> select * from dept where deptno=20;

--//掛起!!

--//session 1:
[email protected]> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

--//你可以發現在這樣的情況下sid=297(0x127)的會話可以正常執行.
--//session 2:
[email protected]> oradebug poke 0x0000000080528f44 4 0x00000000
BEFORE: [080528F44, 080528F48) = 00000127
AFTER:  [080528F44, 080528F48) = 00000000

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 0000005B 0001B0F9 000190FA 00000000

--//session 3:
[email protected]> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

--//從這個測試可以看出第4-7位元組應該表示sid. 這樣0-3 表示ref count.

4.再繼續:
--//session 2:
[email protected]ok> oradebug poke 0x0000000080528f40 4 0x00000001
BEFORE: [080528F40, 080528F44) = 00000000
AFTER:  [080528F40, 080528F44) = 00000001
[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000001 00000000 00000060 0002CA11 000190FA 00000000

--//session 1:
[email protected]> select * from dept where deptno=20;

--//session 3:
[email protected]> select * from dept where deptno=20;

--//掛起!!

--//session 2:
[email protected]> @ 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

[email protected]> @ 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個位元組,特別注意這個問題,比如:
[email protected]> oradebug poke 0x0000000080528f40 8 0x0000000100000002
BEFORE: [080528F40, 080528F48) = 00000000 00000000
AFTER:  [080528F40, 080528F48) = 00000002 00000001

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000002 00000001 00000062 000486F7 000190FA 00000000
--//0x00000002在前,0x00000001在後
--//不知道為什麼我一直以為這樣的情況如果前面是00000002 00000000時不會阻塞,看來我搞錯了.

[email protected]> @ 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,[email protected]=>
  28%          32         .5 -> ,@=>295,[email protected]=>
  23%          26         .4 -> ,@=>295,[email protected]=>library cache: mutex X
  17%          19         .3 -> ,@=>58,[email protected]=>library cache: mutex X
   1%           1          0 -> ,@=>44,[email protected]=>

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.

[email protected]> oradebug poke 0x0000000080528f40 8 0x0000000000000000
BEFORE: [080528F40, 080528F48) = 00000002 00000001
AFTER:  [080528F40, 080528F48) = 00000000 00000000

--//session 2:
[email protected]> @ 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


[email protected]> oradebug poke 0x0000000080528f40 8 0x0000002c00000000
BEFORE: [080528F40, 080528F48) = 00000000 00000000
AFTER:  [080528F40, 080528F48) = 00000000 0000002C

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 0000002C 00000062 000486F7 000190FA 00000000

[email protected]> oradebug dump library_cache 8;
Statement processed.
--//ok,,在sid=第3-7位元組內容的情況下,可以dump.

[email protected]> 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

[email protected]> oradebug poke 0x0000000080528f40 8 0x0000002c00000011
BEFORE: [080528F40, 080528F48) = 00000000 0000002C
AFTER:  [080528F40, 080528F48) = 00000011 0000002C

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000011 0000002C 00000062 000486F7 000190FA 00000000

[email protected]> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_9543_0001.trc

[email protected]> 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


[email protected]> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_9543_0002.trc

[email protected]> oradebug poke 0x0000000080528f40 4 0x000001ab
BEFORE: [080528F40, 080528F44) = 00000100
AFTER:  [080528F40, 080528F44) = 000001AB

[email protected]> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 000001AB 0000002C 00000062 000486F7 000190FA 00000000

[email protected]> 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,我還是不知道表示什麼?


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2791949/,如需轉載,請註明出處,否則將追究法律責任。