[20220302]oracle如何定位使用library cache mutex 2.txt

[20220302]oracle如何定位使用library cache mutex 2.txt 2020-09-19 07:09:32 320人浏览才女

摘要

[20220302]oracle如何定位使用library cache mutex 2.txt--//这个问题实际上困扰我很久，我开始以为library cache bucket在1个chunk内，只要知道基地址+40*buckect值

[20220302]oracle如何定位使用library cache mutex 2.txt

--//这个问题实际上困扰我很久，我开始以为library cache bucket在1个chunk内，只要知道基地址+40*buckect值获得偏移,定位
--//library cache bucket 的地址。
--//注：11g 下每个library cache bucket占用16字节，后面跟着mutex，mutex结构占用24字节(注:有朋友讲占用16字节,我想与转储看
--//到mutex仅仅有4个值有关,4*4=16，我个人还是按照24字节来算),这样整个结构占用40字节。

--//可以参考我前面的测试 [20210524]分析library cache转储 3.txt

--//而实际上的情况被分成好几个chunk，显然无法简单的通过基地址+40*buckect值计算获得偏移，那么oracle计算sql语句的
--//hash_value，通过hash_value值计算出bucket值, 等于hash_value % (2^_kgl_bucket_count * 256) ,知道bucket数值，
--//如何通过bucket数值来定位library cache muext的地址呢？自己尝试做这方面的探究。

--//我前面的测试修改"_kgl_bucket_count"=6,看看缺省值等于9的情况。

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 reset "_kgl_bucket_count";
System altered.

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
--//还原为缺省2^9*256 = 131072.

--//首先说明一下11g使用library cache mutex代替10g library cache latch,定位library cache mutex就很容易定位library cache
--//latch地址，仅仅偏移16字节。而且muext结构体里面记录了library cache latch值。
--//参考：http://blog.itpub.net/267265/viewspace-2792123/ =>[20210915]探究mutex的值 6.txt
--//我自己还有一个疑问就是这些chunk是使用时分配，还是启动数据库时事先分配好的，因为转储仅仅看到存在对象的bucket。
--//注：我的测试发现启动数据库时事先分配好的，为什么不使用连续的chunk，并且使用这么多chunk，我自己不是很清楚。简单的重启
--//就可以验证这个问题。

2.建立测试环境：
create table t as select rownum id ,"test" pad from dual connect by level<=1e6;
alter table t add constraint pk_t primary key (id);
exec dbms_stats.gather_table_stats(user, "t", method_opt=>"for all columns size 1");
alter system flush shared_pool;

$ cat tt1.txt
declare
v_pad varchar2(10);
begin
for i in 1 .. 1e6 loop
execute immediate "select pad from t where id = " || i into v_pad;
end loop;
end;
/

@ tt1.txt

--//sed "1,$s/select/Select/" tt1.txt | sqlplus -s -l scott/book >/dev/null &
--//sed "1,$s/select/SElect/" tt1.txt | sqlplus -s -l scott/book >/dev/null &
--//sed "1,$s/select/SELect/" tt1.txt | sqlplus -s -l scott/book >/dev/null &
--//sed "1,$s/select/SELEct/" tt1.txt | sqlplus -s -l scott/book >/dev/null &
--//sed "1,$s/select/SELECt/" tt1.txt | sqlplus -s -l scott/book >/dev/null &
--//sed "1,$s/select/SELECT/" tt1.txt | sqlplus -s -l scott/book >/dev/null &
--//确实时间有点长，可以把循环修改1e5.

3.转储library_cache:
--//有了前面的探究，这次就简单许多。

SYS@book> oradebug setmypid
Statement processed.

SYS@book> oradebug dump library_cache 8
Statement processed.

SYS@book> @ ttt
tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_13832.trc

$ grep "Bucket:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_13832.trc | sed "s/^Bucket: #=//;s/Mutex=//;s/(.*)//" >| c1.txt
--//将Bucket以及mutex地址写入文件c1.txt

$ awk "NR==1{a=$1;b=strtonum($2) } NR>1{ print (strtonum($2)-b)/($1-a);a=$1;b=strtonum($2)}" c1.txt | sort | uniq -c | sort -nr
   2588 40
      1 -82364.5
      1 733996
      1 -385641
      1 -354348
      1 -350762
      1 -305230
      1 245343
      1 227319
      1 -175809
--//可以发现40出现次数最多，说明一个chunk内每个mutex地址的间隔是40个字节，参考[20210524]分析library cache转储 3.txt 的测试。
--//查询地址跳跃的情况，也就是间隔不是40的情况。
$ awk "NR==1{a=$1;b=strtonum($2);c=$2 } NR>1{ print a,c,$1,$2,(strtonum($2)-b)/($1-a);a=$1;b=strtonum($2);c=$2}" c1.txt | grep -v "40$"
12499 0x863fd8e0 12579 0x89bfd6c8 733996
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
12699 0x89bfe988 12802 0x893e76b0 -82364.5
13309 0x893ec5e8 13368 0x87ffc3D8 -354348
13560 0x87ffe1d8 13584 0x877f6de8 -350762
14333 0x877fe2f0 14344 0x873f2870 -385641
15560 0x873fe670 15636 0x86740530 -175809
35070 0x867fe1c0 35083 0x86b08c98 245343
60158 0x86bfda90 60176 0x86fe49e8 227319
62975 0x86ffff40 63043 0x85c34aa8 -305230

$ head -1 c1.txt
29 0x86383c70
--//跟踪文件对应记录内容如下：
--//Bucket: #=29 Mutex=0x86383c70(0, 18, 0, 6)
--//29*40 = 1160，该bucket的地址 0x86383c70 - 16 = 86383c60

SYS@book> @ tpt/calc 0x86383c60 - 1160
                                DEC                  HEX
----------------------------------- --------------------
                  2251831256.000000             863837D8
--//bucket 0 的地址 0x863837D8.

SYS@book> @ fcha 0x863fd8e0
Find in which heap (UGA, PGA or Shared Pool) the memory address 0x863fd8e0 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000086034000          1          1 permanent memor     3973080 perm              0 00

SYS@book> @ tpt/calc x0000000086034000 + 3973080
                                DEC                  HEX
----------------------------------- --------------------
                  2252333016.000000             863FDFD8
--//第一个chunk的范围是 0x86034000 - 0x863FDFD8

--//查询在第一个chunk范围内ksmmmval = hextoraw("00000000863837D8")的情况:
SELECT rownum
     , x$ksmmem.*
FROM X$KSMMEM
WHERE addr between hextoraw("0000000086034000") and hextoraw("00000000863FDFD8")
   AND ksmmmval = hextoraw("00000000863837D8");

    ROWNUM ADDR                   INDX    INST_ID KSMMMVAL
---------- ---------------- ---------- ---------- ----------------
         1 00000000863827D8   80151803          1 00000000863837D8
         2 00000000863837D8   80152315          1 00000000863837D8 -> bucket 0 地址
         3 00000000863837E0   80152316          1 00000000863837D8 -> bucket 0 地址

--//这样可以推断数组的开始地址是x863827D8.
--//2^9 = 512, 512*8 = 4096

SYS@book> @ tpt/calc x863827D8 + 4096
                                DEC                  HEX
----------------------------------- --------------------
                  2251831256.000000             863837D8

--//正好结尾对应0x863837D8，也就是bucket 0 的地址。

--//查询hextoraw("00000000863827D8") and hextoraw("00000000863837D0") 范围的信息:
SELECT rownum
     , x$ksmmem.*
FROM X$KSMMEM
WHERE addr between hextoraw("00000000863827D8") and hextoraw("00000000863837D0");

ROWNUM ADDR                 INDX INST_ID KSMMMVAL
------ ---------------- -------- ------- ----------------
     1 00000000863827D8 80151803       1 00000000863837D8
     2 00000000863827E0 80151804       1 0000000086385FD8
     3 00000000863827E8 80151805       1 00000000863887D8
     4 00000000863827F0 80151806       1 000000008638AFD8
     5 00000000863827F8 80151807       1 000000008638D7D8
     6 0000000086382800 80151808       1 000000008638FFD8
     7 0000000086382808 80151809       1 00000000863927D8
     8 0000000086382810 80151810       1 0000000086394FD8
     9 0000000086382818 80151811       1 00000000863977D8
    10 0000000086382820 80151812       1 0000000086399FD8
    11 0000000086382828 80151813       1 000000008639C7D8
    12 0000000086382830 80151814       1 000000008639EFD8
    13 0000000086382838 80151815       1 00000000863A17D8
    14 0000000086382840 80151816       1 00000000863A3FD8
    15 0000000086382848 80151817       1 00000000863A67D8
    16 0000000086382850 80151818       1 00000000863A8FD8
    17 0000000086382858 80151819       1 00000000863AB7D8
    18 0000000086382860 80151820       1 00000000863ADFD8
    19 0000000086382868 80151821       1 00000000863B07D8
    20 0000000086382870 80151822       1 00000000863B2FD8
    21 0000000086382878 80151823       1 00000000863B57D8
    22 0000000086382880 80151824       1 00000000863B7FD8
    23 0000000086382888 80151825       1 00000000863BA7D8
    24 0000000086382890 80151826       1 00000000863BCFD8
    25 0000000086382898 80151827       1 00000000863BF7D8
    26 00000000863828A0 80151828       1 00000000863C1FD8
    27 00000000863828A8 80151829       1 00000000863C47D8
    28 00000000863828B0 80151830       1 00000000863C6FD8
    29 00000000863828B8 80151831       1 00000000863C97D8
    30 00000000863828C0 80151832       1 00000000863CBFD8
    31 00000000863828C8 80151833       1 00000000863CE7D8
    32 00000000863828D0 80151834       1 00000000863D0FD8
    33 00000000863828D8 80151835       1 00000000863D37D8
    34 00000000863828E0 80151836       1 00000000863D5FD8
    35 00000000863828E8 80151837       1 00000000863D87D8
    36 00000000863828F0 80151838       1 00000000863DAFD8
    37 00000000863828F8 80151839       1 00000000863DD7D8
    38 0000000086382900 80151840       1 00000000863DFFD8
    39 0000000086382908 80151841       1 00000000863E27D8
    40 0000000086382910 80151842       1 00000000863E4FD8
    41 0000000086382918 80151843       1 00000000863E77D8
    42 0000000086382920 80151844       1 00000000863E9FD8
    43 0000000086382928 80151845       1 00000000863EC7D8
    44 0000000086382930 80151846       1 00000000863EEFD8
    45 0000000086382938 80151847       1 00000000863F17D8
    46 0000000086382940 80151848       1 00000000863F3FD8
    47 0000000086382948 80151849       1 00000000863F67D8
    48 0000000086382950 80151850       1 00000000863F8FD8
    49 0000000086382958 80151851       1 00000000863FB7D8
    50 0000000086382960 80151852       1 0000000089BFD140
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    51 0000000086382968 80151853       1 00000000893E7650
    52 0000000086382970 80151854       1 00000000893E9E50
    53 0000000086382978 80151855       1 0000000087FFBB08
    54 0000000086382980 80151856       1 00000000877F6B58
    55 0000000086382988 80151857       1 00000000877F9358
    56 0000000086382990 80151858       1 00000000877FBB58
    57 0000000086382998 80151859       1 00000000873F2720
    58 00000000863829A0 80151860       1 00000000873F4F20
    59 00000000863829A8 80151861       1 00000000873F7720
    60 00000000863829B0 80151862       1 00000000873F9F20
    61 00000000863829B8 80151863       1 00000000873FC720
    62 00000000863829C0 80151864       1 0000000086740200
    63 00000000863829C8 80151865       1 0000000086742A00
    64 00000000863829D0 80151866       1 0000000086745200
    65 00000000863829D8 80151867       1 0000000086747A00
    66 00000000863829E0 80151868       1 000000008674A200
    67 00000000863829E8 80151869       1 000000008674CA00
    68 00000000863829F0 80151870       1 000000008674F200
    69 00000000863829F8 80151871       1 0000000086751A00
    70 0000000086382A00 80151872       1 0000000086754200
    71 0000000086382A08 80151873       1 0000000086756A00
    72 0000000086382A10 80151874       1 0000000086759200
    73 0000000086382A18 80151875       1 000000008675BA00
    74 0000000086382A20 80151876       1 000000008675E200
    75 0000000086382A28 80151877       1 0000000086760A00
    76 0000000086382A30 80151878       1 0000000086763200
    77 0000000086382A38 80151879       1 0000000086765A00
    78 0000000086382A40 80151880       1 0000000086768200
    79 0000000086382A48 80151881       1 000000008676AA00
    80 0000000086382A50 80151882       1 000000008676D200
    81 0000000086382A58 80151883       1 000000008676FA00
    82 0000000086382A60 80151884       1 0000000086772200
    83 0000000086382A68 80151885       1 0000000086774A00
    84 0000000086382A70 80151886       1 0000000086777200
    85 0000000086382A78 80151887       1 0000000086779A00
    86 0000000086382A80 80151888       1 000000008677C200
    87 0000000086382A88 80151889       1 000000008677EA00
    88 0000000086382A90 80151890       1 0000000086781200
    89 0000000086382A98 80151891       1 0000000086783A00
    90 0000000086382AA0 80151892       1 0000000086786200
    91 0000000086382AA8 80151893       1 0000000086788A00
    92 0000000086382AB0 80151894       1 000000008678B200
    93 0000000086382AB8 80151895       1 000000008678DA00
    94 0000000086382AC0 80151896       1 0000000086790200
    95 0000000086382AC8 80151897       1 0000000086792A00
    96 0000000086382AD0 80151898       1 0000000086795200
    97 0000000086382AD8 80151899       1 0000000086797A00
    98 0000000086382AE0 80151900       1 000000008679A200
    99 0000000086382AE8 80151901       1 000000008679CA00
   100 0000000086382AF0 80151902       1 000000008679F200
   101 0000000086382AF8 80151903       1 00000000867A1A00
   102 0000000086382B00 80151904       1 00000000867A4200
   103 0000000086382B08 80151905       1 00000000867A6A00
   104 0000000086382B10 80151906       1 00000000867A9200
   105 0000000086382B18 80151907       1 00000000867ABA00
   106 0000000086382B20 80151908       1 00000000867AE200
   107 0000000086382B28 80151909       1 00000000867B0A00
   108 0000000086382B30 80151910       1 00000000867B3200
   109 0000000086382B38 80151911       1 00000000867B5A00
   110 0000000086382B40 80151912       1 00000000867B8200
   111 0000000086382B48 80151913       1 00000000867BAA00
   112 0000000086382B50 80151914       1 00000000867BD200
   113 0000000086382B58 80151915       1 00000000867BFA00
   114 0000000086382B60 80151916       1 00000000867C2200
   115 0000000086382B68 80151917       1 00000000867C4A00
   116 0000000086382B70 80151918       1 00000000867C7200
   117 0000000086382B78 80151919       1 00000000867C9A00
   118 0000000086382B80 80151920       1 00000000867CC200
   119 0000000086382B88 80151921       1 00000000867CEA00
   120 0000000086382B90 80151922       1 00000000867D1200
   121 0000000086382B98 80151923       1 00000000867D3A00
   122 0000000086382BA0 80151924       1 00000000867D6200
   123 0000000086382BA8 80151925       1 00000000867D8A00
   124 0000000086382BB0 80151926       1 00000000867DB200
   125 0000000086382BB8 80151927       1 00000000867DDA00
   126 0000000086382BC0 80151928       1 00000000867E0200
   127 0000000086382BC8 80151929       1 00000000867E2A00
   128 0000000086382BD0 80151930       1 00000000867E5200
   129 0000000086382BD8 80151931       1 00000000867E7A00
   130 0000000086382BE0 80151932       1 00000000867EA200
   131 0000000086382BE8 80151933       1 00000000867ECA00
   132 0000000086382BF0 80151934       1 00000000867EF200
   133 0000000086382BF8 80151935       1 00000000867F1A00
   134 0000000086382C00 80151936       1 00000000867F4200
   135 0000000086382C08 80151937       1 00000000867F6A00
   136 0000000086382C10 80151938       1 00000000867F9200
   137 0000000086382C18 80151939       1 00000000867FBA00
   138 0000000086382C20 80151940       1 0000000086B08AD0
   139 0000000086382C28 80151941       1 0000000086B0B2D0
   140 0000000086382C30 80151942       1 0000000086B0DAD0
   141 0000000086382C38 80151943       1 0000000086B102D0
   142 0000000086382C40 80151944       1 0000000086B12AD0
   143 0000000086382C48 80151945       1 0000000086B152D0
   144 0000000086382C50 80151946       1 0000000086B17AD0
   145 0000000086382C58 80151947       1 0000000086B1A2D0
   146 0000000086382C60 80151948       1 0000000086B1CAD0
   147 0000000086382C68 80151949       1 0000000086B1F2D0
   148 0000000086382C70 80151950       1 0000000086B21AD0
   149 0000000086382C78 80151951       1 0000000086B242D0
   150 0000000086382C80 80151952       1 0000000086B26AD0
   151 0000000086382C88 80151953       1 0000000086B292D0
   152 0000000086382C90 80151954       1 0000000086B2BAD0
   153 0000000086382C98 80151955       1 0000000086B2E2D0
   154 0000000086382CA0 80151956       1 0000000086B30AD0
   155 0000000086382CA8 80151957       1 0000000086B332D0
   156 0000000086382CB0 80151958       1 0000000086B35AD0
   157 0000000086382CB8 80151959       1 0000000086B382D0
   158 0000000086382CC0 80151960       1 0000000086B3AAD0
   159 0000000086382CC8 80151961       1 0000000086B3D2D0
   160 0000000086382CD0 80151962       1 0000000086B3FAD0
   161 0000000086382CD8 80151963       1 0000000086B422D0
   162 0000000086382CE0 80151964       1 0000000086B44AD0
   163 0000000086382CE8 80151965       1 0000000086B472D0
   164 0000000086382CF0 80151966       1 0000000086B49AD0
   165 0000000086382CF8 80151967       1 0000000086B4C2D0
   166 0000000086382D00 80151968       1 0000000086B4EAD0
   167 0000000086382D08 80151969       1 0000000086B512D0
   168 0000000086382D10 80151970       1 0000000086B53AD0
   169 0000000086382D18 80151971       1 0000000086B562D0
   170 0000000086382D20 80151972       1 0000000086B58AD0
   171 0000000086382D28 80151973       1 0000000086B5B2D0
   172 0000000086382D30 80151974       1 0000000086B5DAD0
   173 0000000086382D38 80151975       1 0000000086B602D0
   174 0000000086382D40 80151976       1 0000000086B62AD0
   175 0000000086382D48 80151977       1 0000000086B652D0
   176 0000000086382D50 80151978       1 0000000086B67AD0
   177 0000000086382D58 80151979       1 0000000086B6A2D0
   178 0000000086382D60 80151980       1 0000000086B6CAD0
   179 0000000086382D68 80151981       1 0000000086B6F2D0
   180 0000000086382D70 80151982       1 0000000086B71AD0
   181 0000000086382D78 80151983       1 0000000086B742D0
   182 0000000086382D80 80151984       1 0000000086B76AD0
   183 0000000086382D88 80151985       1 0000000086B792D0
   184 0000000086382D90 80151986       1 0000000086B7BAD0
   185 0000000086382D98 80151987       1 0000000086B7E2D0
   186 0000000086382DA0 80151988       1 0000000086B80AD0
   187 0000000086382DA8 80151989       1 0000000086B832D0
   188 0000000086382DB0 80151990       1 0000000086B85AD0
   189 0000000086382DB8 80151991       1 0000000086B882D0
   190 0000000086382DC0 80151992       1 0000000086B8AAD0
   191 0000000086382DC8 80151993       1 0000000086B8D2D0
   192 0000000086382DD0 80151994       1 0000000086B8FAD0
   193 0000000086382DD8 80151995       1 0000000086B922D0
   194 0000000086382DE0 80151996       1 0000000086B94AD0
   195 0000000086382DE8 80151997       1 0000000086B972D0
   196 0000000086382DF0 80151998       1 0000000086B99AD0
   197 0000000086382DF8 80151999       1 0000000086B9C2D0
   198 0000000086382E00 80152000       1 0000000086B9EAD0
   199 0000000086382E08 80152001       1 0000000086BA12D0
   200 0000000086382E10 80152002       1 0000000086BA3AD0
   201 0000000086382E18 80152003       1 0000000086BA62D0
   202 0000000086382E20 80152004       1 0000000086BA8AD0
   203 0000000086382E28 80152005       1 0000000086BAB2D0
   204 0000000086382E30 80152006       1 0000000086BADAD0
   205 0000000086382E38 80152007       1 0000000086BB02D0
   206 0000000086382E40 80152008       1 0000000086BB2AD0
   207 0000000086382E48 80152009       1 0000000086BB52D0
   208 0000000086382E50 80152010       1 0000000086BB7AD0
   209 0000000086382E58 80152011       1 0000000086BBA2D0
   210 0000000086382E60 80152012       1 0000000086BBCAD0
   211 0000000086382E68 80152013       1 0000000086BBF2D0
   212 0000000086382E70 80152014       1 0000000086BC1AD0
   213 0000000086382E78 80152015       1 0000000086BC42D0
   214 0000000086382E80 80152016       1 0000000086BC6AD0
   215 0000000086382E88 80152017       1 0000000086BC92D0
   216 0000000086382E90 80152018       1 0000000086BCBAD0
   217 0000000086382E98 80152019       1 0000000086BCE2D0
   218 0000000086382EA0 80152020       1 0000000086BD0AD0
   219 0000000086382EA8 80152021       1 0000000086BD32D0
   220 0000000086382EB0 80152022       1 0000000086BD5AD0
   221 0000000086382EB8 80152023       1 0000000086BD82D0
   222 0000000086382EC0 80152024       1 0000000086BDAAD0
   223 0000000086382EC8 80152025       1 0000000086BDD2D0
   224 0000000086382ED0 80152026       1 0000000086BDFAD0
   225 0000000086382ED8 80152027       1 0000000086BE22D0
   226 0000000086382EE0 80152028       1 0000000086BE4AD0
   227 0000000086382EE8 80152029       1 0000000086BE72D0
   228 0000000086382EF0 80152030       1 0000000086BE9AD0
   229 0000000086382EF8 80152031       1 0000000086BEC2D0
   230 0000000086382F00 80152032       1 0000000086BEEAD0
   231 0000000086382F08 80152033       1 0000000086BF12D0
   232 0000000086382F10 80152034       1 0000000086BF3AD0
   233 0000000086382F18 80152035       1 0000000086BF62D0
   234 0000000086382F20 80152036       1 0000000086BF8AD0
   235 0000000086382F28 80152037       1 0000000086BFB2D0
   236 0000000086382F30 80152038       1 0000000086FE4758
   237 0000000086382F38 80152039       1 0000000086FE6F58
   238 0000000086382F40 80152040       1 0000000086FE9758
   239 0000000086382F48 80152041       1 0000000086FEBF58
   240 0000000086382F50 80152042       1 0000000086FEE758
   241 0000000086382F58 80152043       1 0000000086FF0F58
   242 0000000086382F60 80152044       1 0000000086FF3758
   243 0000000086382F68 80152045       1 0000000086FF5F58
   244 0000000086382F70 80152046       1 0000000086FF8758
   245 0000000086382F78 80152047       1 0000000086FFAF58
   246 0000000086382F80 80152048       1 0000000086FFD758
   247 0000000086382F88 80152049       1 0000000085C34020
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   248 0000000086382F90 80152050       1 0000000085C36820
   249 0000000086382F98 80152051       1 0000000085C39020
   250 0000000086382FA0 80152052       1 0000000085C3B820
   251 0000000086382FA8 80152053       1 0000000085C3E020
   252 0000000086382FB0 80152054       1 0000000085C40820
   253 0000000086382FB8 80152055       1 0000000085C43020
   254 0000000086382FC0 80152056       1 0000000085C45820
   255 0000000086382FC8 80152057       1 0000000085C48020
   256 0000000086382FD0 80152058       1 0000000085C4A820
   257 0000000086382FD8 80152059       1 0000000085C4D020
   258 0000000086382FE0 80152060       1 0000000085C4F820
   259 0000000086382FE8 80152061       1 0000000085C52020
   260 0000000086382FF0 80152062       1 0000000085C54820
   261 0000000086382FF8 80152063       1 0000000085C57020
   262 0000000086383000 80152064       1 0000000085C59820
   263 0000000086383008 80152065       1 0000000085C5C020
   264 0000000086383010 80152066       1 0000000085C5E820
   265 0000000086383018 80152067       1 0000000085C61020
   266 0000000086383020 80152068       1 0000000085C63820
   267 0000000086383028 80152069       1 0000000085C66020
   268 0000000086383030 80152070       1 0000000085C68820
   269 0000000086383038 80152071       1 0000000085C6B020
   270 0000000086383040 80152072       1 0000000085C6D820
   271 0000000086383048 80152073       1 0000000085C70020
   272 0000000086383050 80152074       1 0000000085C72820
   273 0000000086383058 80152075       1 0000000085C75020
   274 0000000086383060 80152076       1 0000000085C77820
   275 0000000086383068 80152077       1 0000000085C7A020
   276 0000000086383070 80152078       1 0000000085C7C820
   277 0000000086383078 80152079       1 0000000085C7F020
   278 0000000086383080 80152080       1 0000000085C81820
   279 0000000086383088 80152081       1 0000000085C84020
   280 0000000086383090 80152082       1 0000000085C86820
   281 0000000086383098 80152083       1 0000000085C89020
   282 00000000863830A0 80152084       1 0000000085C8B820
   283 00000000863830A8 80152085       1 0000000085C8E020
   284 00000000863830B0 80152086       1 0000000085C90820
   285 00000000863830B8 80152087       1 0000000085C93020
   286 00000000863830C0 80152088       1 0000000085C95820
   287 00000000863830C8 80152089       1 0000000085C98020
   288 00000000863830D0 80152090       1 0000000085C9A820
   289 00000000863830D8 80152091       1 0000000085C9D020
   290 00000000863830E0 80152092       1 0000000085C9F820
   291 00000000863830E8 80152093       1 0000000085CA2020
   292 00000000863830F0 80152094       1 0000000085CA4820
   293 00000000863830F8 80152095       1 0000000085CA7020
   294 0000000086383100 80152096       1 0000000085CA9820
   295 0000000086383108 80152097       1 0000000085CAC020
   296 0000000086383110 80152098       1 0000000085CAE820
   297 0000000086383118 80152099       1 0000000085CB1020
   298 0000000086383120 80152100       1 0000000085CB3820
   299 0000000086383128 80152101       1 0000000085CB6020
   300 0000000086383130 80152102       1 0000000085CB8820
   301 0000000086383138 80152103       1 0000000085CBB020
   302 0000000086383140 80152104       1 0000000085CBD820
   303 0000000086383148 80152105       1 0000000085CC0020
   304 0000000086383150 80152106       1 0000000085CC2820
   305 0000000086383158 80152107       1 0000000085CC5020
   306 0000000086383160 80152108       1 0000000085CC7820
   307 0000000086383168 80152109       1 0000000085CCA020
   308 0000000086383170 80152110       1 0000000085CCC820
   309 0000000086383178 80152111       1 0000000085CCF020
   310 0000000086383180 80152112       1 0000000085CD1820
   311 0000000086383188 80152113       1 0000000085CD4020
   312 0000000086383190 80152114       1 0000000085CD6820
   313 0000000086383198 80152115       1 0000000085CD9020
   314 00000000863831A0 80152116       1 0000000085CDB820
   315 00000000863831A8 80152117       1 0000000085CDE020
   316 00000000863831B0 80152118       1 0000000085CE0820
   317 00000000863831B8 80152119       1 0000000085CE3020
   318 00000000863831C0 80152120       1 0000000085CE5820
   319 00000000863831C8 80152121       1 0000000085CE8020
   320 00000000863831D0 80152122       1 0000000085CEA820
   321 00000000863831D8 80152123       1 0000000085CED020
   322 00000000863831E0 80152124       1 0000000085CEF820
   323 00000000863831E8 80152125       1 0000000085CF2020
   324 00000000863831F0 80152126       1 0000000085CF4820
   325 00000000863831F8 80152127       1 0000000085CF7020
   326 0000000086383200 80152128       1 0000000085CF9820
   327 0000000086383208 80152129       1 0000000085CFC020
   328 0000000086383210 80152130       1 0000000085CFE820
   329 0000000086383218 80152131       1 0000000085D01020
   330 0000000086383220 80152132       1 0000000085D03820
   331 0000000086383228 80152133       1 0000000085D06020
   332 0000000086383230 80152134       1 0000000085D08820
   333 0000000086383238 80152135       1 0000000085D0B020
   334 0000000086383240 80152136       1 0000000085D0D820
   335 0000000086383248 80152137       1 0000000085D10020
   336 0000000086383250 80152138       1 0000000085D12820
   337 0000000086383258 80152139       1 0000000085D15020
   338 0000000086383260 80152140       1 0000000085D17820
   339 0000000086383268 80152141       1 0000000085D1A020
   340 0000000086383270 80152142       1 0000000085D1C820
   341 0000000086383278 80152143       1 0000000085D1F020
   342 0000000086383280 80152144       1 0000000085D21820
   343 0000000086383288 80152145       1 0000000085D24020
   344 0000000086383290 80152146       1 0000000085D26820
   345 0000000086383298 80152147       1 0000000085D29020
   346 00000000863832A0 80152148       1 0000000085D2B820
   347 00000000863832A8 80152149       1 0000000085D2E020
   348 00000000863832B0 80152150       1 0000000085D30820
   349 00000000863832B8 80152151       1 0000000085D33020
   350 00000000863832C0 80152152       1 0000000085D35820
   351 00000000863832C8 80152153       1 0000000085D38020
   352 00000000863832D0 80152154       1 0000000085D3A820
   353 00000000863832D8 80152155       1 0000000085D3D020
   354 00000000863832E0 80152156       1 0000000085D3F820
   355 00000000863832E8 80152157       1 0000000085D42020
   356 00000000863832F0 80152158       1 0000000085D44820
   357 00000000863832F8 80152159       1 0000000085D47020
   358 0000000086383300 80152160       1 0000000085D49820
   359 0000000086383308 80152161       1 0000000085D4C020
   360 0000000086383310 80152162       1 0000000085D4E820
   361 0000000086383318 80152163       1 0000000085D51020
   362 0000000086383320 80152164       1 0000000085D53820
   363 0000000086383328 80152165       1 0000000085D56020
   364 0000000086383330 80152166       1 0000000085D58820
   365 0000000086383338 80152167       1 0000000085D5B020
   366 0000000086383340 80152168       1 0000000085D5D820
   367 0000000086383348 80152169       1 0000000085D60020
   368 0000000086383350 80152170       1 0000000085D62820
   369 0000000086383358 80152171       1 0000000085D65020
   370 0000000086383360 80152172       1 0000000085D67820
   371 0000000086383368 80152173       1 0000000085D6A020
   372 0000000086383370 80152174       1 0000000085D6C820
   373 0000000086383378 80152175       1 0000000085D6F020
   374 0000000086383380 80152176       1 0000000085D71820
   375 0000000086383388 80152177       1 0000000085D74020
   376 0000000086383390 80152178       1 0000000085D76820
   377 0000000086383398 80152179       1 0000000085D79020
   378 00000000863833A0 80152180       1 0000000085D7B820
   379 00000000863833A8 80152181       1 0000000085D7E020
   380 00000000863833B0 80152182       1 0000000085D80820
   381 00000000863833B8 80152183       1 0000000085D83020
   382 00000000863833C0 80152184       1 0000000085D85820
   383 00000000863833C8 80152185       1 0000000085D88020
   384 00000000863833D0 80152186       1 0000000085D8A820
   385 00000000863833D8 80152187       1 0000000085D8D020
   386 00000000863833E0 80152188       1 0000000085D8F820
   387 00000000863833E8 80152189       1 0000000085D92020
   388 00000000863833F0 80152190       1 0000000085D94820
   389 00000000863833F8 80152191       1 0000000085D97020
   390 0000000086383400 80152192       1 0000000085D99820
   391 0000000086383408 80152193       1 0000000085D9C020
   392 0000000086383410 80152194       1 0000000085D9E820
   393 0000000086383418 80152195       1 0000000085DA1020
   394 0000000086383420 80152196       1 0000000085DA3820
   395 0000000086383428 80152197       1 0000000085DA6020
   396 0000000086383430 80152198       1 0000000085DA8820
   397 0000000086383438 80152199       1 0000000085DAB020
   398 0000000086383440 80152200       1 0000000085DAD820
   399 0000000086383448 80152201       1 0000000085DB0020
   400 0000000086383450 80152202       1 0000000085DB2820
   401 0000000086383458 80152203       1 0000000085DB5020
   402 0000000086383460 80152204       1 0000000085DB7820
   403 0000000086383468 80152205       1 0000000085DBA020
   404 0000000086383470 80152206       1 0000000085DBC820
   405 0000000086383478 80152207       1 0000000085DBF020
   406 0000000086383480 80152208       1 0000000085DC1820
   407 0000000086383488 80152209       1 0000000085DC4020
   408 0000000086383490 80152210       1 0000000085DC6820
   409 0000000086383498 80152211       1 0000000085DC9020
   410 00000000863834A0 80152212       1 0000000085DCB820
   411 00000000863834A8 80152213       1 0000000085DCE020
   412 00000000863834B0 80152214       1 0000000085DD0820
   413 00000000863834B8 80152215       1 0000000085DD3020
   414 00000000863834C0 80152216       1 0000000085DD5820
   415 00000000863834C8 80152217       1 0000000085DD8020
   416 00000000863834D0 80152218       1 0000000085DDA820
   417 00000000863834D8 80152219       1 0000000085DDD020
   418 00000000863834E0 80152220       1 0000000085DDF820
   419 00000000863834E8 80152221       1 0000000085DE2020
   420 00000000863834F0 80152222       1 0000000085DE4820
   421 00000000863834F8 80152223       1 0000000085DE7020
   422 0000000086383500 80152224       1 0000000085DE9820
   423 0000000086383508 80152225       1 0000000085DEC020
   424 0000000086383510 80152226       1 0000000085DEE820
   425 0000000086383518 80152227       1 0000000085DF1020
   426 0000000086383520 80152228       1 0000000085DF3820
   427 0000000086383528 80152229       1 0000000085DF6020
   428 0000000086383530 80152230       1 0000000085DF8820
   429 0000000086383538 80152231       1 0000000085DFB020
   430 0000000086383540 80152232       1 0000000085DFD820
   431 0000000086383548 80152233       1 0000000085E00020
   432 0000000086383550 80152234       1 0000000085E02820
   433 0000000086383558 80152235       1 0000000085E05020
   434 0000000086383560 80152236       1 0000000085E07820
   435 0000000086383568 80152237       1 0000000085E0A020
   436 0000000086383570 80152238       1 0000000085E0C820
   437 0000000086383578 80152239       1 0000000085E0F020
   438 0000000086383580 80152240       1 0000000085E11820
   439 0000000086383588 80152241       1 0000000085E14020
   440 0000000086383590 80152242       1 0000000085E16820
   441 0000000086383598 80152243       1 0000000085E19020
   442 00000000863835A0 80152244       1 0000000085E1B820
   443 00000000863835A8 80152245       1 0000000085E1E020
   444 00000000863835B0 80152246       1 0000000085E20820
   445 00000000863835B8 80152247       1 0000000085E23020
   446 00000000863835C0 80152248       1 0000000085E25820
   447 00000000863835C8 80152249       1 0000000085E28020
   448 00000000863835D0 80152250       1 0000000085E2A820
   449 00000000863835D8 80152251       1 0000000085E2D020
   450 00000000863835E0 80152252       1 0000000085E2F820
   451 00000000863835E8 80152253       1 0000000085E32020
   452 00000000863835F0 80152254       1 0000000085E34820
   453 00000000863835F8 80152255       1 0000000085E37020
   454 0000000086383600 80152256       1 0000000085E39820
   455 0000000086383608 80152257       1 0000000085E3C020
   456 0000000086383610 80152258       1 0000000085E3E820
   457 0000000086383618 80152259       1 0000000085E41020
   458 0000000086383620 80152260       1 0000000085E43820
   459 0000000086383628 80152261       1 0000000085E46020
   460 0000000086383630 80152262       1 0000000085E48820
   461 0000000086383638 80152263       1 0000000085E4B020
   462 0000000086383640 80152264       1 0000000085E4D820
   463 0000000086383648 80152265       1 0000000085E50020
   464 0000000086383650 80152266       1 0000000085E52820
   465 0000000086383658 80152267       1 0000000085E55020
   466 0000000086383660 80152268       1 0000000085E57820
   467 0000000086383668 80152269       1 0000000085E5A020
   468 0000000086383670 80152270       1 0000000085E5C820
   469 0000000086383678 80152271       1 0000000085E5F020
   470 0000000086383680 80152272       1 0000000085E61820
   471 0000000086383688 80152273       1 0000000085E64020
   472 0000000086383690 80152274       1 0000000085E66820
   473 0000000086383698 80152275       1 0000000085E69020
   474 00000000863836A0 80152276       1 0000000085E6B820
   475 00000000863836A8 80152277       1 0000000085E6E020
   476 00000000863836B0 80152278       1 0000000085E70820
   477 00000000863836B8 80152279       1 0000000085E73020
   478 00000000863836C0 80152280       1 0000000085E75820
   479 00000000863836C8 80152281       1 0000000085E78020
   480 00000000863836D0 80152282       1 0000000085E7A820
   481 00000000863836D8 80152283       1 0000000085E7D020
   482 00000000863836E0 80152284       1 0000000085E7F820
   483 00000000863836E8 80152285       1 0000000085E82020
   484 00000000863836F0 80152286       1 0000000085E84820
   485 00000000863836F8 80152287       1 0000000085E87020
   486 0000000086383700 80152288       1 0000000085E89820
   487 0000000086383708 80152289       1 0000000085E8C020
   488 0000000086383710 80152290       1 0000000085E8E820
   489 0000000086383718 80152291       1 0000000085E91020
   490 0000000086383720 80152292       1 0000000085E93820
   491 0000000086383728 80152293       1 0000000085E96020
   492 0000000086383730 80152294       1 0000000085E98820
   493 0000000086383738 80152295       1 0000000085E9B020
   494 0000000086383740 80152296       1 0000000085E9D820
   495 0000000086383748 80152297       1 0000000085EA0020
   496 0000000086383750 80152298       1 0000000085EA2820
   497 0000000086383758 80152299       1 0000000085EA5020
   498 0000000086383760 80152300       1 0000000085EA7820
   499 0000000086383768 80152301       1 0000000085EAA020
   500 0000000086383770 80152302       1 0000000085EAC820
   501 0000000086383778 80152303       1 0000000085EAF020
   502 0000000086383780 80152304       1 0000000085EB1820
   503 0000000086383788 80152305       1 0000000085EB4020
   504 0000000086383790 80152306       1 0000000085EB6820
   505 0000000086383798 80152307       1 0000000085EB9020
   506 00000000863837A0 80152308       1 0000000085EBB820
   507 00000000863837A8 80152309       1 0000000085EBE020
   508 00000000863837B0 80152310       1 0000000085EC0820
   509 00000000863837B8 80152311       1 0000000085EC3020
   510 00000000863837C0 80152312       1 0000000085EC5820
   511 00000000863837C8 80152313       1 0000000085EC8020
   512 00000000863837D0 80152314       1 0000000085ECA820
512 rows selected.
--//注意看下划线，在这个地址出现跳跃，也就是使用另外1个chunk。
--//保存以上结果到文件q1.txt,执行如下。
$ awk "{printf "%s 0x%s %s ",$1,$5,$5}" q1.txt | awk "NR==1{a=$1;b=strtonum($2) } NR>1{ print a,$3,(strtonum($2)-b)/($1-a);a=$1;b=strtonum($2)}" | grep -v " 10240$"
49 0000000089BFD140 58726760
50 00000000893E7650 -8477424
52 0000000087FFBB08 -20898632
53 00000000877F6B58 -8409008
56 00000000873F2720 -4232248
61 0000000086740200 -13354272
137 0000000086B08AD0 3199184
235 0000000086FE4758 4101256
246 0000000085C34020 -20748088

--//取出第2个字段，编辑保存aa3.txt：
$ cat aa3.txt
@fcha 0x863fd8e0
@fcha 0000000089BFD140
@fcha 00000000893E7650
@fcha 0000000087FFBB08
@fcha 00000000877F6B58
@fcha 00000000873F2720
@fcha 0000000086740200
@fcha 0000000086B08AD0
@fcha 0000000086FE4758
@fcha 0000000085C34020

SYS@book> @ aa3.txt
Find in which heap (UGA, PGA or Shared Pool) the memory address 0x863fd8e0 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000086034000          1          1 permanent memor     3973080 perm              0 00

Find in which heap (UGA, PGA or Shared Pool) the memory address 0000000089BFD140 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000089BFD120          1          1 permanent memor       10272 perm              0 00

Find in which heap (UGA, PGA or Shared Pool) the memory address 00000000893E7650 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 00000000893E7630          1          1 permanent memor       20512 perm              0 00

Find in which heap (UGA, PGA or Shared Pool) the memory address 0000000087FFBB08 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000087FFBAE8          1          1 permanent memor       10272 perm              0 00

Find in which heap (UGA, PGA or Shared Pool) the memory address 00000000877F6B58 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 00000000877F6B38          1          1 permanent memor       30752 perm              0 00

Find in which heap (UGA, PGA or Shared Pool) the memory address 00000000873F2720 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 00000000873F2700          1          1 permanent memor       51232 perm              0 00

Find in which heap (UGA, PGA or Shared Pool) the memory address 0000000086740200 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 00000000867401E0          1          1 permanent memor      778272 perm              0 00

Find in which heap (UGA, PGA or Shared Pool) the memory address 0000000086B08AD0 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000086B08AB0          1          1 permanent memor     1003552 perm              0 00

Find in which heap (UGA, PGA or Shared Pool) the memory address 0000000086FE4758 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000086FE4738          1          1 permanent memor      112672 perm              0 00

Find in which heap (UGA, PGA or Shared Pool) the memory address 0000000085C34020 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000085C34000          1          1 permanent memor     3979368 perm              0 00

--//如果对比前面测试，视乎有某种异曲同工之处，真不知道为什么oracle这样分配bucket对应的chunk。

4.通过一个例子来讲解如何定位该bucket地址的.

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

--//注意bucket从0开始计数。对应前面++++行(247行)。地址为0000000085C34020。
--//246*8 = 1968
SYS@book> @ calc x00000000863827D8 + 1968
                                DEC                  HEX
----------------------------------- --------------------
                  2251829128.000000             86382F88
--//注意看前面++++行，正好对应ADDR值，通过这个地址，知道保存的值KSMMMVAL=x0000000085C34020
--//   247 0000000086382F88 80152049       1 0000000085C34020
--//MOD(63016,256)=40, 40*40 = 1600

SYS@book> @ calc x0000000085C34020 + 1600
                                DEC                  HEX
----------------------------------- --------------------
                  2244167264.000000             85C34660

--//x85C34660这个地址记录的就是bucket = 63016 的开始地址。

SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 00000037 00000000 0000F628 00000000

--//执行如下，注意我的测试设置session_cached_cursors=50，多次执行后会变成"软软解析"登录会话第一次后执行的是软解析。
--//我采用的执行方式是执行完成马上退出。
$ echo "Select sysdate from dual ;"|sqlplus   -s -l / as sysdba
SYSDATE
-------------------
2022-03-03 09:27:59

--//每执行以上语句1次，使用oradebug peek 0x85C34660 40在另外会话查询该地址保存前40字节内容。
SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 00000037 00000000 0000F628 00000000
SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 00000039 00000000 0000F628 00000000
SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 0000003B 00000000 0000F628 00000000
SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 0000003C 00000000 0000F628 00000000
SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 0000003D 00000000 0000F628 00000000
SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 0000003E 00000000 0000F628 00000000

--//注意看mutex结构的第4双字节内容，这里就是第8双字节内容的变化，0x37->0x39->0x3B->0x3c->0x3d->0x3e.不知道为什么第2次,第
--//3次执行,gets记数增加2(也许是其它因素的干扰)，后面每次执行gets记数增加1.

--//你可以打开一个新会话，执行如下：
SYS@book> Select sysdate from dual ;
SYSDATE
-------------------
2022-03-03 09:40:38

SYS@book> Select sysdate from dual ;
SYSDATE
-------------------
2022-03-03 09:40:43

SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 0000003E 00000000 0000F628 00000000
SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 00000040 00000000 0000F628 00000000
SYS@book> oradebug peek 0x85C34660 40
[085C34660, 085C34688) = 81EC41D0 00000000 81EC41D0 00000000 00000000 00000000 00000040 00000000 0000F628 00000000
--//你可以发现第2次执行就是软软解析，不再touch该bucket。gets的记数不再增加。

SYS@book> @ sharepool/shp4 dvk83p4bbtxj8 0
KGLHDADR              KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
--------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000081EC41D0 Select sysdate from dual                          0          0          0 00000000827ED258 0000000082EBD838       4536       8088       3082     15706      15706 381482536 dvk83p4bbtxj8          0
parent handle address 0000000081EC41D0 Select sysdate from dual                          0          0          0 00000000831295B0 00                     4720          0          0      4720       4720 381482536 dvk83p4bbtxj8      65535

--//你可以发现bucket地址记录的内容前面就是该语句sql_id=dvk83p4bbtxj8的父游标地址x0000000081EC41D0。
--//如果你查看父游标0x81EC41D0 开始40字节内容。
SYS@book> oradebug peek 0x81EC41D0 40
[081EC41D0, 081EC41F8) = 85C34660 00000000 85C34660 00000000 82EBD160 00000000 81EC4328 00000000 00000000 10012841
--//开始部分x85C34660正好是该bucket的地址，oracle正是通过这样的方式链接多个bucket一样的sql语句。

--//关于这部分内容大家可以参考，链接：http://blog.itpub.net/267265/viewspace-2775038/=>[20210602]分析library cache转储 5.txt
--//另外我还给测试_kghdsidx_count <> 1 的情况，我记忆好像跟我这里的测试不同，，另外写一篇blog。

5. 附上shp4.sql脚本：

$ cat sharepool/shp4.sql
column N0_6_16 fORMat 99999999
SELECT DECODE (kglhdadr,
               kglhdpar, "parent handle address",
               "child handle address")
          text,
       kglhdadr,
       kglhdpar,
       substr(kglnaobj,1,40) c40,
           KGLHDLMD,
           KGLHDPMD,
           kglhdivc,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
         0 kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
           kglnahsh,
           kglobt03 ,
           kglobt09
FROM x$kglob
WHERE kglobt03 = "&1" or kglhdpar="&1" or kglhdadr="&1" or KGLNAHSH= &2;
--//昏，发现我脚本2021/12/20改动过，text前面加了注解，应该取消，这样KGLHDADR显示是text的内容。

--//另外fcha.sql ,calc.sql脚本来自Tanel Poder的TPT脚本。在tanelpoder.com/downloads/可以下载。