[20180813]刷新共用池與父子游標.txt--//測試刷新共用池與父子游標含有那些信息保存在共用池.--//自己最近遇到的問題,感覺自己以前理解有點亂,測試看看.1.環境SCOTT@book> @ ver1PORT_STRING VERSION BANNER x86_64/Linux 2.4. ...
[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;
