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

来源：这里教程网时间：2026-03-03 17:29:13 作者：

[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\n",$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/可以下载。

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推