[20180822]session_cached_cursors与子游标堆0.txt

--//前几天测试刷新共享池与父子游标的问题,

--//链接: http://blog.itpub.net/267265/viewspace-2200066/=>[20180813]刷新共享池与父子游标.txt

--//我测试如果语句被回话缓存时,刷新共享池后,

--//父子游标,父游标堆0,子游标都没有清除.并且KGLHDLMD=1

--//子游标堆0,子游标堆6会被清除.

--//晚上看<oracle内核技术揭密>时,提到对于缓存的游标,子游标堆0的内存也不会被覆盖.进程在扫描LRU寻找可覆盖的chunk时,如果发

--//现子游标堆0,会检查它对应的子游标句柄上是否有1号Library cache lock.如果有,会将其从LRU中去掉,而子游标堆6就没有这种"待

--//遇".也就是这样方式父子游标,父游标堆0,子游标,子游标堆0都没有清除,仅仅子游标堆6会被清除.

--//与刷新共享池操作有一点点不同,差别在于子游标堆0是否被清除覆盖,我重复验证看看:

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

SCOTT@book> show parameter session_cached_cursors

NAME                   TYPE    VALUE

---------------------- ------- -----

session_cached_cursors integer 50

SCOTT@book> show parameter open_cursors

NAME         TYPE    VALUE

------------ ------- -----

open_cursors integer 300

SCOTT@book> alter system set open_cursors=50000 scope=memory;

System altered.

--//注意这个参数设置后要退出才会生效!!

2.测试:

--//session 1:

select * from dept where deptno=10;

select * from dept where deptno=10;

select * from dept where deptno=10;

select * from dept where deptno=10;

--//确定sql_id=4xamnunv51w9j,可以查询v$sql视图确定.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0

TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09

-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------

子游标句柄地址 000000007D4B8000 000000007D4B8390 select * from dept where deptno=10                0          0          0 000000007D07E040 000000007D07E838       4528      12144       3067     19739      19739  911274289 4xamnunv51w9j          0

父游标句柄地址 000000007D4B8390 000000007D4B8390 select * from dept where deptno=10                0          0          0 000000007D0D6318 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

--//建立测试脚本,仅仅分析sql语句,不执行.这样利用前面设定open_cursor参数,消耗共享池内存.

create table a1( id1 number,id2 number);

$ cat ac.sql

declare

msql varchar2(500);

mcur number;

mstat number;

begin

    for i in 1 .. 49000 loop

        mcur := dbms_sql.open_cursor;

        msql := 'select id1 from a1 where id2='||to_char(i);

        dbms_sql.parse(mcur,msql,dbms_sql.native);

--        mstat := dbms_sql.execute(mcur);

    end loop;

end;

/

3.执行测试脚本:

SCOTT@book> @ ac.sql

--//等,报错!!

declare

*

ERROR at line 1:

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","SQLA","tmp")

ORA-06512: at "SYS.DBMS_SQL", line 1199

ORA-06512: at line 9

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0

TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09

-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------

子游标句柄地址 000000007D4B8000 000000007D4B8390 select * from dept where deptno=10                1          0          0 000000007D07E040 00                     4528          0       3067      7595       7595  911274289 4xamnunv51w9j          0

父游标句柄地址 000000007D4B8390 000000007D4B8390 select * from dept where deptno=10                1          0          0 000000007D0D6318 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

--//确实,子游标堆0没有清除.不过有点奇怪的是刷新共享池可以清除子游标堆0,不理解.

SYS@book> alter system flush shared_pool;

System altered.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0

TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09

-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------

子游标句柄地址 000000007D4B8000 000000007D4B8390 select * from dept where deptno=10                1          0          1 00               00                        0          0       3067      3067       3067  911274289 4xamnunv51w9j          0

父游标句柄地址 000000007D4B8390 000000007D4B8390 select * from dept where deptno=10                1          0          1 000000007D0D6318 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535