共计 4599 个字符，预计需要花费 12 分钟才能阅读完成。
应用那个 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_CHILDRENWHERE 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
cend
break kslgetl if $rdi==0X6010DA40
commands
silent
printf "child#=2 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
cend
break kslgetl if $rdi==0X6010DAE0
commands
silent
printf "child#=3 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
cend
break kslgetl if $rdi==0X6010DB80
commands
silent
printf "child#=4 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
cend
break kslgetl if $rdi==0X6010DC20
commands
silent
printf "child#=5 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
cend
break kslgetl if $rdi==0X6010DCC0
commands
silent
printf "child#=6 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
cend
break kslgetl if $rdi==0X6010DD60
commands
silent
printf "child#=7 kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
cend
–// 先执行 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; 屡次排除链接
–// 因为 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_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
HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX
3912471479 14ymr4znm74xr 0 e93393b7
–// 3912471479 % 131072 % 7 +1 = 4
SCOTT select * from dept where deptno=50;
no rows selected
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.
