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

[20151229]关于共享池4x-SQL内存结构父子游标 (补充3).txt

-- 前几天的测试,还是有点问题,链接如下:
-- http://blog.itpub.net/267265/viewspace-1942280/
-- 继续做1点补充:

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

--session 1:
SCOTT@book> select * from dept where deptno=10;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--sql_id=4xamnunv51w9j，这个可以查询v$sql获得。

2.测试:
--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  N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 000000006318D1D0 0000000061B435C0 select * from dept where deptno=10       000000006653F0F8 00000000695D1770       4488      12144       3067    19699      19699  911274289 4xamnunv51w9j
父游标句柄地址 0000000061B435C0 0000000061B435C0 select * from dept where deptno=10       000000007C37E8D0 00                     4720          0          0     4720       4720  911274289 4xamnunv51w9j

3.前面我的测试提到如果这个时候执行刷新共享池不会导致这条语句从共享池退出。
SYS@test> alter system flush shared_pool;
System altered.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16  N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 000000006318D1D0 0000000061B435C0 select * from dept where deptno=10       00               00                        0          0       3067     3067       3067  911274289 4xamnunv51w9j
父游标句柄地址 0000000061B435C0 0000000061B435C0 select * from dept where deptno=10       000000007C37E8D0 00                     4720          0          0     4720       4720  911274289 4xamnunv51w9j

--子游标句柄 的KGLOBHD0 ，KGLOBHD6 不在. 但是父游标句柄与子游标句柄以及父游标堆0不会清除。

--实际上我一直认为如果session 1不退出,这条语句不会从共享池退出，实际上存在一点点小错误。如果回到会话1执行其他语句，在回
--到session 2再刷新共享池，就可以刷新将这条语句清除出去。

--session 1:
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:36:01

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

4.再回到session 1，执行3次。

--session 1:
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;

SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:39:04

SCOTT@book> @&r/spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        90       5157 13988       32          2 alter system kill session '90,5157' immediate;

--session 2:
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
SADDR                   SID USER_NAME  ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                           LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- ---------------------------------- ------------------- ----------- --------------------
0000000084EC6260         90 SCOTT      000000006307E710  911274289 4xamnunv51w9j select * from dept where deptno=10                                 DICTIONARY LOOKUP CU
                                                                                                                                                    RSOR CACHED
--CURSOR_TYPE类型变为"DICTIONARY LOOKUP CURSOR CACHED".

--这个时候刷新看看：

SYS@book> alter system flush shared_pool ;
System altered.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16  N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 000000008505B4D8 000000006307E710 select * from dept where deptno=10       00               00                        0          0       3067     3067       3067  911274289 4xamnunv51w9j
父游标句柄地址 000000006307E710 000000006307E710 select * from dept where deptno=10       000000006193DB68 00                     4720          0          0     4720       4720  911274289 4xamnunv51w9j

--子游标句柄 的KGLOBHD0 ，KGLOBHD6 不在. 但是父游标句柄与子游标句柄以及父游标堆0不会清除。
--换一句话讲如果执行的sql语句执行3次在一个会话中，CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'.

--回到session 1，再执行2次:

select * from dept where deptno=10;
select * from dept where deptno=10;

SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:46:05

--session 2:
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
no rows selected

--????没有记录。也就是CURSOR_TYPE="DICTIONARY LOOKUP CURSOR CACHED",刷新共享池会导致该语句不会被会话缓存。

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16  N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 000000008505B4D8 000000006307E710 select * from dept where deptno=10       0000000085053450 000000006114F6F8       4528      12144       3067    19739      19739  911274289 4xamnunv51w9j
父游标句柄地址 000000006307E710 000000006307E710 select * from dept where deptno=10       000000006193DB68 00                     4720          0          0     4720       4720  911274289 4xamnunv51w9j

--session 1:
SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:48:33

--session 2:
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
SADDR                   SID USER_NAME  ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                           LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- ---------------------------------- ------------------- ----------- --------------------
0000000084EC6260         90 SCOTT      000000006307E710  911274289 4xamnunv51w9j select * from dept where deptno=10                                 DICTIONARY LOOKUP CU
                                                                                                                                                    RSOR CACHED

--执行3次，CURSOR_TYPE="DICTIONARY LOOKUP CURSOR CACHED"

--session 1:
SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:49:18

--session 2:
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
SADDR                   SID USER_NAME  ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                           LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- ---------------------------------- ------------------- ----------- ---------------------
0000000084EC6260         90 SCOTT      000000006307E710  911274289 4xamnunv51w9j select * from dept where deptno=10                                 SESSION CURSOR CACHED

--执行4次，CURSOR_TYPE="SESSION CURSOR CACHED".

--这个时候再刷新看看：

SYS@book> alter system flush shared_pool ;
System altered.

SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
SADDR                   SID USER_NAME  ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                           LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- ---------------------------------- ------------------- ----------- ---------------------
0000000084EC6260         90 SCOTT      000000006307E710  911274289 4xamnunv51w9j select * from dept where deptno=10                                 SESSION CURSOR CACHED

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16  N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 000000008505B4D8 000000006307E710 select * from dept where deptno=10       00               00                        0          0       3067     3067       3067  911274289 4xamnunv51w9j
父游标句柄地址 000000006307E710 000000006307E710 select * from dept where deptno=10       000000006193DB68 00                     4720          0          0     4720       4720  911274289 4xamnunv51w9j

5.退出会话1：

SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
no rows selected

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16  N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 000000008505B4D8 000000006307E710 select * from dept where deptno=10       00               00                        0          0       3067     3067       3067  911274289 4xamnunv51w9j
父游标句柄地址 000000006307E710 000000006307E710 select * from dept where deptno=10       000000006193DB68 00                     4720          0          0     4720       4720  911274289 4xamnunv51w9j

SYS@book> alter system flush shared_pool ;
System altered.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
no rows selected

--也就是退出会话，在session cache cursor不存在了，再刷新共享池就可以将这条sql语句占用共享池的内存清除干净。

--总结：
1.实际上session 1执行第1次，在session 2 查看v$open_cursor视图，对应会话的CURSOR_TYPE='OPEN'.这个时候刷新共享池还会有部分内容占据共享池。(父子游标以及父游标堆0,父游标堆0的DS描述符).
2.这个时候session 1在执行别的语句，在session 2 查看v$open_cursor视图，对应会话才不存在，刷新共享池可以清除干净。
3.重新再来session 1执行3次后，在session 2 查看v$open_cursor视图，对应会话的CURSOR_TYPE='OPEN'.再执行别的语句，在session 2 查看v$open_cursor视图CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'。
4.这个时候刷新共享池还会有部分内容占据共享池。查看v$open_cursor视图，对应的sql语句的CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'。
5.但是这个时候session 1 相同语句，打开光标要重新记数，执行4次以上，CURSOR_TYPE="SESSION CURSOR CACHED".
6.这个时候无论如何刷新共享池还会有部分内容占据共享池，但是这个时候session 1 相同语句，打开光标还要重新记数。

--总之如果语句在执行完成没有其他执行，当前的光标是open状态，这个时候无法完全从共享池清除。
--如果CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'(执行3次) 或者CURSOR_TYPE='SESSION CURSOR CACHED'(执行4次),这个时候无法完全从共享池清除。但是重新打开的光标要重新记数。
--在v$open_cusor无法查询到，才能彻底的清除干净。所以按照vage的介绍，如果出现ora-4031,临时解决要清除kill一组相似的应用，才能临时解决这个问题。

--这个是我的测试结果，环境11.2.0.4，其他版本我不确定是这种情况。