What problems can shared pool solve?

Time:2021-10-28

Use the shared pool latch.txt

–//My test environment is very small. There is only one shared pool latch. If there are multiple, one SQL statement should use that shared pool latch
–//According to the previous study, guess should be in hash_ value , bucket_ size , _ kghdsidx_ Count related, test and see.
–//Note: bucket is the default_ size =2^(9+8) =131072

1. Environment:
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.

–//Restart the database

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.
–//Write down 7 addr addresses. 000000006010d9a0 000000006010da40 000000006010dae0 000000006010db80 000000006010dc20 000000006010dcc0 000000006100dd60

2. Test:
–//Edit GDB script:
$ 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

–//Execute select * from dept where deptno = 20;, Desc dept multiple times to avoid some recursion
–// hash_value % bucket_size % _kghdsidx_count
–// 95129850 % 7 = 4
–// 95129850 % 131072 % 7 = 2

–//Verify the 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 corresponds to child # = 3
–//Estimate hash_ value % bucket_ size % _ kghdsidx_ Count + 1. Or_ kghdsidx_ count-hash_ value % _ kghdsidx_ Count (no!!)
–//It can be roughly inferred that the calculation formula used is hash_ value % bucket_ size % _ kghdsidx_ count + 1
–//Find more SQL statements to verify.

3. Continue to verify
–//Note that it is best to execute select sysdate from dual before verification; Multiple exclusiongameThe impact encountered in the link.
–//Because the cursor will not be released immediately after the execution of sqlplus under 11g, the previous statement is seen, which may be wrong.

–//Session 3, the main purpose is to determine the 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:
–//Remind again to execute www.cungun.comselect sysdate from dual; Many times.
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. It’s all right.

Summary:
–//Indicates that an SQL statement will use the hash of its SQL statement_ value % bucket_ size % _ kghdsidx_ Shared pool latch of count + 1