分享程序员开发的那些事...
更新时间：2022-09-10 16:15:28
[20151223]关于共享池4x-SQL内存结构父子游标.txt
--重复测试：http://blog.itpub.net/267265/viewspace-1436541/
--这个主要和recr和freeabl类似.
--1.节约内存
--2.减少检索链表的时间.
--3.oracle的算法规定,sql语句必须至少是一父一子的情况.很多情况下都是一父多子.也就是说,每个游标,oracle都会为它设置个父游标
-- 如果有sql文本相同,但无法共享执行计划的情况出现,那就会出现一父多子的情况.
--注意除了sql对象,共享池中其它类型的对象都没有父子游标的概念.
--自己按照的介绍,重复测试一遍,加强理解，以前做个1次10g，今天补充11g的。
1.测试环境:
SCOTT@book> @ &r/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=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--sql_id=4xamnunv51w9j
2.--父游标句柄:
$ cat shp4.sql
SELECT DECODE (kglhdadr,
kglhdpar, '父游标句柄地址',
'子游标句柄地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1';
SYS@book> @ &r/sharepool/shp4.sql 4xamnunv51w9j
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 0000000061B19060 0000000062B4F770 4488 12144 3067 19699 19699 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--可以发现KGLHDADR=0000000061B19120是父游标句柄地址,直接使用它查询x$ksmsp的ksmchptr是不行的.存在一个偏移量
--正常是偏移0x30(48字节)是父游标句柄开始的chunk地址(0000000061B190F0):
SYS@book> select * from x$ksmsp where ksmchptr='0000000061B190F0';
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F5BF48ED4D0 16220 1 1 2 KGLHD 0000000061B190F0 560 recr 80 00
select * from x$ksmsp where ksmchptr in (
SELECT x
FROM (SELECT a.ksmchptr, lag (a.ksmchptr, 1) OVER (ORDER BY a.ksmchptr) x
FROM x$ksmsp a )
WHERE '0000000061B19120' between x and ksmchptr);
--或者
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ;
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F5BF461D840 16274 1 1 2 KGLHD 0000000061B190F0 560 recr 80 00
--从父游标句柄里面可以发现执行sql的文本.
--另外可以发现父游标句柄的chunk类型是recr.大小560字节.
3.SQL的chunk:父游标堆0:
--父游标句柄地址 KGLOBHD0='000000007C04C190'
--注意父游标句柄地址那行:KGLOBHD0 = 000000007C04C190 就是父游标堆0描述符(DS)地址.
new 1: select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar='000000007C04C190'
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR C60
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ------------------------------------------------------------
00007F5BF497B028 6531 1 1 3 KGLH0^3650f131 0000000062B4F000 4096 recr 4095 000000007C04C190 Typ=1 Len=14: 4b,47,4c,48,30,5e,33,36,35,30,66,31,33,31
--可以发现父游标堆0的chunk有1个，类型是recr类型，大小4096.
4.SQL的chunk:父游标堆0的DS:
--再来看看父游标堆0描述符(DS)地址 000000007C04C190
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ;
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F5BF49F8610 14685 1 1 2 KGLDA 000000007C04C128 240 freeabl 0 00
--父游标堆0的DS. chunk是freeable类型.大小240.
5.SQL的chunk:子游标句柄:
SYS@book> @ &r/sharepool/shp4.sql 4xamnunv51w9j
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 0000000061B19060 0000000062B4F770 4488 12144 3067 19699 19699 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--子游标句柄地址 KGLHDADR=000000007C09AA80
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C09AA80', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F5BF49DAA08 14144 1 1 2 KGLHD 000000007C09AA50 368 recr 80 00
--偏移0x30(48字节),猜测还是正确的。
--另外可以发现子游标句柄的chunk类型是recr.大小368字节.
6.SQL的chunk:子游标堆0:
SYS@book> @ &r/sharepool/shp4.sql 4xamnunv51w9j
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 0000000061B19060 0000000062B4F770 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--注意子游标句柄地址那行:
KGLOBHD0=0000000061B19060,就是子游标堆0描述符(DS)地址.
KGLOBHD6=0000000062B4F770,就是子游标堆6描述符(DS)地址.
new 1: select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar='0000000061B19060'
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR C60
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ------------------------------------------------------------
00007F5BF49651A8 6625 1 1 3 KGLH0^3650f131 0000000062B4E000 4096 recr 4095 0000000061B19060 Typ=1 Len=14: 4b,47,4c,48,30,5e,33,36,35,30,66,31,33,31
--可以发现子游标堆0的chunk有1个，1个是recr类型，大小4096.
7.SQL的chunk:子游标堆0的DS:
SYS@book> @ &r/sharepool/shp4.sql 4xamnunv51w9j
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 0000000061B19060 0000000062B4F770 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--注意子游标句柄地址 KGLOBHD0＝0000000061B19060
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000061B19060', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F5BF4A33438 16536 1 1 2 KGLDA 0000000061B18FF8 248 freeabl 0 00
--可以发现子游标堆0的堆描述符在KSMCHPTR=0000000061B18FF8的chunk。
--子游标堆0的DS. chunk是freeable类型.大小248.
8.SQL的chunk:子游标堆6:
SYS@book> @ &r/sharepool/shp4.sql 4xamnunv51w9j
old 16: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1'
new 16: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 0000000061B19060 0000000062B4F770 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--注意子游标句柄地址 堆6的DS地址：KGLOBHD6=0000000062B4F770.
new 1: select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar='0000000062B4F770'
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR C60
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ------------------------------------------------------------
00007F5BF49A5FB0 3487 1 1 4 SQLA^3650f131 00000000623956C8 4096 recr 4095 0000000062B4F770 Typ=1 Len=13: 53,51,4c,41,5e,33,36,35,30,66,31,33,31
00007F5BF49A5F58 3488 1 1 4 SQLA^3650f131 00000000623946C8 4096 freeabl 0 0000000062B4F770 Typ=1 Len=13: 53,51,4c,41,5e,33,36,35,30,66,31,33,31
00007F5BF49A7EE8 3489 1 1 4 SQLA^3650f131 00000000623936C8 4096 freeabl 0 0000000062B4F770 Typ=1 Len=13: 53,51,4c,41,5e,33,36,35,30,66,31,33,31
--可以发现子游标堆6的chunk有3个，1个是recr类型，2个freeabl类型，大小4096.
--可以发现执行计划在堆6中,类型freeabl。
9.SQL的chunk:子游标堆6的DS:
SYS@book> @ &r/sharepool/shp4.sql 4xamnunv51w9j
old 16: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1'
new 16: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 0000000061B19060 0000000062B4F770 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--注意子游标句柄地址 堆6的DS地址：KGLOBHD6=0000000062B4F770.
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000062B4F770', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F5BF4956D18 6898 1 1 3 KGLH0^3650f131 0000000062B4F000 4096 recr 4095 000000007C04C190
--注意父游标句柄地址那行:KGLOBHD0 = 000000007C04C190 就是父游标堆0描述符(DS)地址.
--可以发现子游标堆6描述符(DS)地址在 父游标的堆0中。
10.做一个总结画图不是很方便，列出来：
父游标句柄的chunk类型是recr.大小560字节.
父游标堆0的chunk有1个recr类型,大小4096.
父游标堆0的DS. chunk是freeable类型.大小240.
--父游标 需要3个chunk，2个类型recr，1个类型freeabl。
子游标句柄的chunk类型是recr.大小368字节.
子游标堆0的chunk有1个recr类型，大小4096.
子游标堆0的DS. chunk是freeable类型.大小248.
子游标堆6的chunk有3个，1个是recr类型，2个freeabl类型，大小4096.
子游标堆6的DS 在父游标的堆0中(不做计算)。类型freeabl。
--子游标 需要6个chunk，3个类型recr，3个类型freeabl。
--总共9个chunk。5个类型recr，4个类型freeabl。这个测试与vaga的测试一致。
11.占用共享池的大小：
SYS@book> select SHARABLE_MEM from v$sql where sql_id='4xamnunv51w9j';
SHARABLE_MEM
------------
19739
SYS@book> select SHARABLE_MEM from v$sqlarea where sql_id='4xamnunv51w9j';
SHARABLE_MEM
------------
19739
SYS@book> @&r/sharepool/shp4 4xamnunv51w9j
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 0000000061B19060 0000000062B4F770 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--对比可以发现与查询x$kglob的子游标句柄地址那行KGLOBHS0+KGLOBHS6+KGLOBT16一致。也就是v$sql视图占用SHARABLE_MEM内存不计算父游标。
--按照上面的计算 子游标 368+4096+248+3*4096=17000
--可以发现查询的基表是 x$kglcursor_child,而SHARABLE_MEM 对应的是kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16。
--因为其它的值是0,所以两者是相等的。
--可以发现这样1条sql语句需要占用16K上下，如果在oltp系统没有使用绑定，消耗共享内存很大的，而且导致共享内存出现大量碎片，
--管理带来困难，非常容易触发ora-4031错误。
12.刷新共享池看看:
SYS@book> @&r/sharepool/shp4 4xamnunv51w9j
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 0000000061B19060 0000000062B4F770 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @&r/sharepool/shp4 4xamnunv51w9j
old 16: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1'
new 16: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 00 00 0 0 3067 3067 3067 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--可以发现父游标占用内存没有回收，仅仅回收了大部分子游标占用内存。
--注意看子游标句柄地址 那行，KGLOBHD0 KGLOBHD6 =0 ,也就是清除了子游标的堆0与堆6. 子游标句柄没有清除。
--再次执行一次查询：
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SYS@book> @&r/sharepool/shp4 4xamnunv51w9j
old 16: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1'
new 16: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 00000000850A5068 0000000062B4F770 4488 12144 3067 19699 19699 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
13.继续如果再产生一个子光标呢？
SCOTT@book> alter session set optimizer_index_caching =10;
Session altered.
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SYS@book> @&r/sharepool/shp4 4xamnunv51w9j
old 16: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1'
new 16: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 00000000850A5068 0000000062B4F770 4488 12144 4347 20979 20979 911274289 4xamnunv51w9j
子游标句柄地址 000000007C1BC238 0000000061B19120 select * from dept where deptno=10 000000007C376CC8 0000000062B4FD38 4504 12144 4347 20995 20995 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--可以发现如果语句不能共享，再产生新子光标。
--并且每个子光标占用的内存增加了，不知道为什么？KGLOBT16部分增加了。
--注意增加的一行 子游标句柄地址 堆6的DS地址：KGLOBHD6=0000000062B4FD38
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000062B4FD38', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F5BF49826E0 7986 1 1 3 KGLH0^3650f131 0000000062B4F000 4096 recr 4095 000000007C04C190
--注意父游标句柄地址那行:KGLOBHD0 = 000000007C04C190 就是父游标堆0描述符(DS)地址.
--可以发现子游标堆6描述符(DS)地址在 父游标的堆0中。
14.退出运行的会话在刷新看看：
SYS@book> @&r/sharepool/shp4 4xamnunv51w9j
old 16: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1'
new 16: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C09AA80 0000000061B19120 select * from dept where deptno=10 00000000850A5068 0000000062B4F770 4488 12144 4347 20979 20979 911274289 4xamnunv51w9j
子游标句柄地址 000000007C1BC238 0000000061B19120 select * from dept where deptno=10 000000007C376CC8 0000000062B4FD38 4504 12144 4347 20995 20995 911274289 4xamnunv51w9j
父游标句柄地址 0000000061B19120 0000000061B19120 select * from dept where deptno=10 000000007C04C190 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
SYS@book> alter system flush shared_pool ;
System altered.
SYS@book> @&r/sharepool/shp4 4xamnunv51w9j
old 16: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1'
new 16: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j'
no rows selected
--可以发现仅仅在运行该语句的会话退出后，再刷新共享池，该语句在共享池占用的空间才消失。所以有时候刷新并不能有效的回收共享
--池内存,要临时解决ora-4031错误，要kill或者退出一组相似的应用，再刷新效果才比较好。
--研究这东西太累！！