[20160811]dbms_shared_pool清除子光标.txt
--工作需要,看了一下使用dbms_shared_pool包的purge清除子光标.顺便做一些细节测试看看:
1.环境与说明:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
--我一般使用的脚本如下,原始的链接找不到了
$ cat flush_sql.sql
DECLARE
name varchar2(100);
version varchar2(3);
BEGIN
select regexp_replace(version,'\..*') into version from v$instance;
if version = '10' then
execute immediate
q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport
end if;
select address||','||hash_value into name from v$sqlarea where sql_id like '&1';
dbms_shared_pool.purge(name,'C',&2);
END;
/
SYS@test> @ desc_proc sys dbms_shared_pool purge
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED
---------- -------------------- ----------- ---------- -------------------- -------------------- --------- -------------------- ----------
SYS DBMS_SHARED_POOL PURGE 1 NAME VARCHAR2 IN VARCHAR2 N
2 FLAG CHAR IN CHAR Y
3 HEAPS NUMBER IN NUMBER Y
1 SCHEMA VARCHAR2 IN VARCHAR2 N
2 OBJNAME VARCHAR2 IN VARCHAR2 N
3 NAMESPACE NUMBER IN NUMBER N
4 HEAPS NUMBER IN NUMBER N
1 HASH VARCHAR2 IN VARCHAR2 N
2 NAMESPACE NUMBER IN NUMBER N
3 HEAPS NUMBER IN NUMBER N
10 rows selected.
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_shared_pool.htm#sthref7227
--我感兴趣的是heaps参数:
heaps
Heaps to be purged. For example, if heap 0 and heap 6 are to be purged:
1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object
would be purged
--按照介绍: heap 0 = 1,heap6=2^6=64,如果heap 0清除了,整个对象也清除了.因为heap6是下面一个子堆.
2.测试:
SCOTT@test01p> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--sql_id=4xamnunv51w9j,过程略.保险起见,执行多次以上语句.
SYS@test> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF2894D568 000007FF57D67660 select * from dept where deptno=10 000007FF2894D4B0 000007FF28936528 4032 12144 3115 19291 19291 911274289 4xamnunv51w9j 0
父游标句柄地址 000007FF57D67660 000007FF57D67660 select * from dept where deptno=10 000007FF28C12090 00 4072 0 0 4072 4072 911274289 4xamnunv51w9j 65535
--为了避免以上语句光标锁定,退出看看是否可以清除.
3.使用包dbms_shared_pool.purge:
SYS@test> @ flush_sql 4xamnunv51w9j 64
PL/SQL procedure successfully completed.
SYS@test> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF2894D568 000007FF57D67660 select * from dept where deptno=10 000007FF2894D4B0 00 4072 0 3115 7187 7187 911274289 4xamnunv51w9j 0
父游标句柄地址 000007FF57D67660 000007FF57D67660 select * from dept where deptno=10 000007FF28C12090 00 4072 0 0 4072 4072 911274289 4xamnunv51w9j 65535
--确实子游标句柄地址的KGLOBHD6=00.
SYS@test> @ flush_sql 4xamnunv51w9j 1
PL/SQL procedure successfully completed.
SYS@test> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF2894D568 000007FF57D67660 select * from dept where deptno=10 00 00 0 0 3115 3115 3115 911274289 4xamnunv51w9j 0
父游标句柄地址 000007FF57D67660 000007FF57D67660 select * from dept where deptno=10 000007FF28C12090 00 4072 0 0 4072 4072 911274289 4xamnunv51w9j 65535
--确实子游标句柄地址的KGLOBHD0=00.
SYS@test> @ flush_sql 4xamnunv51w9j 1
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 12
--也就是这个时候v$sqlarea视图已经无法查询到对应记录.
SYS@test> select * from v$sql where sql_id='4xamnunv51w9j';
no rows selected
--v$sql视图也查询不到.
4.重新执行在测试看看:
SYS@test> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF2894D568 000007FF57D67660 select * from dept where deptno=10 000007FF57663820 000007FF28936528 4072 12144 3115 19331 19331 911274289 4xamnunv51w9j 0
父游标句柄地址 000007FF57D67660 000007FF57D67660 select * from dept where deptno=10 000007FF28C12090 00 4072 0 0 4072 4072 911274289 4xamnunv51w9j 65535
SYS@test> @ flush_sql 4xamnunv51w9j 1
PL/SQL procedure successfully completed.
SYS@test> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF2894D568 000007FF57D67660 select * from dept where deptno=10 00 00 0 0 3115 3115 3115 911274289 4xamnunv51w9j 0
父游标句柄地址 000007FF57D67660 000007FF57D67660 select * from dept where deptno=10 000007FF28C12090 00 4072 0 0 4072 4072 911274289 4xamnunv51w9j 65535
--可以发现heap0 清除了,heap 6也一起清除.
--另外从以上测试可以发现父游标句柄地址不会清除的.
5.测试打开光标的情况下是否可以清除:
--打开session 1:
SCOTT@test01p> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--不退出,也不执行其他语句,安装vage介绍,这样光标是没有关闭,从11g开始要执行下一条语句才会关闭.
----打开session 2:
SYS@test> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF2894D568 000007FF57D67660 select * from dept where deptno=10 000007FF57663820 000007FF28936528 4032 12144 3115 19291 19291 911274289 4xamnunv51w9j 0
父游标句柄地址 000007FF57D67660 000007FF57D67660 select * from dept where deptno=10 000007FF28C12090 00 4072 0 0 4072 4072 911274289 4xamnunv51w9j 65535
SYS@test> @ flush_sql 4xamnunv51w9j 65
PL/SQL procedure successfully completed.
SYS@test> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF2894D568 000007FF57D67660 select * from dept where deptno=10 00 00 0 0 3115 3115 3115 911274289 4xamnunv51w9j 0
父游标句柄地址 000007FF57D67660 000007FF57D67660 select * from dept where deptno=10 000007FF28C12090 00 4072 0 0 4072 4072 911274289 4xamnunv51w9j 65535
--看来我理解错误!!^_^.仅仅清除了子光标.
--再次执行以上语句.
--打开session 1:
SCOTT@test01p> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--打开session 2:
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000007FF2894D568 000007FF57D67660 select * from dept where deptno=10 00 00 0 0 3115 3115 3115 911274289 4xamnunv51w9j 0
父游标句柄地址 000007FF57D67660 000007FF57D67660 select * from dept where deptno=10 000007FF28C12090 00 4072 0 0 4072 4072 911274289 4xamnunv51w9j 65535
--//依旧无法清除父游标.也就是正在执行完的语句(还没有其他语句执行),是无法清除父光标的.
--打开session 1,执行其他语句:
SCOTT@test01p> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--打开session 2:
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> @ sharepool/shp4 4xamnunv51w9j 0
no rows selected
--这样才彻底清除干净.
总结:
1.包dbms_shared_pool.purge仅仅能清除子光标的heap6,heap0,当然清除heap0 ,heap6 也一起清除.
2.如果在会话正在执行该语句的情况下没有其他语句执行的情况下,alter system flush shared_pool;仅仅能清除子光标,必须等下一次
执行别的语句,alter system flush shared_pool;才能彻底清除.
--附上sharepool/shp4.sql脚本:
$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
kglhdpar, '父游标句柄地址',
'子游标句柄地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
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;
