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