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

来源：这里教程网时间：2026-03-03 11:56:01 作者：

[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