[20260806]测试11g cursor_sharing=force下的library cache lock pin的情况.txt
[20260806]测试11g cursor_sharing=force下的library cache lock pin的情况.txt
--//从来没有测试11g下cursor_sharing=force下的library cache lock/library cache pin的情况，才发现与21c的情况存在一点点不同.
--//做一个记录：
1.环境：
SCOTT@book> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 11.2.0.4.0
BANNER : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL procedure successfully completed.
2.测试：
SCOTT@book> @ spid
==============================
SID : 84
SERIAL# : 3
PROCESS : 3141
SERVER : DEDICATED
SPID : 3142
PID : 37
P_SERIAL# : 1
KILL_COMMAND : alter system kill session '84,3' immediate;
PL/SQL procedure successfully completed.
SCOTT@book> @ curs force
alter session set cursor_sharing=force
Session altered.
$gdb -f -p 3142 -x /home/oracle/sqllaji/gdb/lkpn11g.gdb
Breakpoint 1 at 0x9829848
Breakpoint 2 at 0x9825d10
(gdb) c
Continuing.
--//第1次执行：
kgllkal count 01 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
kgllkal count 02 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kgllkal count 03 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
kgllkal count 04 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kglpnal count 01 -- handle address: 0000000086f25410, mode: 2 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kgllkal count 05 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
kgllkal count 06 -- handle address: 0000000086ed4f08, mode: 2 kglnaobj address:0x86ed50b0: "a52d14309df7c4ea$BUILD$"
kgllkal count 07 -- handle address: 0000000086ed4d98, mode: 1 kglnaobj address:0x86ed4f40: ""
kglpnal count 02 -- handle address: 0000000086ed4d98, mode: 3 kglnaobj address:0x86ed4f40: ""
kgllkal count 08 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
kgllkal count 09 -- handle address: 0000000086ed4aa8, mode: 1 kglnaobj address:0x86ed4c50: "c00a3f96ce940f7ba52d14309df7c4eaChild:0"
kglpnal count 03 -- handle address: 0000000086ed4aa8, mode: 3 kglnaobj address:0x86ed4c50: "c00a3f96ce940f7ba52d14309df7c4eaChild:0"
kgllkal count 10 -- handle address: 0000000086d22f80, mode: 1 kglnaobj address:0x86d23128: "SCOTT"
kgllkal count 11 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
kgllkal count 12 -- handle address: 0000000086fa4de8, mode: 2 kglnaobj address:0x86fa4f90: "DEPTSCOTT"
kglpnal count 04 -- handle address: 0000000086fa4de8, mode: 2 kglnaobj address:0x86fa4f90: "DEPTSCOTT"
kgllkal count 13 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
--//第2次执行：
kgllkal count 14 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
kgllkal count 15 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kgllkal count 16 -- handle address: 0000000086ed4d98, mode: 1 kglnaobj address:0x86ed4f40: ""
kgllkal count 17 -- handle address: 0000000086fa4de8, mode: 2 kglnaobj address:0x86fa4f90: "DEPTSCOTT"
kglpnal count 05 -- handle address: 0000000086fa4de8, mode: 2 kglnaobj address:0x86fa4f90: "DEPTSCOTT"
--//第3次执行：
kgllkal count 18 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
kgllkal count 19 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kgllkal count 20 -- handle address: 0000000086ed4d98, mode: 1 kglnaobj address:0x86ed4f40: ""
--//第4次执行：
kgllkal count 21 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
--//第5次执行：
kgllkal count 22 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
--//你可以发现在第4次执行在原始的sql语句上存在一个library cache lock。
--//查询该句柄地址0000000086f25658是否存在。
SYS@book> @ fchaz 0000000086f25658
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 0000000086F25628 1 2 KGLHD 560 recr 80 00 0000000086F25628 0000000086F25858
--//占用KSMCHSIZ=560.
SYS@book> @ sharepool/shp4z 0000000086f25658 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
parent handle address 0000000086F25658 0000000086F25658 select * from dept w 1 0 0 00 00 0 0 0 0 0 2854205335 112535 65535
--//仅仅存在父游标句柄。堆0，堆6不存在。
--//注意看sql语句的记录，仅仅记录select * from dept w20字节，奇怪，我前面的gdb显示完整的sql语句。
SYS@book> @ sharepool/shp4z 0000000086f25410 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
child handle address 0000000086ED4D98 0000000086F25410 select * from dept where deptno=:"SYS_B_ 1 0 0 0000000086ED4CE0 0000000086385770 4528 12144 3099 19771 19771 2650260714 aab8n62fzgj7a 115946 0
parent handle address 0000000086F25410 0000000086F25410 select * from dept where deptno=:"SYS_B_ 1 0 0 0000000086ED5160 00 4744 0 0 4744 4744 2650260714 aab8n62fzgj7a 115946 65535
3.继续：
--//sql语句换一个文字变量执行看看。
--//第1次执行：
kgllkal count 23 -- handle address: 0000000086f926b8, mode: 1 kglnaobj address:0x86f92860: "select * from dept where deptno=22"
kgllkal count 24 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
--//第2次执行：
kgllkal count 25 -- handle address: 0000000086f926b8, mode: 1 kglnaobj address:0x86f92860: "select * from dept where deptno=22"
--//第3次执行：
kgllkal count 26 -- handle address: 0000000086f926b8, mode: 1 kglnaobj address:0x86f92860: "select * from dept where deptno=22"
--//情况与前面类似。
--//注意看select * from dept where deptno=22语句的句柄地址0000000086f926b8与前面测试完成不同。
SYS@book> @ fchaz 0000000086f926b8
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 0000000086F92688 1 2 KGLHD 560 recr 80 00 0000000086F92688 0000000086F928B8
SYS@book> @ sharepool/shp4z 0000000086f926b8 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
parent handle address 0000000086F926B8 0000000086F926B8 select * from dept w 1 0 0 00 00 0 0 0 0 0 3494935040 31232 65535
--//hash值前面是2854205335，后面是3494935040，记录的sql语句不全仅仅显示20个字符(注：我写的脚本取前面40字节)。
--//KGLOBT03(sql_id 为null)。
4.如果文字变量的sql语句先执行的情况下情况如何呢。
--//打开新会话，执行如下sql语句多次，注意cursor_sharing=exact
SCOTT@book> select * from dept where deptno=23;
no rows selected
--//继续在原会话执行：
--//第1次执行：
kgllkal count 27 -- handle address: 0000000085a9e930, mode: 1 kglnaobj address:0x85a9ead8: "select * from dept where deptno=23"
kgllkal count 28 -- handle address: 0000000085a9e4b0, mode: 1 kglnaobj address:0x85a9e658: ""
--//第2次执行：
--//没有任何输出。
--//在这种情况下。select * from dept where deptno=23语句的父子游标都存在，保持完整。第2次执行就是软软解析。不存在任何kgllkal的函数调用情况。
SYS@book> @ sharepool/shp4z 0000000085a9e930 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
child handle address 0000000085A9E4B0 0000000085A9E930 select * from dept where deptno=23 1 0 0 0000000085A9E3F8 00000000852D2740 4528 12144 3067 19739 19739 257949538 67qaa5c7pzzv2 130914 0
parent handle address 0000000085A9E930 0000000085A9E930 select * from dept where deptno=23 1 0 0 0000000085A9E878 00 4720 0 0 4720 4720 257949538 67qaa5c7pzzv2 130914 65535
5.小结：
--//可以看出在cursor_sharing=force的情况如果原始sql语句的父子光标不存在，每次执行还是存在一个类似探测的过程，占用消耗小量的
--//共享池内存，占用父游标的游标的空间。如果原始sql语句的父子光标存在的情况，情况类似cursor_sharing=exact。
--//可以想象如果大量类似的非绑定变量sql语句执行，还是存在小量latch: shared pool争用，消耗小量共享池内存.
--//补充如果在21c下测试就看不见上面的情况。
--//补充验证正常情况下记录hash值.
--//cursor_sharing=exact.
SCOTT@book> select * from dept where deptno=22;
no rows selected
SCOTT@book> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
3494935040 dd1rm77850yh0 0 31232 d0507a00 2026-08-06 17:11:14 16777216
--//select * from dept where deptno=22; cursor_sharing=force
SYS@book> @ sharepool/shp4z 0000000086f926b8 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
parent handle address 0000000086F926B8 0000000086F926B8 select * from dept w 1 0 0 00 00 0 0 0 0 0 3494935040 31232 65535
6.测试使用代码:
# grep -v "^#" /home/oracle/sqllaji/gdb/lkpn11g.gdb
set pagination off
set print repeats 0
set print elements 0
set logging file /tmp/lkpn.log
set logging overwrite on
set logging on
set $lk = 0
set $pn = 0
set $lock = 0
break kgllkal
commands
silent
printf "kgllkal count %02d -- handle address: %016x, mode: %d ", ++$lk ,$rsi ,$rdx
echo kglnaobj address:
x/s $rsi+0x1a8
c
end
break kglpnal
commands
silent
printf "kglpnal count %02d -- handle address: %016x, mode: %d ", ++$pn ,$rsi ,$rdx
echo kglnaobj address:
x/s $rsi+0x1a8
c
end
$ cat sharepool/shp4z.sql
column N0_6_16 format 99999999
column fcura_addrlen new_value _fcura_addrlen format 999
column handle_type format a22
set termout off
select vsize(addr)*2 fcura_addrlen from x$dual;
set termout on
SELECT DECODE (kglhdadr,
kglhdpar, 'parent handle address',
'child handle address')
handle_type,
kglhdadr,
kglhdpar,
--//substr(kglnaobj,1,40) c40,
substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') ,1,40) c40,
KGLHDLMD,
KGLHDPMD,
kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03,
kglhdbid,
kglobt09
FROM x$kglob
WHERE
KGLHDPAR = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLHDADR = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLOBHD0 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD1 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD2 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD3 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD4 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD5 = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLOBHD6 = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLOBT03 = lower('&1')
or KGLNAHSH= &2;
--//从来没有测试11g下cursor_sharing=force下的library cache lock/library cache pin的情况，才发现与21c的情况存在一点点不同.
--//做一个记录：
1.环境：
SCOTT@book> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 11.2.0.4.0
BANNER : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL procedure successfully completed.
2.测试：
SCOTT@book> @ spid
==============================
SID : 84
SERIAL# : 3
PROCESS : 3141
SERVER : DEDICATED
SPID : 3142
PID : 37
P_SERIAL# : 1
KILL_COMMAND : alter system kill session '84,3' immediate;
PL/SQL procedure successfully completed.
SCOTT@book> @ curs force
alter session set cursor_sharing=force
Session altered.
$gdb -f -p 3142 -x /home/oracle/sqllaji/gdb/lkpn11g.gdb
Breakpoint 1 at 0x9829848
Breakpoint 2 at 0x9825d10
(gdb) c
Continuing.
--//第1次执行：
kgllkal count 01 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
kgllkal count 02 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kgllkal count 03 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
kgllkal count 04 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kglpnal count 01 -- handle address: 0000000086f25410, mode: 2 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kgllkal count 05 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
kgllkal count 06 -- handle address: 0000000086ed4f08, mode: 2 kglnaobj address:0x86ed50b0: "a52d14309df7c4ea$BUILD$"
kgllkal count 07 -- handle address: 0000000086ed4d98, mode: 1 kglnaobj address:0x86ed4f40: ""
kglpnal count 02 -- handle address: 0000000086ed4d98, mode: 3 kglnaobj address:0x86ed4f40: ""
kgllkal count 08 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
kgllkal count 09 -- handle address: 0000000086ed4aa8, mode: 1 kglnaobj address:0x86ed4c50: "c00a3f96ce940f7ba52d14309df7c4eaChild:0"
kglpnal count 03 -- handle address: 0000000086ed4aa8, mode: 3 kglnaobj address:0x86ed4c50: "c00a3f96ce940f7ba52d14309df7c4eaChild:0"
kgllkal count 10 -- handle address: 0000000086d22f80, mode: 1 kglnaobj address:0x86d23128: "SCOTT"
kgllkal count 11 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
kgllkal count 12 -- handle address: 0000000086fa4de8, mode: 2 kglnaobj address:0x86fa4f90: "DEPTSCOTT"
kglpnal count 04 -- handle address: 0000000086fa4de8, mode: 2 kglnaobj address:0x86fa4f90: "DEPTSCOTT"
kgllkal count 13 -- handle address: 000000008f972098, mode: 2 kglnaobj address:0x8f972240: "bookSYS"
--//第2次执行：
kgllkal count 14 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
kgllkal count 15 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kgllkal count 16 -- handle address: 0000000086ed4d98, mode: 1 kglnaobj address:0x86ed4f40: ""
kgllkal count 17 -- handle address: 0000000086fa4de8, mode: 2 kglnaobj address:0x86fa4f90: "DEPTSCOTT"
kglpnal count 05 -- handle address: 0000000086fa4de8, mode: 2 kglnaobj address:0x86fa4f90: "DEPTSCOTT"
--//第3次执行：
kgllkal count 18 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
kgllkal count 19 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
kgllkal count 20 -- handle address: 0000000086ed4d98, mode: 1 kglnaobj address:0x86ed4f40: ""
--//第4次执行：
kgllkal count 21 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
--//第5次执行：
kgllkal count 22 -- handle address: 0000000086f25658, mode: 1 kglnaobj address:0x86f25800: "select * from dept where deptno=21"
--//你可以发现在第4次执行在原始的sql语句上存在一个library cache lock。
--//查询该句柄地址0000000086f25658是否存在。
SYS@book> @ fchaz 0000000086f25658
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 0000000086F25628 1 2 KGLHD 560 recr 80 00 0000000086F25628 0000000086F25858
--//占用KSMCHSIZ=560.
SYS@book> @ sharepool/shp4z 0000000086f25658 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
parent handle address 0000000086F25658 0000000086F25658 select * from dept w 1 0 0 00 00 0 0 0 0 0 2854205335 112535 65535
--//仅仅存在父游标句柄。堆0，堆6不存在。
--//注意看sql语句的记录，仅仅记录select * from dept w20字节，奇怪，我前面的gdb显示完整的sql语句。
SYS@book> @ sharepool/shp4z 0000000086f25410 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
child handle address 0000000086ED4D98 0000000086F25410 select * from dept where deptno=:"SYS_B_ 1 0 0 0000000086ED4CE0 0000000086385770 4528 12144 3099 19771 19771 2650260714 aab8n62fzgj7a 115946 0
parent handle address 0000000086F25410 0000000086F25410 select * from dept where deptno=:"SYS_B_ 1 0 0 0000000086ED5160 00 4744 0 0 4744 4744 2650260714 aab8n62fzgj7a 115946 65535
3.继续：
--//sql语句换一个文字变量执行看看。
--//第1次执行：
kgllkal count 23 -- handle address: 0000000086f926b8, mode: 1 kglnaobj address:0x86f92860: "select * from dept where deptno=22"
kgllkal count 24 -- handle address: 0000000086f25410, mode: 1 kglnaobj address:0x86f255b8: "select * from dept where deptno=:\"SYS_B_0\""
--//第2次执行：
kgllkal count 25 -- handle address: 0000000086f926b8, mode: 1 kglnaobj address:0x86f92860: "select * from dept where deptno=22"
--//第3次执行：
kgllkal count 26 -- handle address: 0000000086f926b8, mode: 1 kglnaobj address:0x86f92860: "select * from dept where deptno=22"
--//情况与前面类似。
--//注意看select * from dept where deptno=22语句的句柄地址0000000086f926b8与前面测试完成不同。
SYS@book> @ fchaz 0000000086f926b8
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 0000000086F92688 1 2 KGLHD 560 recr 80 00 0000000086F92688 0000000086F928B8
SYS@book> @ sharepool/shp4z 0000000086f926b8 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
parent handle address 0000000086F926B8 0000000086F926B8 select * from dept w 1 0 0 00 00 0 0 0 0 0 3494935040 31232 65535
--//hash值前面是2854205335，后面是3494935040，记录的sql语句不全仅仅显示20个字符(注：我写的脚本取前面40字节)。
--//KGLOBT03(sql_id 为null)。
4.如果文字变量的sql语句先执行的情况下情况如何呢。
--//打开新会话，执行如下sql语句多次，注意cursor_sharing=exact
SCOTT@book> select * from dept where deptno=23;
no rows selected
--//继续在原会话执行：
--//第1次执行：
kgllkal count 27 -- handle address: 0000000085a9e930, mode: 1 kglnaobj address:0x85a9ead8: "select * from dept where deptno=23"
kgllkal count 28 -- handle address: 0000000085a9e4b0, mode: 1 kglnaobj address:0x85a9e658: ""
--//第2次执行：
--//没有任何输出。
--//在这种情况下。select * from dept where deptno=23语句的父子游标都存在，保持完整。第2次执行就是软软解析。不存在任何kgllkal的函数调用情况。
SYS@book> @ sharepool/shp4z 0000000085a9e930 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
child handle address 0000000085A9E4B0 0000000085A9E930 select * from dept where deptno=23 1 0 0 0000000085A9E3F8 00000000852D2740 4528 12144 3067 19739 19739 257949538 67qaa5c7pzzv2 130914 0
parent handle address 0000000085A9E930 0000000085A9E930 select * from dept where deptno=23 1 0 0 0000000085A9E878 00 4720 0 0 4720 4720 257949538 67qaa5c7pzzv2 130914 65535
5.小结：
--//可以看出在cursor_sharing=force的情况如果原始sql语句的父子光标不存在，每次执行还是存在一个类似探测的过程，占用消耗小量的
--//共享池内存，占用父游标的游标的空间。如果原始sql语句的父子光标存在的情况，情况类似cursor_sharing=exact。
--//可以想象如果大量类似的非绑定变量sql语句执行，还是存在小量latch: shared pool争用，消耗小量共享池内存.
--//补充如果在21c下测试就看不见上面的情况。
--//补充验证正常情况下记录hash值.
--//cursor_sharing=exact.
SCOTT@book> select * from dept where deptno=22;
no rows selected
SCOTT@book> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
3494935040 dd1rm77850yh0 0 31232 d0507a00 2026-08-06 17:11:14 16777216
--//select * from dept where deptno=22; cursor_sharing=force
SYS@book> @ sharepool/shp4z 0000000086f926b8 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
parent handle address 0000000086F926B8 0000000086F926B8 select * from dept w 1 0 0 00 00 0 0 0 0 0 3494935040 31232 65535
6.测试使用代码:
# grep -v "^#" /home/oracle/sqllaji/gdb/lkpn11g.gdb
set pagination off
set print repeats 0
set print elements 0
set logging file /tmp/lkpn.log
set logging overwrite on
set logging on
set $lk = 0
set $pn = 0
set $lock = 0
break kgllkal
commands
silent
printf "kgllkal count %02d -- handle address: %016x, mode: %d ", ++$lk ,$rsi ,$rdx
echo kglnaobj address:
x/s $rsi+0x1a8
c
end
break kglpnal
commands
silent
printf "kglpnal count %02d -- handle address: %016x, mode: %d ", ++$pn ,$rsi ,$rdx
echo kglnaobj address:
x/s $rsi+0x1a8
c
end
$ cat sharepool/shp4z.sql
column N0_6_16 format 99999999
column fcura_addrlen new_value _fcura_addrlen format 999
column handle_type format a22
set termout off
select vsize(addr)*2 fcura_addrlen from x$dual;
set termout on
SELECT DECODE (kglhdadr,
kglhdpar, 'parent handle address',
'child handle address')
handle_type,
kglhdadr,
kglhdpar,
--//substr(kglnaobj,1,40) c40,
substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') ,1,40) c40,
KGLHDLMD,
KGLHDPMD,
kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03,
kglhdbid,
kglobt09
FROM x$kglob
WHERE
KGLHDPAR = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLHDADR = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLOBHD0 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD1 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD2 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD3 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD4 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD5 = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLOBHD6 = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLOBT03 = lower('&1')
or KGLNAHSH= &2;
浙公网安备 33010602011771号