[20220301]oracle如何定位使用library cache mutex.txt
[20220301]oracle如何定位使用library cache mutex.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的地址呢？自己嘗試做這方面的探究。
1.環境：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> @ hide _kghdsidx_count
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
--------------- ------------------ ------------- ------------- ------------ ----- ---------
_kghdsidx_count max kghdsidx count TRUE 1 1 FALSE FALSE
SYS@book> @ hide _kgl_bucket_count
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
----------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
_kgl_bucket_count Library cache hash table bucket count (2^_kgl_bucket_count * 256) TRUE 9 9 FALSE FALSE
--//預設2^9*256 = 131072.
--//修改如下:
alter system set "_kgl_bucket_count"=6 scope=spfile;
--//重啟資料庫略.
--//建立與使用 2^6*256 = 16384 library cache bucket.我本來想法是全部bucket的資訊在一個chunk，實際的測試沒有出現這樣的
--//情況，看下面的測試。
--//首先說明一下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。
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;
/
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 &
--//可以將select其中一個字元換成大寫，再次執行，執行語句沒有使用繫結變數，主要目的是儘可能多的使用共享記憶體,我的本意是使
--//用更多的bucket,這樣也許更好展開分析,等待指令碼執行完成.
--//因為我是利用中午的時間執行該指令碼，好像執行時間有點長。我以為這樣可以導致每個bucket都用上，實際上沒用,因為我的測試環
--//境共享池不大，如果沒有物件或者被剔除共享池，dump library_cache時不會轉儲,實際上我最想知道的是bucket 0的地址,不過這個
--//資訊理論是可以透過其他bucket的地址推匯出來.
3.轉儲library_cache:
SYS@book> oradebug setmypid
Statement processed.
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_7680_0001.trc
SYS@book> oradebug dump library_cache 10;
Statement processed.
$ grep "Bucket:" book_ora_7680_0001.trc | sed "s/^Bucket: #=//;s/Mutex=//;s/(.*)//" > b1.txt
--//將Bucket以及mutex地址寫入檔案b1.txt
$ awk 'NR==1{a=$1;b=strtonum($2) } NR>1{ print (strtonum($2)-b)/($1-a);a=$1;b=strtonum($2)}' b1.txt | sort | uniq -c | sort -nr
3641 40
1 -765346
1 -4243792
1 -4.18173e+06
1 -3341088
1 -326305
1 14677506
--//可以發現40出現次數最多3641次,同時也說明我前面執行的指令碼使用很多bucket的想法不現實.
--//說明許多mutex的地址間隔是40個位元組，注實際上間隔40位元組的mutex地址一定在相同的chunk中.
--//參考[20210524]分析library cache轉儲 3.txt 的測試。
--//查詢mutex地址出現跳躍的情況，也就是間隔不是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}' b1.txt | grep -v "40$"
12798 0x863ff998 12802 0x89bfd1a0 14677506
13055 0x89bff928 13057 0x893e7688 -4243792
13567 0x893ec638 13572 0x87ffbbb8 -4.18173e+06
13819 0x87ffe250 13830 0x877f6c58 -765346
14584 0x877fe228 14597 0x873f27f8 -326305
15869 0x873feeb8 15873 0x86740238 -3341088
--//oradebug dump library_cache 10僅僅顯示有物件bucket,另外從最後一列數值看這些chunk並不在連續的位置,而且還出現負數的情
--//況.單獨儲存第2列的值到文字aa1.txt並且修改行如下：
$ cat aa1.txt
@fcha 0x863ff998
@fcha 0x89bff928
@fcha 0x893ec638
@fcha 0x87ffe250
@fcha 0x877fe228
@fcha 0x873feeb8
--//執行aa1.txt指令碼:
SYS@book> @ aa1.txt
Find in which heap (UGA, PGA or Shared Pool) the memory address 0x863ff998 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000086034000 1 1 permanent memor 3979736 perm 0 00
Find in which heap (UGA, PGA or Shared Pool) the memory address 0x89bff928 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 0x893ec638 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 0x87ffe250 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 0x877fe228 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 0x873feeb8 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
--//我開始以為修改"_kgl_bucket_count"=6 能使整個資訊放在一個chunk，因為第1個chunk大小3979736
--//2^6*256 = 16384 ,16384*40 = 655360 ,足以容納在該chunk中，而實際的測試情況不是這樣。
--//計算第1個chunk的結束地址:
SYS@book> @ calc x86034000 + 3979736
DEC HEX
----------------------------------- --------------------
2252339672.000000 863FF9D8
--//檢查轉儲檔案的資訊
--//Bucket: #=3 Mutex=0x86382a60(0, 44, 1, 6)
--//有點失望,我本來很想知道Bucket: #=0的mutex地址,不過從前面的測試這個地址是可以推匯出來的.
--//Mutex=0x86382a60 - 16 就是該bucket的地址0x86382a50。如果給算Bucket: #=0的地址就要減去40*3=120.
SCOTT@book> @ calc 0x86382a50 - 120
DEC HEX
----------------------------------- --------------------
2251827672.000000 863829D8
--// 40*16384 = 655360
SCOTT@book> @ calc 0x863829D8 + 655360
DEC HEX
----------------------------------- --------------------
2252483032.000000 864229D8
--//0x864229D8已經超出x86034000 - x863FF9D8 範圍。從前面執行的輸出
$ 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}' b1.txt | grep -v "40$"
12798 0x863ff998 12802 0x89bfd1a0 14677506
...
--//大致可以推斷第1個chunk儲存的mutex的bucket值最大值12799,共12800個.0x863ff998已經非常接近 0x863FF9D8。
--// 40*12800 = 512000
SCOTT@book> @ calc 0x863829D8 + 512000
DEC HEX
----------------------------------- --------------------
2252339672.000000 863FF9D8
--//正好對應第1個chunk的結束地址。
SYS@book> @ calc x863829D8 - x86034000
DEC HEX
----------------------------------- --------------------
3467736.000000 34E9D8
--//開頭有3467736位元組用來儲存什麼資訊，這個也太大了。
--//查詢第1個chunk含有值x00000000863829D8的地址：
SYS@book> select* from X$KSMMEM where addr between hextoraw('0000000086034000') and hextoraw('00000000863FF9D8') and ksmmmval =hextoraw('00000000863829D8');
ADDR INDX INST_ID KSMMMVAL
---------------- ---------- ---------- ----------------
00000000863827D8 80151803 1 00000000863829D8
00000000863829D8 80151867 1 00000000863829D8 -> bucket 0 地址。
00000000863829E0 80151868 1 00000000863829D8 -> bucket 0 地址。
SYS@book> oradebug peek 0x00000000863829D8 40
[0863829D8, 086382A00) = 863829D8 00000000 863829D8 00000000 00000000 00000000 00000C5E 00000001 00000000 00000000
--//因為該bucket地址上沒有物件，bucket 0 指向的地址執行自己，兩個都是0x863829D8.其中一個就是在0x00000000863829E0位置.
--//這樣輸出的第一條記錄addr記錄的就是(儲存的值是0x00000000863827D8)應該是bucket首地址，我估計裡面儲存2^6=64個地址。
--//2^6 = 64 ,每個地址佔8個位元組 ，64*8 = 512。
SYS@book> @ calc 0x00000000863827D8 + 512
DEC HEX
----------------------------------- --------------------
2251827672.000000 863829D8
--//不會是巧合把，正好結尾對應0x863829D8，也就是bucket 0 的地址。
--//查詢hextoraw('00000000863827D8') ~ hextoraw('00000000863829D0') 的資訊。
--//注:如果查詢範圍hextoraw('00000000863827D8') ~ hextoraw('00000000863829D8'),結果不對,多了一條記錄.因為寫成
--//addr >= hextoraw('00000000863827D8') and addr < hextoraw('00000000863829D8') ;
SYS@book> select rownum, x$ksmmem.* from X$KSMMEM where addr between hextoraw('00000000863827D8') and hextoraw('00000000863829D0') ;
ROWNUM ADDR INDX INST_ID KSMMMVAL
------ ---------------- -------- ------- ----------------
1 00000000863827D8 80151803 1 00000000863829D8
2 00000000863827E0 80151804 1 00000000863851D8
3 00000000863827E8 80151805 1 00000000863879D8
4 00000000863827F0 80151806 1 000000008638A1D8
5 00000000863827F8 80151807 1 000000008638C9D8
6 0000000086382800 80151808 1 000000008638F1D8
7 0000000086382808 80151809 1 00000000863919D8
8 0000000086382810 80151810 1 00000000863941D8
9 0000000086382818 80151811 1 00000000863969D8
10 0000000086382820 80151812 1 00000000863991D8
11 0000000086382828 80151813 1 000000008639B9D8
12 0000000086382830 80151814 1 000000008639E1D8
13 0000000086382838 80151815 1 00000000863A09D8
14 0000000086382840 80151816 1 00000000863A31D8
15 0000000086382848 80151817 1 00000000863A59D8
16 0000000086382850 80151818 1 00000000863A81D8
17 0000000086382858 80151819 1 00000000863AA9D8
18 0000000086382860 80151820 1 00000000863AD1D8
19 0000000086382868 80151821 1 00000000863AF9D8
20 0000000086382870 80151822 1 00000000863B21D8
21 0000000086382878 80151823 1 00000000863B49D8
22 0000000086382880 80151824 1 00000000863B71D8
23 0000000086382888 80151825 1 00000000863B99D8
24 0000000086382890 80151826 1 00000000863BC1D8
25 0000000086382898 80151827 1 00000000863BE9D8
26 00000000863828A0 80151828 1 00000000863C11D8
27 00000000863828A8 80151829 1 00000000863C39D8
28 00000000863828B0 80151830 1 00000000863C61D8
29 00000000863828B8 80151831 1 00000000863C89D8
30 00000000863828C0 80151832 1 00000000863CB1D8
31 00000000863828C8 80151833 1 00000000863CD9D8
32 00000000863828D0 80151834 1 00000000863D01D8
33 00000000863828D8 80151835 1 00000000863D29D8
34 00000000863828E0 80151836 1 00000000863D51D8
35 00000000863828E8 80151837 1 00000000863D79D8
36 00000000863828F0 80151838 1 00000000863DA1D8
37 00000000863828F8 80151839 1 00000000863DC9D8
38 0000000086382900 80151840 1 00000000863DF1D8
39 0000000086382908 80151841 1 00000000863E19D8
40 0000000086382910 80151842 1 00000000863E41D8
41 0000000086382918 80151843 1 00000000863E69D8
42 0000000086382920 80151844 1 00000000863E91D8
43 0000000086382928 80151845 1 00000000863EB9D8
44 0000000086382930 80151846 1 00000000863EE1D8
45 0000000086382938 80151847 1 00000000863F09D8
46 0000000086382940 80151848 1 00000000863F31D8
47 0000000086382948 80151849 1 00000000863F59D8
48 0000000086382950 80151850 1 00000000863F81D8
49 0000000086382958 80151851 1 00000000863FA9D8
50 0000000086382960 80151852 1 00000000863FD1D8
51 0000000086382968 80151853 1 0000000089BFD140
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
52 0000000086382970 80151854 1 00000000893E7650
53 0000000086382978 80151855 1 00000000893E9E50
54 0000000086382980 80151856 1 0000000087FFBB08
55 0000000086382988 80151857 1 00000000877F6B58
56 0000000086382990 80151858 1 00000000877F9358
57 0000000086382998 80151859 1 00000000877FBB58
58 00000000863829A0 80151860 1 00000000873F2720
59 00000000863829A8 80151861 1 00000000873F4F20
60 00000000863829B0 80151862 1 00000000873F7720
61 00000000863829B8 80151863 1 00000000873F9F20
62 00000000863829C0 80151864 1 00000000873FC720
63 00000000863829C8 80151865 1 0000000086740200
64 00000000863829D0 80151866 1 0000000086742A00
64 rows selected.
--//第2行的值減去第1行的值：
SYS@book> @ calc x00000000863851D8 － x00000000863829D8
DEC HEX
----------------------------------- --------------------
10240.000000 2800
SYS@book> @ calc 10240 / 40
DEC HEX
----------------------------------- --------------------
256.000000 100
--//正好等於256個。
--//如果你看前面的轉儲就可以知道bucket在多個chunk之中。
$ 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}' b1.txt | grep -v "40$"
12798 0x863ff998 12802 0x89bfd1a0 14677506
13055 0x89bff928 13057 0x893e7688 -4243792
+++++++++++++++++++++++++++++++++++++++
13567 0x893ec638 13572 0x87ffbbb8 -4.18173e+06
13819 0x87ffe250 13830 0x877f6c58 -765346
14584 0x877fe228 14597 0x873f27f8 -326305
15869 0x873feeb8 15873 0x86740238 -3341088
--//12798/256 = 49.9921875,可以推斷前面12800 個bucket儲存在chunksize=3979736的chunk中。
--//對應12800/256 = 50 ，前面地址表rownum=51一定出現在另外的chunk中。注意看下劃線的資訊。
--//很明顯一個特點就是前面rownum 1到50 欄位KSMMMVAL記錄的值結尾都是D8。
--//查詢第2個chunk的地址範圍。看前面的++++資訊。
SYS@book> @ fcha 89bff928
Find in which heap (UGA, PGA or Shared Pool) the memory address 89bff928 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
--// ROWNUM ADDR INDX INST_ID KSMMMVAL
--//---------- ---------------- ---------- ---------- ----------------
--// 51 0000000086382968 80151853 1 0000000089BFD140
SYS@book> @ fcha 89BFD140
Find in which heap (UGA, PGA or Shared Pool) the memory address 89BFD140 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
--//KSMCHPTR=0000000089BFD120, 指向的都是同一個chunk。
--//這樣知道bucket值後，透過它定位library cache muext的地址大致演算法就很清晰了。
--//找到該探查表 我這裡是0x00000000863827D8.
--//bucket/256 的結果取整就知道 對應該陣列的地址。再透過bucket%256 * 40 的結果 就知道定位該bucket的library cache muext的地址。
4.補充分析：
--//儲存select rownum, x$ksmmem.* from X$KSMMEM where addr between hextoraw('00000000863827D8') and hextoraw('00000000863829D0') ;到檔案qq1.txt
$ awk '{printf "%s 0x%s\n",$1,$5}' qq1.txt | awk 'NR==1{a=$1;b=strtonum($2) } NR>1{ print a,(strtonum($2)-b)/($1-a);a=$1;b=strtonum($2)}'
1 10240
2 10240
3 10240
4 10240
5 10240
6 10240
7 10240
8 10240
9 10240
10 10240
11 10240
12 10240
13 10240
14 10240
15 10240
16 10240
17 10240
18 10240
19 10240
20 10240
21 10240
22 10240
23 10240
24 10240
25 10240
26 10240
27 10240
28 10240
29 10240
30 10240
31 10240
32 10240
33 10240
34 10240
35 10240
36 10240
37 10240
38 10240
39 10240
40 10240
41 10240
42 10240
43 10240
44 10240
45 10240
46 10240
47 10240
48 10240
49 10240
50 58720104
~~~~~~~~~~~
51 -8477424
52 10240
53 -20898632
54 -8409008
55 10240
56 10240
57 -4232248
58 10240
59 10240
60 10240
61 10240
62 -13354272
63 10240
--//前面差值都是10240，有點奇怪的是後面的chunk大小不一樣。我總有1個感覺這些chunk不是在資料庫啟動是分配的。
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 743297024 bytes
Fixed Size 2256832 bytes
Variable Size 205520960 bytes
Database Buffers 528482304 bytes
Redo Buffers 7036928 bytes
Database mounted.
Database opened.
SYS@book> select rownum, x$ksmmem.* from X$KSMMEM where addr between hextoraw('00000000863827D8') and hextoraw('00000000863829D0') ;
ROWNUM ADDR INDX INST_ID KSMMMVAL
------ ---------------- -------- ------- ----------------
1 00000000863827D8 80151803 1 00000000863829D8
2 00000000863827E0 80151804 1 00000000863851D8
3 00000000863827E8 80151805 1 00000000863879D8
4 00000000863827F0 80151806 1 000000008638A1D8
5 00000000863827F8 80151807 1 000000008638C9D8
6 0000000086382800 80151808 1 000000008638F1D8
7 0000000086382808 80151809 1 00000000863919D8
8 0000000086382810 80151810 1 00000000863941D8
9 0000000086382818 80151811 1 00000000863969D8
10 0000000086382820 80151812 1 00000000863991D8
11 0000000086382828 80151813 1 000000008639B9D8
12 0000000086382830 80151814 1 000000008639E1D8
13 0000000086382838 80151815 1 00000000863A09D8
14 0000000086382840 80151816 1 00000000863A31D8
15 0000000086382848 80151817 1 00000000863A59D8
16 0000000086382850 80151818 1 00000000863A81D8
17 0000000086382858 80151819 1 00000000863AA9D8
18 0000000086382860 80151820 1 00000000863AD1D8
19 0000000086382868 80151821 1 00000000863AF9D8
20 0000000086382870 80151822 1 00000000863B21D8
21 0000000086382878 80151823 1 00000000863B49D8
22 0000000086382880 80151824 1 00000000863B71D8
23 0000000086382888 80151825 1 00000000863B99D8
24 0000000086382890 80151826 1 00000000863BC1D8
25 0000000086382898 80151827 1 00000000863BE9D8
26 00000000863828A0 80151828 1 00000000863C11D8
27 00000000863828A8 80151829 1 00000000863C39D8
28 00000000863828B0 80151830 1 00000000863C61D8
29 00000000863828B8 80151831 1 00000000863C89D8
30 00000000863828C0 80151832 1 00000000863CB1D8
31 00000000863828C8 80151833 1 00000000863CD9D8
32 00000000863828D0 80151834 1 00000000863D01D8
33 00000000863828D8 80151835 1 00000000863D29D8
34 00000000863828E0 80151836 1 00000000863D51D8
35 00000000863828E8 80151837 1 00000000863D79D8
36 00000000863828F0 80151838 1 00000000863DA1D8
37 00000000863828F8 80151839 1 00000000863DC9D8
38 0000000086382900 80151840 1 00000000863DF1D8
39 0000000086382908 80151841 1 00000000863E19D8
40 0000000086382910 80151842 1 00000000863E41D8
41 0000000086382918 80151843 1 00000000863E69D8
42 0000000086382920 80151844 1 00000000863E91D8
43 0000000086382928 80151845 1 00000000863EB9D8
44 0000000086382930 80151846 1 00000000863EE1D8
45 0000000086382938 80151847 1 00000000863F09D8
46 0000000086382940 80151848 1 00000000863F31D8
47 0000000086382948 80151849 1 00000000863F59D8
48 0000000086382950 80151850 1 00000000863F81D8
49 0000000086382958 80151851 1 00000000863FA9D8
50 0000000086382960 80151852 1 00000000863FD1D8
51 0000000086382968 80151853 1 0000000089BFD140
52 0000000086382970 80151854 1 00000000893E7650
53 0000000086382978 80151855 1 00000000893E9E50
54 0000000086382980 80151856 1 0000000087FFBB08
55 0000000086382988 80151857 1 00000000877F6B58
56 0000000086382990 80151858 1 00000000877F9358
57 0000000086382998 80151859 1 00000000877FBB58
58 00000000863829A0 80151860 1 00000000873F2720
59 00000000863829A8 80151861 1 00000000873F4F20
60 00000000863829B0 80151862 1 00000000873F7720
61 00000000863829B8 80151863 1 00000000873F9F20
62 00000000863829C0 80151864 1 00000000873FC720
63 00000000863829C8 80151865 1 0000000086740200
64 00000000863829D0 80151866 1 0000000086742A00
64 rows selected.
--//儲存為qq2.txt。
--//注：我的測試環境記憶體是手工分配的，重啟後該對應的地址不會變。
$ diff qq1.txt qq2.txt
--//看來不是，啟動時就已經配置好了。
$ awk '{printf "%s 0x%s %s\n",$1,$5,$5}' qq1.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$"
50 0000000089BFD140 58720104
51 00000000893E7650 -8477424
53 0000000087FFBB08 -20898632
54 00000000877F6B58 -8409008
57 00000000873F2720 -4232248
62 0000000086740200 -13354272
--//單獨儲存第2列的值到文字aa2.txt ,執行如下：
$ cat aa2.txt
@fcha 0x863ff998
@fcha 0000000089BFD140
@fcha 00000000893E7650
@fcha 0000000087FFBB08
@fcha 00000000877F6B58
@fcha 00000000873F2720
@fcha 0000000086740200
SYS@book> @ aa2.txt
Find in which heap (UGA, PGA or Shared Pool) the memory address 0x863ff998 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000086034000 1 1 permanent memor 3979736 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 784488 perm 0 00
--//一共使用7個chunk，你可以發現最小chunksize=10272 ,能容納256個bucket+muext的結構。
--//40*256 = 10240。
5.總結：
--//我給承認寫這類的東西自己與別人看起來都很亂，我這樣寫的主要目的是記錄我整個當時現場的探究歷程。
--//還是一點，寫這個東西要表達理解有點困難，也許是自己非常不擅長這類寫作，專業術語也可能用不對^_^。
--//當然我自己寫的東西我自己能看懂與理解，之所以寫這麼繁瑣，就是方便以後再看時能夠很快理解。
--//實際上很簡單在第一個chunk裡面記錄了一張表或者講一個陣列,記錄數量為2^_kgl_bucket_count,每個佔8位元組(我的OS 64位系統)
--//假設知道基地址A後,如果知道bucket值.使用bucket/256 取整就可以定位 該陣列的地址 等於 A + trunc(bucket/256)*8
--//再透過bucket%256 * 40 + A + trunc(bucket/256)*8 , 就知道定位該bucket的library cache mutex的地址。
--//以後補充_kgl_bucket_count=9預設值的情況。另外我的測試_kghdsidx_count=1,如果等於其它呢，我給另外寫blog驗證看看。
--//另外fcha.sql ,calc.sql指令碼來自Tanel Poder的TPT指令碼。在tanelpoder.com/downloads/可以下載。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2864481/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- [20190402]Library Cache mutex.txtMutex
- [20220303]oracle如何定位使用library cache mutex 3.txtOracleMutex
- 定位Library Cache pin,Library Cache lock等待的解決方法
- [20220302]oracle如何定位使用library cache mutex 2.txtOracleMutex
- Oracle Library cacheOracle
- 常用定位library cache lock的方法
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- 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 mutex x tipsOracleMutex
- oracle library cache之trace小記Oracle
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- 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的快速定位與解決
- 【TUNE_ORACLE】等待事件之“library cache pins”Oracle事件
- [Oracle]--Library cache lock 故障解決一例Oracle
- oracle library cache相關的等待事件及latchOracle事件
- 【ASM_ORACLE】Library Cache最佳化篇（二）Library cache load lock的概念和解決辦法ASMOracle
- library cache及buffer cache中如何利用hash演算法得到hash值演算法
- zt_Oracle Library cache 內部機制 說明Oracle
- oracle 12.2.0.1 crs call completion cpu使用率100% library cache lockOracle
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin等待分析
- LIBRARY CACHE LOCK 等待事件事件
- library cache pin 等待事件事件
- 解決Library Cache latchs
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- oracle調優之-共享池尺寸調優+library cache+dicitonary library 命中率Oracle