[20160811]dbms_shared_pool清除子光标.txt

[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;

时间: 2024-10-11 15:05:13

[20160811]dbms_shared_pool清除子光标.txt的相关文章

[20160215]超长sql语句与父子光标.txt

[20160215]超长sql语句与父子光标.txt --看<oracle内核技术揭秘>提到sql语句不会进入保留池, 要进入保留池,chunk的大小必须大于_shared_pool_reserved_min_alloc. --而实际上许多sql语句一般最大4096字节.而且这些内存分配的原则是首先从共享池分配,不行并且大于 --shared_pool_reserved_min_alloc才会从保留池申请. --作者视乎忘记一种特殊情况就是超长sql语句,这样父游标保存sql的语句chunk可

[20130104]oracle能有多少子光标.txt

[20121019]oracle能有多少子光标.txt 原链接:http://www.antognini.ch/2012/10/how-many-children-can-a-parent-cursor-have-1000000/ 看看oracle可能有多少子光标,也就是最大是多少?重复原作者的测试看看. 我的测试环境: SQL> select * from v$version where rownum BANNER ---------------------------------------

Dart云平台-DartPad

如果你想体验Dart语言,但又不想在你的电脑上安装Dart开发环境,那你可以使用Google提供的Dart云编译服务--DartPad DartPad是一个自由.开放的源码服务,帮助开发人员学习Dart语言,进入DartPad的源代码会被发送到谷歌云计算平台上运行,服务器会将源代码进行编译并处理成JavaScript返回给浏览器,编译产生的错误和警告也会返回 DartPad的地址:https://dartpad.dartlang.org/ 因为是Google提供的服务,所以你需要翻墙才能打开该网

Dart网络编程-备忘录2.0

这个项目是根据之前的备忘录升级的 http://blog.csdn.net/hekaiyou/article/details/46834057 先翻墙,再打开WebStorm,然后新建web项目todo_with_delete 创建成功后先试试运行,成功后再修改代码,首先修改todo_with_delete.html <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <titl

Android自定义控件实战——下拉刷新控件终结者:PullToRefreshLayout

转载请声明出处http://blog.csdn.net/zhongkejingwang/article/details/38340701            说到下拉刷新控件,网上版本有很多,很多软件也都有下拉刷新功能.有一个叫XListView的,我看别人用过,没看过是咋实现的,看这名字估计是继承自ListView修改的,不过效果看起来挺丑的,也没什么扩展性,太单调了.看了QQ2014的列表下拉刷新,发现挺好看的,我喜欢,贴一下图看一下qq的下拉刷新效果:                 

IE BUG相关文章集合

Haslayout IE Haslayout 详解 haslayout "HasLayout" Overview 你了解IE的haslayout(拥有布局)吗? 详说 IE hasLayout Block formatting contexts Block formatting Block Formatting Contexts(块级格式化上下文) 详说 Block Formatting Contexts (块级格式化上下文) IE bug The CSS Box Model css盒

第十七节 HTML样式

一个网页页面或一个站点中,会有不少地方要用到相同的几个文本格式.每次重复这几个格式会很麻烦,于是把这些格式做成一个html样式,每次只需使用一次这个样式即可. 点击Laucher面板上左起第三个图标,或点击菜单栏的Window/HTML Styles,也可直接用快捷键Ctrl+F7,会弹出html样式面板.(html样式面板) 面板上已有一些预设的html样式.要新增样式,点击面板右下角的 图标,会弹出一个对话框. (新建html样式对话框) 对话框上的部分参数已被Dreamweaver设置好,

创建用于ASP.NET的分页控件

asp.net|创建|分页|控件 从程序员的角度来看,Microsoft SQL Server? 查询的最大缺陷之一就是返回的行数通常比应用程序的用户界面实际可以容纳的行数要多得多.这种尴尬情形经常将开发人员陷于困境.开发人员是应该创建一个非常长的页面,让用户花时间去滚动浏览,还是应该通过设置一个手动分页机制来更好地解决这个问题? 哪种解决方案更好,在很大程度上取决于要检索的数据的特性.由多个项目(如搜索结果)组成的较长列表,最好通过各页大小相等.每页相对较短的多个页面显示.由单个项目(如文章的

庖丁解牛Asp.net3.5控件和组件开发技术系列—页面状态机制(四)

6.6 清除页面状态 在控件开发时,有时候需要清除子控件的页面状态,比如在创建子控件时,以下是一个在数据绑定时清除视图并创建子控件的应用场景: /// <summary> /// 获得本书更多内容,请看: /// http://blog.csdn.net/ChengKing/archive/2008/08/18/2792440.aspx /// </summary> public override void DataBind() { base.OnDataBinding(Event