[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.