[20210803]使用那个shared pool latch(补充).txt

[20210803]使用那个shared pool latch(补充).txt --//前一阵子写了http://blog.itpub.net/267265/viewspace-2780256/=>[20210708]使用那个shared pool latch.txt --//里面提到sql语句使用的shared pool latch.是 sql语句的hash_value % bucket_size % _kghdsidx_count +1 , --//我是通过例子以及gdb来验证,有人质疑不会是巧合,当然我测试前确实是在猜测. --//实际上可以通过一个简单的方法验证我的判断是对的. 1.环境: SYS@127.0.0.1:9014/ywdb> @ 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@127.0.0.1:9014/ywdb> @ 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 SYS@127.0.0.1:9014/ywdb> @ hide idx_count NAME            DESCRIPTION        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD --------------- ------------------ ------------- ------------- ------------ ----- --------- _kghdsidx_count max kghdsidx count TRUE          7             7            FALSE FALSE SYS@127.0.0.1:9014/ywdb> @ tix New tracefile_identifier =  /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_3733_0001.trc SYS@127.0.0.1:9014/ywdb> oradebug setmypid Statement processed. SYS@127.0.0.1:9014/ywdb> oradebug dump library_cache 4; Statement processed. --//生产系统,转储有点慢... SYS@127.0.0.1:9014/ywdb> @ tix New tracefile_identifier =  /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_3733_0002.trc SYS@127.0.0.1:9014/ywdb> oradebug dump heapdump 2; Statement processed. # du  -sm  /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_3733_000*.trc 215     /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_3733_0001.trc 197     /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_3733_0002.trc # egrep "HEAP DUMP|KGLH0\^|SQLA\^" /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_3733_0002.trc >| aa.txt 2.分析aa.txt: --//单独读出 HEAP DUMP heap name="sga heap(1,0)" 与 HEAP DUMP heap name="sga heap(2,0)" 之间的内容. --//保存为a0.txt # grep ds=0x a0.txt >| a1.txt # sed '1,$s/^.*\^//;1,$s/\".*$//' a1.txt | sort | uniq >| a2.txt */ head -5 a2.txt 10017bdc 1003249 1003249 10210a5e 10439dee --//编辑修改如下,注意小写换成大写. # cat a2.txt | tr '[a-z]' '[A-Z]' > a3.txt --//开头加入ibase=16 # head -5 a3.txt ibase=16 10017BDC  % 20000 % 7 +1 1003249   % 20000 % 7 +1 1003249    % 20000 % 7 +1 10210A5E  % 20000 % 7 +1 --//简单说明一下我采用16进制计算,131072 = 0x20000. # cat a3.txt | bc -q | uniq -c    3197 1 --//你可以发现全部结果都是1的.说明我的判断是正确的,其它一样验证. --//补充说明:   Chunk        5f1000f70 sz=     4096    recreate  "KGLH0^10210a5e "  latch=(nil)   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   Chunk        5f1001f70 sz=     4096    recreate  "SQLA^347151dc  "  latch=(nil)   Chunk        5f1002f70 sz=     4096    freeable  "SQLA^105a0126  "  ds=0x59f9aa4a8 --//我发现下划线的不再这个范围. --//10210a5e = 270600798 --//270600798 %131072 %7+1 = 4 --//所以我计算的实际上包含ds=0x那些行. $ egrep "HEAP DUMP|10210a5e" /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_3733_0002.trc HEAP DUMP heap name="sga heap"  desc=0x60001190 HEAP DUMP heap name="sga heap(1,0)"  desc=0x6005cf30   Chunk        5f1000f70 sz=     4096    recreate  "KGLH0^10210a5e "  latch=(nil)   Chunk        5f1000f70 sz=     4096    recreate  "KGLH0^10210a5e "  latch=(nil) HEAP DUMP heap name="sga heap(2,0)"  desc=0x600667f8 HEAP DUMP heap name="sga heap(3,0)"  desc=0x600700c0 HEAP DUMP heap name="sga heap(4,0)"  desc=0x60079988 HEAP DUMP heap name="sga heap(5,0)"  desc=0x60083250 HEAP DUMP heap name="sga heap(6,0)"  desc=0x6008cb18 HEAP DUMP heap name="sga heap(7,0)"  desc=0x600963e0 # egrep "HEAP DUMP|347151dc" /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_3733_0002.trc HEAP DUMP heap name="sga heap"  desc=0x60001190 HEAP DUMP heap name="sga heap(1,0)"  desc=0x6005cf30   Chunk        59ec223f0 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        5b877a1b0 sz=     4096    recreate  "KGLH0^347151dc "  latch=(nil)   Chunk        5b8f98f88 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        5bbb1e0b8 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        5d49f15f0 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        5d5165070 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        5d53da328 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        5d6778ba8 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        5f1001f70 sz=     4096    recreate  "SQLA^347151dc  "  latch=(nil)   Chunk        5f2d78ce8 sz=     4096    freeable  "KGLH0^347151dc "  ds=0x5d6464cb8   Chunk        611b2da28 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        61238b8e0 sz=     4096    recreate  "KGLH0^347151dc "  latch=(nil)   Chunk        6136fbba8 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        613bab998 sz=     4096    freeable  "SQLA^347151dc  "  ds=0x61238c058   Chunk        5f1001f70 sz=     4096    recreate  "SQLA^347151dc  "  latch=(nil)   Chunk        5b877a1b0 sz=     4096    recreate  "KGLH0^347151dc "  latch=(nil) HEAP DUMP heap name="sga heap(2,0)"  desc=0x600667f8 HEAP DUMP heap name="sga heap(3,0)"  desc=0x600700c0 HEAP DUMP heap name="sga heap(4,0)"  desc=0x60079988 HEAP DUMP heap name="sga heap(5,0)"  desc=0x60083250 HEAP DUMP heap name="sga heap(6,0)"  desc=0x6008cb18 HEAP DUMP heap name="sga heap(7,0)"  desc=0x600963e0 --//我计算了HEAP DUMP heap name="sga heap(7,0)"  desc=0x600963e0以下的内容. # cat b3.txt | bc -q | uniq -c    2826 7