[20210520]11g shared pool latch與library cache mutex的簡單探究3.txt

[20210520]11g shared pool latch與library cache mutex的簡單探究3.txt

--//前面測試library cache 的轉儲,發現11g不再使用latch library cache,而是使用library cache mutex代替.
--//測試11g下這種使用情況.

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

SELECT addr
        ,latch#
        ,child#
        ,level#
        ,name
        ,gets
        ,sleeps
        ,immediate_gets
        ,immediate_misses
        ,spin_gets
    FROM V$LATCH_CHILDREN
   WHERE name LIKE 'shared pool'
ORDER BY addr;

ADDR                 LATCH#     CHILD#     LEVEL# NAME              GETS     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES  SPIN_GETS
---------------- ---------- ---------- ---------- ----------- ---------- ---------- -------------- ---------------- ----------
000000006010D860        336          1          7 shared pool    6780053       1054              0                0      12871
000000006010D900        336          2          7 shared pool         11          0              0                0          0
000000006010D9A0        336          3          7 shared pool         11          0              0                0          0
000000006010DA40        336          4          7 shared pool         11          0              0                0          0
000000006010DAE0        336          5          7 shared pool         11          0              0                0          0
000000006010DB80        336          6          7 shared pool         11          0              0                0          0
000000006010DC20        336          7          7 shared pool         11          0              0                0          0
7 rows selected.
--//注意僅僅一個shared pool latch的gets很大,其它實際上啟動後不會使用.
--//shared pool latch的數量與共享池記憶體大小,cpu數量有關.實際上取 共享池記憶體大小/512M(對於11G是這樣,早期版本有一些除256M,128M)
--//以及cpu數量/4的最小值.我設定sga才484M.僅僅1個shared pool latch.

2.測試：
--//session 1:
SCOTT@book> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
--//執行多次。

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
  95129850 80baj2c2ur47u            0   5ab90fa

SYS@book> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_13734_0001.trc

SYS@book> oradebug setmypid
Statement processed.

SYS@book> oradebug dump library_cache 26;
Statement processed.
--//這樣可以獲得堆大小資訊。 2+8+16 = 26
--//alter session set events 'immediate trace name library_cache level N';
. Level=1，轉儲庫快取統計資訊。
. Level=2，轉儲雜湊表概要。
. Level=4，轉儲庫快取物件，只包含基本資訊。
. Level=8，轉儲庫快取物件，包含詳細資訊(包括 child references、pin waiters等)。
. Level=16，增加堆大小資訊。
. Level=32，增加堆資訊。

SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address  000000007D949148 000000007C351C40 select * from dept where deptno=20                1          0          0 000000007C8CF828 000000007DCF55E0       4528      12144       3067     19739      19739   95129850 80baj2c2ur47u          0
parent handle address 000000007C351C40 000000007C351C40 select * from dept where deptno=20                1          0          0 000000007BE1E1F8 00                     4720          0          0      4720       4720   95129850 80baj2c2ur47u      65535

Bucket: #=102650 Mutex=0x80528f40(0, 22, 0, 6)
  LibraryHandle:  Address=0x7c351c40 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=6 LoadCount=2 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x7c351cf0(0, 1, 0, 0) Mutex=0x7c351d80(1, 30, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841]
    WaitersLists:
      Lock=0x7c351cd0[0x7c351cd0,0x7c351cd0]
      Pin=0x7c351cb0[0x7c351cb0,0x7c351cb0]
      LoadLock=0x7c351d28[0x7c351d28,0x7c351d28]
    Timestamp:  Current=05-13-2021 10:04:12
    HandleReference:  Address=0x7c351e10 Handle=(nil) Flags=[00]
    ReferenceList:
      Reference:  Address=0x7d9f70c0 Handle=0x7cf101a0 Flags=ROD[21]
    LibraryObject:  Address=0x7dcf4f20 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^5ab90fa pins=0 Change=NONE
          Heap=0x7be1e1f8 Pointer=0x7dcf4fc0 Extent=0x7dcf4ea0 Flags=I/-/P/A/-/-
          FreedLocation=0 Alloc=2.437500 Size=3.976562 LoadTime=12215214510
      ChildTable:  size='16'
        Child:  id='0' Table=0x7dcf5dd0 Reference=0x7dcf5810 Handle=0x7d949148
      Children:
        Child:  childNum='0'
          LibraryHandle:  Address=0x7d949148 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
            Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
            Statistics:  InvalidationCount=0 ExecutionCount=6 LoadCount=1 ActiveLocks=1 TotalLockCount=3 TotalPinCount=7
            Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
            Concurrency:  DependencyMutex=0x7d9491f8(0, 0, 0, 0) Mutex=0x7c351d80(1, 30, 0, 6)
            Flags=RON/PIN/PN0/EXP/CHD/[10012111]
            WaitersLists:
              Lock=0x7d9491d8[0x7d9491d8,0x7d9491d8]
              Pin=0x7d9491b8[0x7d9491b8,0x7d9491b8]
              LoadLock=0x7d949230[0x7d949230,0x7d949230]
            ReferenceList:
              Reference:  Address=0x7dcf5810 Handle=0x7c351c40 Flags=CHL[02]
            LibraryObject:  Address=0x7c9b3210 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
              Dependencies:  count='2' size='16' table='0x7c9b4048'
                Dependency:  num='0'
                  Reference=0x7c9b37c8 Position=0 Flags=DEP[0001]
                  Handle=0x7c606dc0 Type=NONE(255) Parent=SCOTT
                Dependency:  num='1'
                  Reference=0x7c9b3868 Position=14 Flags=DEP[0001]
                  Handle=0x7d46f3a0 Type=TABLE(02) Parent=SCOTT.DEPT
              ReadOnlyDependencies:  count='1' size='16'
                ReadDependency:  num='0' Table=0x7c9b40e0 Reference=0x7c9b36c8 Handle=0x7cf101a0 Flags=DEP/ROD/KPP[61]
              Authorizations:  count='1' size='16' entryeize='16'
              Accesses:  count='1' size='16'
                Dependency:  num='1' Type=0009
              Translations:  count='1' size='16'
                Translation:  num='0' Original=0x7d46f3a0 Final=0x7d46f3a0
              DataBlocks:
                Block:  #='0' name=KGLH0^5ab90fa pins=0 Change=NONE
                  Heap=0x7c8cf828 Pointer=0x7c9b32b0 Extent=0x7c9b3190 Flags=I/-/P/A/-/-
                  FreedLocation=0 Alloc=2.593750 Size=3.976562 LoadTime=12215214510
                Block:  #='6' name=SQLA^5ab90fa pins=0 Change=NONE
                  Heap=0x7dcf55e0 Pointer=0x7dbe4500 Extent=0x7dbe38c0 Flags=I/-/-/A/-/E
                  FreedLocation=0 Alloc=8.445312 Size=11.859375 LoadTime=0
            NamespaceDump:
              Child Cursor:  Heap0=0x7c9b32b0 Heap6=0x7dbe4500 Heap0 Load Time=05-13-2021 10:04:12 Heap6 Load Time=05-13-2021 10:04:12
    NamespaceDump:
      Parent Cursor:  sql_id=80baj2c2ur47u parent=0x7dcf4fc0 maxchild=1 plk=y ppn=n

--//hash=5ab90fa = 95129850
--//95129850 % 131072 = 102650
--//知道這條語句一定使用Bucket: #=102650 Mutex=0x80528f40.
--//BUCKET肯定不會變，我關閉重啟資料庫發現Mutex對應的地址也沒有變，估計這個啟動時就設定好,我前面測試這類mutex結構體佔用
--//40位元組。

SYS@book> oradebug peek 0x80528f40 40
[080528F40, 080528F68) = 00000000 00000000 00000018 00000000 000190FA 00000000 80528F58 00000000 80528F58 00000000
--//看不出什麼資訊， 0x000190FA = 102650,對應Bucket.
--//退出回話,重新整理共享池:
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;

SYS@book> oradebug peek 0x80528f40 40
[080528F40, 080528F68) = 00000000 00000000 0000001B 00000000 000190FA 00000000 80528F58 00000000 80528F58 00000000

3.測試:
--//再次登入，測試前執行命令多次.主要是避免遞迴sql語句以及測試時遇到硬解析時掛起.
--//desc dept;
--//Select * from dept where deptno=20;
--//@ wait
--//前面的測試使用不小心很容易掛起，我決定換一種方式使用gdb跟蹤方式看看。

--//session 1:
SCOTT@book> @ s
SCOTT@book(15,7)> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        15          7 7304                     DEDICATED 7305        25          4 alter system kill session '15,7' immediate;

--//session 2:
--//建立gdb指令碼。
$ cat latch_mutex.gdb
break kslgetl if $rdi==0X6010D860
  commands
    silent
    printf "kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
    c
  end

break kglGetMutex if $rsi==0X80528f40
  commands
    silent
    printf "kglGetMutex %x, %x, %x, %d\n", $rdi, $rsi, $rdx, $rcx
    c
  end

$ gdb -p 7305 -x latch_mutex.gdb
...
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnque11.so
0x000000379a00da70 in __read_nocancel () from /lib64/libpthread.so.0
Breakpoint 1 at 0x93f97a8
Breakpoint 2 at 0x98451a0

--//session 1,第1次執行：
SCOTT@book(15,7)> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

--//session 2:
(gdb) c
Continuing.
kglGetMutex c0cc9e0, 80528f40, 7c779468, 1
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4041
kslgetl 6010d860, 1, 0, 3983
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 2109087424, 3991
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 2112437952, 3991
kslgetl 6010d860, 1, 2108142296, 3991
kslgetl 6010d860, 1, 2108141736, 3991
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4041
kslgetl 6010d860, 1, 2108970984, 3991
kslgetl 6010d860, 1, 2088454424, 3991
kslgetl 6010d860, 1, 2112262184, 3991
kslgetl 6010d860, 1, 2112437408, 3991
kslgetl 6010d860, 1, 2086935112, 3991
--//先出現mutex。

--//session 1,第2次執行：
SCOTT@book(15,7)> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

--//session 2:
kslgetl 6010d860, 1, 2086943304, 3991
kglGetMutex c0cc9e0, 80528f40, 7c779468, 1
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 2086935112, 3991

--//session 1,第3次執行：
SCOTT@book(15,7)> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

SCOTT@book(15,7)> select * from dept where deptno=20;

--//session 2:
kslgetl 6010d860, 1, 2086943304, 3991
kglGetMutex c0cc9e0, 80528f40, 7c779468, 1

--//session 1,第4次執行：
SCOTT@book(15,7)> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
--//session 2:
--//沒有任何輸出。

總結：
--//很明顯前面3次出現都需要遇到這兩個等待事件，
--//第1次library cache: mutex X，再持有latch: shared pool
--//第2，3次先持有latch: shared pool，然後才是library cache: mutex X.
--//第4次沒有需要latch: shared pool，library cache: mutex X。