[20220304]測試library cache mutex遇到的疑問.txt

[20220304]測試library cache mutex遇到的疑問.txt

--//昨天測試library cache mutex如何定位時遇到的疑問，就是每次執行sql語句後gets數量不是開始不是按照1增加，而是2.
--//今天測試看看,順便加強記憶與理解。

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> @ hide _kghdsidx_count
NAME                               DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
_kghdsidx_count                    max kghdsidx count                                                 TRUE          1             1            FALSE FALSE

SYS@book> @ hide _kgl_bucket_count
NAME                               DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
_kgl_bucket_count                  Library cache hash table bucket count (2^_kgl_bucket_count * 256)  TRUE          9             9            FALSE FALSE

--//還原為預設引數，並且我刪除以前測試時設定一些引數，比如db_2k_cache_size。

2.測試：
--//再使用前面的查詢，發現該地址表不再原來的位置。我重新定位看看。
SYS@book> oradebug setmypid
Statement processed.

SYS@book> oradebug dump library_cache 4
Statement processed.

--//檢查跟蹤檔案發現如下：
*** 2022-03-04 09:13:55.609
Processing Oradebug command 'dump library_cache 4'
Library Cache Dump
SGA:0x80782738 Flags=ac3
DebugContext:  DebugNameSpace=0 DebugType=0 DebugLevel=0x0
Bucket: #=96 Mutex=0x807846e8(0, 4, 0, 6)

SYS@book> @ tpt/calc 0x807846e8 - 16
                                DEC                  HEX
----------------------------------- --------------------
                  2155366104.000000             807846D8
--//該library cache bueket的地址在 x807846D8.
--//library cache bueket 佔 16位元組，mutex結構體佔24位元組，加起來佔40.
--//96*40 = 3840 ，減去3840就是library cache bueket = 0 地址。
SYS@book> @ tpt/calc x807846D8 - 3840
                                DEC                  HEX
----------------------------------- --------------------
                  2155362264.000000             807837D8
--//library cache bueket = 0 地址，根據前面的測試該地址前面就是該地址表。
--//該地址表每個佔8位元組，共2^9=512條記錄。
--//512*8 = 4096
SCOTT@book> @  tpt/calc x807837D8  - 4096
                                DEC                  HEX
----------------------------------- --------------------
                  2155358168.000000             807827D8

SYS@book> select * from (SELECT rownum-1 rn , x$ksmmem.* FROM X$KSMMEM WHERE addr between hextoraw('00000000807827D8') and hextoraw('00000000807837D0')) where rn=246;
        RN ADDR                   INDX    INST_ID KSMMMVAL
---------- ---------------- ---------- ---------- ----------------
       246 0000000080782F88   68093425          1 0000000080115020

--//我前面的查詢結果如下：
SYS@book> Select sysdate from dual ;
SYSDATE
-------------------
2022-03-03 09:12:01

SYS@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
 381482536 dvk83p4bbtxj8            0      63016      1388734953  16bcf628  2022-03-03 09:12:01    16777216

--//HASH_VALUE=381482536,KGL_BUCKET=63016.
--//計算方法 381482536%(2^9*256) = 63016。 bucket值等於63016。

SYS@book> select trunc (63016 /256 ), mod(63016 ,256)  from dual ;
TRUNC(63016/256) MOD(63016,256)
---------------- --------------
             246             40

--//MOD(63016,256)=40, 40*40 = 1600

SYS@book> @ tpt/calc x0000000080115020 + 1600
                                DEC                  HEX
----------------------------------- --------------------
                  2148619872.000000             80115660

--//library cache bucket=246的地址在x80115660.

SYS@book> oradebug peek 0x80115660 40
[080115660, 080115688) = 80115660 00000000 80115660 00000000 00000000 00000000 00000002 00000000 0000F628 00000000

--//當前沒有物件，因為裡面儲存的地址指向自己0x80115660.

$ echo  'Select sysdate from dual ;'|sqlplus   -s -l / as sysdba
SYSDATE
-------------------
2022-03-04 09:27:36

--//每次執行如上語句,peek1次。
SYS@book> oradebug peek 0x80115660 40
[080115660, 080115688) = 80115660 00000000 80115660 00000000 00000000 00000000 00000002 00000000 0000F628 00000000
SYS@book> oradebug peek 0x80115660 40
[080115660, 080115688) = 7BF9D5A8 00000000 7BF9D5A8 00000000 00000000 00000000 00000003 00000000 0000F628 00000000
SYS@book> oradebug peek 0x80115660 40
[080115660, 080115688) = 7BF9D5A8 00000000 7BF9D5A8 00000000 00000000 00000000 00000004 00000000 0000F628 00000000
SYS@book> oradebug peek 0x80115660 40
[080115660, 080115688) = 7BF9D5A8 00000000 7BF9D5A8 00000000 00000000 00000000 00000005 00000000 0000F628 00000000
SYS@book> oradebug peek 0x80115660 40
[080115660, 080115688) = 7BF9D5A8 00000000 7BF9D5A8 00000000 00000000 00000000 00000006 00000000 0000F628 00000000

--//預設每次執行gets計數增加1次，看來前面的測試也許是其它因素的干擾。

SYS@book> @ sharepool/shp4 dvk83p4bbtxj8 0
TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address  000000007D774B70 000000007BF9D5A8 Select sysdate from dual                          0          0          0 000000007BF427A0 000000007BDC28D0       4528       8088       3082     15698      15698  381482536 dvk83p4bbtxj8          0
parent handle address 000000007BF9D5A8 000000007BF9D5A8 Select sysdate from dual                          0          0          0 000000007C1D4A40 00                     4720          0          0      4720       4720  381482536 dvk83p4bbtxj8      65535

--//你可以發現該bucket地址記錄了父遊標控制程式碼的地址x000000007BF9D5A8。
SYS@book> oradebug peek 0x000000007BF9D5A8 40
[07BF9D5A8, 07BF9D5D0) = 80115660 00000000 80115660 00000000 7BDC21F8 00000000 7BF9D700 00000000 00000000 10012841

--//而父遊標裡面有記錄了該library cahce bucket的地址，形成雙向連結。