[20151224]关于共享池4x-SQL内存结构父子游标 (补充).txt
--昨天的测试存在一点小问题，补充说明一下：
1.环境：
SYS@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
--session 1:
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--sql_id=4xamnunv51w9j，这个可以查询v$sql获得。
--session 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 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
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C21A3F8 000000007C21A878 select * from dept where deptno=10 000000007C29D6B0 0000000062B50770 4488 12144 3067 19699 19699 911274289 4xamnunv51w9j
父游标句柄地址 000000007C21A878 000000007C21A878 select * from dept where deptno=10 000000007C21A7C0 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
2.前面我的测试提到如果这个时候执行刷新共享池不会导致这条语句从共享池退出。
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
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- ---------- -------------
子游标句柄地址 000000007C21A3F8 000000007C21A878 select * from dept where deptno=10 00 00 0 0 3067 3067 3067 911274289 4xamnunv51w9j
父游标句柄地址 000000007C21A878 000000007C21A878 select * from dept where deptno=10 000000007C21A7C0 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j
--子游标句柄 的KGLOBHD0 ，KGLOBHD6 但是父游标句柄与子游标句柄不会清楚。
--实际上我一直认为如果session 1不退出,这条语句不会从共享池退出，实际上存在一点点小错误。如果回到会话1执行其他语句，在回
--到session 2再刷新共享池，就可以刷新将这条语句清除出去。
--session 1:
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-24 10:19:29
--session 2:
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
--可以发现清除掉了，实际上这种情况在vage的书中提到，自己看书不认真，更正一下。
