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