[20210914]探究mutex的值 4.txt

[20210914]探究mutex的值 4.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

--//前面的測試有點亂,重新測試看看.

1.環境:
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.

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.建立測試指令碼:

$ cat mutex_gets.sh
#! /bin/bash
# test mutex gets change
#
# argv1=mutex_address
# argv2=sleep secoonds
#

sqlplus -s -l / as sysdba <<EOF|ts.awk &
oradebug setmypid
$(seq 6 | xargs -IQ echo -e "oradebug peek 0x${1} 24\nhost sleep $2\n")
quit
EOF

sqlplus -s -l scott/book <<EOF | ts.awk &
set head off feedback off
$(seq 5 | xargs -IQ echo -e "select * from dept where deptno=20;\nhost sleep $2\n")
quit
EOF

#strace -Ttt -f -p $! -o /tmp/mutex.txt &
#strace -Ttt -f -p $! -o /tmp/mutex.txt -e select,sched_yield &

$ . mutex_gets.sh 80528F40 1 | grep 080528F40
[2021-09-14 09:36:34] [080528F40, 080528F58) = 00000000 00000000 00000045 00000000 000190FA 00000000
[2021-09-14 09:36:35] [080528F40, 080528F58) = 00000000 00000000 00000046 00000000 000190FA 00000000
[2021-09-14 09:36:36] [080528F40, 080528F58) = 00000000 00000000 00000047 00000000 000190FA 00000000
[2021-09-14 09:36:37] [080528F40, 080528F58) = 00000000 00000000 00000048 00000000 000190FA 00000000
[2021-09-14 09:36:38] [080528F40, 080528F58) = 00000000 00000000 00000049 00000000 000190FA 00000000
[2021-09-14 09:36:39] [080528F40, 080528F58) = 00000000 00000000 00000049 00000000 000190FA 00000000

--//因為我設定session_cached_cursors=0,每次都是軟解析,各樣每次間隔1秒gets增加1,可以看出peek 24位元組中8-11位元組表示gets的數量.

SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000049 00000000 000190FA 00000000
--//0x49 = 73

SYS@book> oradebug dump library_cache 8;
Statement processed.

SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 0000004A 00000000 000190FA 00000000
--//0x4a = 74

--//檢查轉儲:
Bucket: #=102650 Mutex=0x80528f40(0, 74, 0, 6)
--//74 = 0x4a
  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=70 LoadCount=2 ActiveLocks=1 TotalLockCount=70 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=69 HandleInUse=69 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x7d3c96b0(0, 2, 0, 0) Mutex=0x7d3c9740(86, 301, 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

--//你可以發現轉儲library cache ,對應的gets也增加1.
--//本來想繼續描述sleep的情況的,發現一些我無法理解的情況,先放一放.