[20180813]刷新共享池与父子游标.txt

[20180813]刷新共享池与父子游标.txt --//测试刷新共享池与父子游标含有那些信息保存在共享池. --//自己最近遇到的问题,感觉自己以前理解有点乱,测试看看. 1.环境 SCOTT@book> @ 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 2.测试: --//session 1: select * from dept where deptno=10; --//确定sql_id=4xamnunv51w9j,可以查询v$sql视图确定. --//session 2: SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0 TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000000007D9134A0 000000007D7110E0 select * from dept where deptno=10                1          0 000000007D6F2250 000000007BFF1138       4488      12144       3067     19699      19699  911274289 4xamnunv51w9j          0 父游标句柄地址 000000007D7110E0 000000007D7110E0 select * from dept where deptno=10                1          0 000000007D9E7608 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535 --//全部父子光标,父堆0.子堆0,6都在.KGLHDLMD=1.当前session 1,执行完该条语句,11g下游标不会释放. SYS@book> alter system flush shared_pool; System altered. SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0 TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000000007D9134A0 000000007D7110E0 select * from dept where deptno=10                1          0 00               00                        0          0       3067      3067       3067  911274289 4xamnunv51w9j          0 父游标句柄地址 000000007D7110E0 000000007D7110E0 select * from dept where deptno=10                1          0 000000007D9E7608 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535 --//父子游标,父游标堆0,子游标都没有清除.并且KGLHDLMD=1 --//子游标堆0,子游标堆6清除. --//当前执行的语句,游标不会关闭,刷新共享池,并不能父子游标,父游标堆0,子游标. --//是否可以这么理解KGLHDLMD=1的情况下,不会清除全部信息. --//session 1: SCOTT@book> select sysdate from dual; SYSDATE ------------------- 2018-08-14 09:00:11 --//session 2: SYS@book> alter system flush shared_pool; System altered. SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0 no rows selected --//可以发现光标已经全部清除.因为session 1当前执行的是 select sysdate from dual;. --//sql_id=4xamnunv51w9j的游标已经关闭.这样刷新共享池,可以完全清除. --//忘记在刷新前看看游标的情况,补充测试3. 3.测试: --//session 1: select * from dept where deptno=10; select sysdate from dual; --//注意当前语句不是select * from dept where deptno=10;. --//session 2: SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0 TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000000007D8A5B98 000000007DB3C798 select * from dept where deptno=10                0          0 000000007D72DD88 000000007C9A8358       4520      12144       3067     19731      19731  911274289 4xamnunv51w9j          0 父游标句柄地址 000000007DB3C798 000000007DB3C798 select * from dept where deptno=10                0          0 000000007DAFF9F0 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535 --//全部父子光标,父堆0.子堆0,6都在.KGLHDLMD=0. SYS@book> alter system flush shared_pool; System altered. SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0 no rows selected 4.测试: --//测试会话缓存光标的情况. --//session 1: SCOTT@book> show parameter session_cached_cursors NAME                   TYPE    VALUE ---------------------- ------- ----- session_cached_cursors integer 50 select * from dept where deptno=10; select sysdate from dual; select * from dept where deptno=10; select sysdate from dual; select * from dept where deptno=10; select sysdate from dual; select * from dept where deptno=10; select sysdate from dual; --//执行3次以上,注意最后一条语句是select sysdate from dual; --//session 2: SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0 TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000000007D4BB278 000000007D4BB608 select * from dept where deptno=10                1          0 000000007D4BB1C0 000000007BA63988       4528      12144       3067     19739      19739  911274289 4xamnunv51w9j          0 父游标句柄地址 000000007D4BB608 000000007D4BB608 select * from dept where deptno=10                1          0 000000007D4BF948 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535 --//执行3次以后,KGLHDLMD=1. --//你可以在每次执行select sysdate from dual;查看sql_id=4xamnunv51w9j光标情况. --//仅仅第3次后KGLHDLMD=1. SYS@book> alter system flush shared_pool; System altered. SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0 TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000000007D4BB278 000000007D4BB608 select * from dept where deptno=10                1          0 00               00                        0          0       3067      3067       3067  911274289 4xamnunv51w9j          0 父游标句柄地址 000000007D4BB608 000000007D4BB608 select * from dept where deptno=10                1          0 000000007D4BF948 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535 --//父子游标,父游标堆0,子游标都没有清除.并且KGLHDLMD=1 --//子游标堆0,子游标堆6清除. --//当前执行的语句不是该条,但是当会话缓存游标以后,刷新共享池,并不能清除父子游标,父游标堆0,子游标. --//也就是刷新共享池无法完全清除会话缓存的光标. --//附上shp4.sql脚本: $ cat sharepool/shp4.sql column N0_6_16 format 99999999 SELECT DECODE (kglhdadr,                kglhdpar, '父游标句柄地址',                '子游标句柄地址')           text,        kglhdadr,        kglhdpar,        substr(kglnaobj,1,40) c40,            KGLHDLMD,            KGLHDPMD, --         kglhdivc,        kglobhd0,        kglobhd6,        kglobhs0,kglobhs6,kglobt16,        kglobhs0+kglobhs6+kglobt16 N0_6_16,            kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,            kglnahsh,            kglobt03 ,            kglobt09   FROM x$kglob  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;