[20220301]oracle如何定位使用library cache mutex.txt
![[20220301]oracle如何定位使用library cache mutex.txt](https://static.528045.com/imgs/26.jpg?imageMogr2/format/webp/blur/1x0/quality/35 "[20220301]oracle如何定位使用library cache mutex.txt")
![[20220301]oracle如何定位使用library cache mutex.txt](/upload/202205/01/wi1daadyrl0.jpg)
[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
",$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
",$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/可以下载。
原文地址：https://www.cnblogs.com/lfree/archive/2022/03/04/15962856.html
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[20220301]oracle如何定位使用library cache mutex.txt](/upload/202205/01/tldq1wofmge.jpg)
![[20220302]oracle如何定位使用library cache mutex 2.txt](/upload/202205/01/4k0a3vut53i.jpg)
![[20220303]oracle如何定位使用library cache mutex 3.txt](/upload/202205/01/zjp2ujzhgm0.jpg)
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/31.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
