[20210708]使用那個shared pool latch.txt

[20210708]使用那個shared pool latch.txt

--//我的測試環境很小,僅僅1個shared pool latch.如果存在多個,一條sql語句應該會使用那個shared pool latch.
--//按照前面的學習,猜測應該於hash_value , bucket_size , _kghdsidx_count  相關,測試看看。
--//注：一般預設bucket_size ＝2^(9+8) =131072

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

SCOTT@book> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
  95129850 80baj2c2ur47u            0   5ab90fa

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

SYS@book> alter system set "_kghdsidx_count"=7 scope=spfile;
System altered.

--//重啟資料庫略
 
SELECT addr
        ,latch#
        ,child#
        ,level#
        ,name
        ,gets
        ,sleeps
        ,immediate_gets
        ,immediate_misses
        ,spin_gets
    FROM V$LATCH_CHILDREN
   WHERE name LIKE 'shared pool'
ORDER BY addr;

ADDR                 LATCH#     CHILD#     LEVEL# NAME         GETS     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES  SPIN_GETS
---------------- ---------- ---------- ---------- ----------- ----- ---------- -------------- ---------------- ----------
000000006010D9A0        336          1          7 shared pool 10126          0              0                0         29
000000006010DA40        336          2          7 shared pool  6220          0              0                0          5
000000006010DAE0        336          3          7 shared pool  8610          0              0                0          7
000000006010DB80        336          4          7 shared pool  7817          0              0                0         21
000000006010DC20        336          5          7 shared pool  7446          0              0                0         18
000000006010DCC0        336          6          7 shared pool  7302          1              0                0          7
000000006010DD60        336          7          7 shared pool  6347          0              0                0          3
7 rows selected.
--//記下7個addr地址. 000000006010D9A0 000000006010DA40 000000006010DAE0 000000006010DB80 000000006010DC20 000000006010DCC0 000000006010DD60.

2.測試:
--//編輯gdb指令碼:
$ cat shared_pool.gdb
set pagination off

break kslgetl if $rdi==0X6010D9A0
  commands
    silent
    printf "child#=1 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
    c
  end

break kslgetl if $rdi==0X6010DA40
  commands
    silent
    printf "child#=2 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
    c
  end

break kslgetl if $rdi==0X6010DAE0
  commands
    silent
    printf "child#=3 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
    c
  end

break kslgetl if $rdi==0X6010DB80
  commands
    silent
    printf "child#=4 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
    c
  end

break kslgetl if $rdi==0X6010DC20
  commands
    silent
    printf "child#=5 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
    c
  end

break kslgetl if $rdi==0X6010DCC0
  commands
    silent
    printf "child#=6 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
    c
  end

break kslgetl if $rdi==0X6010DD60
  commands
    silent
    printf "child#=7 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
    c
  end

--//先執行Select * from dept where deptno=20;,desc dept多次,避免一些遞迴.
--// hash_value % bucket_size % _kghdsidx_count
--// 95129850 %  7  = 4
--// 95129850 % 131072 % 7 = 2

--//驗證看看使用那個shared pool latch.
--//session 1:
SCOTT@book(1,7)> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
         1          7 4691                     DEDICATED 4692        24          4 alter system kill session '1,7' immediate;

SCOTT@book> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

--//session 2:
$ gdb -p 4692 -x shared_pool.gdb

...
Breakpoint 1 at 0x93f97a8
(gdb) c
...
child#=3 kslgetl 6010dae0, 1, 0, 4039
child#=3 kslgetl 6010dae0, 1, 0, 3980
child#=3 kslgetl 6010dae0, 1, 0, 4039
child#=3 kslgetl 6010dae0, 1, 2132183136, 3991
--//6010dae0 對應的就是CHILD#＝3.
--//估計 hash_value % bucket_size % _kghdsidx_count + 1 . 或者  _kghdsidx_count-hash_value % _kghdsidx_count(不對!!)
--//可以大致推測使用的計算公式是 hash_value % bucket_size % _kghdsidx_count + 1
--//多找幾條sql語句驗證看看。

3.繼續驗證看看.
--//注意驗證前最好執行select sysdate from dual;多次排除連結http://blog.itpub.net/267265/viewspace-2773241/中遇到的影響。
--//因為11g下sqlplus執行後不會馬上釋放游標，導致看到的是前面的語句，這樣可能不對。

--//session 3，主要目的確定hash_value:

SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
 911274289 4xamnunv51w9j            1  3650f131
--// 911274289 % 131072 % 7 +1  = 6

SCOTT@book> select * from dept where deptno=30;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
  69952862 816w0g822qtay            0   42b655e
--// 69952862 % 131072 % 7+1 = 4

SCOTT@book> select * from dept where deptno=40;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
3912471479 14ymr4znm74xr            0  e93393b7
--// 3912471479 % 131072 % 7 +1  = 4

SCOTT@book> select * from dept where deptno=50;
no rows selected

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
1927948053 bswp9zttfn9sp            0  72ea2715
--//1927948053 % 131072 % 7 +1   = 3

--//session 1:
--//再次提醒執行select sysdate from dual;多次。
select sysdate from dual;
select sysdate from dual;
select sysdate from dual;
select sysdate from dual;
select sysdate from dual;
select * from dept where deptno=10;
select sysdate from dual;
select sysdate from dual;
select * from dept where deptno=30;
select sysdate from dual;
select sysdate from dual;
select * from dept where deptno=40;
select sysdate from dual;
select sysdate from dual;
select * from dept where deptno=50;


--//session 2:
$ gdb -p 4692 -x shared_pool.gdb
...
child#=6 kslgetl 6010dcc0, 1, 0, 3980 => select * from dept where deptno=10;  => child#=6
...
child#=4 kslgetl 6010db80, 1, 0, 3980 => select * from dept where deptno=30;  => child#=4
...
child#=4 kslgetl 6010db80, 1, 0, 3980 => select * from dept where deptno=40;  => child#=4
...
child#=3 kslgetl 6010dae0, 1, 0, 4039 => select * from dept where deptno=50;  => child#=3

--//OK.都能對上。

總結:
--//說明一條sql語句會使用 其sql語句的 hash_value % bucket_size % _kghdsidx_count +1 的 shared pool latch.