[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