[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