[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的地址，形成双向链接。