[20220302]oracle如何定位使用library cache mutex 2.txt
[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/可以下載。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2864482/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220301]oracle如何定位使用library cache mutex.txtOracleMutex
- [20220303]oracle如何定位使用library cache mutex 3.txtOracleMutex
- Oracle Library cache mutex x tipsOracleMutex
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- 'cursor:mutex ..'/ 'cursor:pin ..'/ 'library cache:mutex ..'型別的等待事件Mutex型別事件
- library cache: mutex X引發的故障Mutex
- 用markhot緩解library cache:mutex xMutex
- 定位Library Cache pin,Library Cache lock等待的解決方法
- oracle11g library cache-mutex x的處理測試OracleMutex
- [20170727]library cache: mutex X.txtMutex
- [20241105]跟蹤library cache lock library cache pin使用gdb(11g)2.txt
- Oracle Library cacheOracle
- 常用定位library cache lock的方法
- [20190402]Library Cache mutex.txtMutex
- Library Cache: Mutex X – Bug 20879889 – Fixed in 11.2.0.4Mutex
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- [20210508]分析library cache轉儲 2.txt
- oracle異常:library cache lockOracle
- zt_如何平面解決library cache lock和library cache pin
- 【ASK_ORACLE】Library Cache概念篇（二）之Library Cache Pin的定義Oracle
- 11G資料庫之library cache lock及library cache pin模擬結合hanganalyze定位資料庫
- oracle library cache之trace小記Oracle
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- [20201203]探究library cache mutex X 3.txtMutex
- [20220304]測試library cache mutex遇到的疑問.txtMutex
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt
- library cache pin和library cache lock的診斷分析
- library cache lock和library cache pin區別總結
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- Oracle Library cache內部機制詳解Oracle
- oracle 10049 event之library cache lockOracle
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- 0106library cache pin的快速定位與解決
- [20240920]跟蹤library cache lock library cache pin使用gdb.txt
- [20240827]分析為什麼出現library cache lock等待事件2.txt事件
- 故障：核心表業務高峰期授權導致library cache lock和mutex x競爭Mutex
- 【TUNE_ORACLE】等待事件之“library cache pins”Oracle事件