1224关于共享池4SQL内存结构父子游标补充

[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的书中提到，自己看书不认真，更正一下。