sharedpoollatch可以解决哪些问题？使用那个shared pool latch.txt --//我的测试环境

使用那个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.环境: SCOTTbook> 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

SCOTTbook> select * from dept where deptno=20;

DEPTNO DNAME LOC 20 RESEARCH DALLAS SCOTTbook> hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX

95129850 80baj2c2ur47u 0 5ab90fa

SYSbook> 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

SYSbook> 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. 如果相对electron有更多直观理解的, 也可以参考其格式如下:

游戏：www.cungun.com

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: SCOTTbook(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; SCOTTbook> 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;多次排除游戏的链接中遇到的影响。 --//因为11g下sqlplus执行后不会马上释放光标，导致看到的是前面的语句，这样可能不对。

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

SCOTTbook> select * from dept where deptno=10;

DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK SCOTTbook> hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX

911274289 4xamnunv51w9j 1 3650f131 --// 911274289 % 131072 % 7 +1 = 6

SCOTTbook> select * from dept where deptno=30;

DEPTNO DNAME LOC 30 SALES CHICAGO SCOTTbook> hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX

69952862 816w0g822qtay 0 42b655e --// 69952862 % 131072 % 7+1 = 4

SCOTTbook> select * from dept where deptno=40;

DEPTNO DNAME LOC 40 OPERATIONS BOSTON SCOTTbook> hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX

3912471479 14ymr4znm74xr 0 e93393b7 --// 3912471479 % 131072 % 7 +1 = 4

SCOTTbook> select * from dept where deptno=50; no rows selected

SCOTTbook> hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX

1927948053 bswp9zttfn9sp 0 72ea2715 --//1927948053 % 131072 % 7 +1 = 3

--//session 1: --//再次提醒执行www.cungun.comselect 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.