[20180828]关于参数cursor_space_for_time(10g).txt
--//昨天测试session_cached_cursors不等于0的情况下,如果会话已经缓存了游标,这样即使其它语句消耗共享池,
--//父子游标,父游标堆0,子游标,子游标堆0都没有清除,仅仅子游标堆6会被清除.
--//今天补充测试cursor_space_for_time=true的情况.好像这个参数在10g之前还有效.因为11g以后使用metux来代替部分latch,
--//不在支持此参数.
1.环境:
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SYS@test> show parameter cursor_space_for_time
NAME TYPE VALUE
--------------------- ------- -------
cursor_space_for_time boolean FALSE
SYS@test> alter system set open_cursors=50000 scope=memory;
System altered.
--//建立测试脚本,仅仅分析sql语句,不执行.这样利用设定参数open_cursor=50000,消耗共享池内存.
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;
/
2.测试:
--//cursor_space_for_time=false
--//session 1:
SCOTT@test> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//确定sql_id=4xamnunv51w9j,可以查询v$sql视图确定.
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 0000000076B394C8 0000000076B396F0 select * from dept where deptno=10 1 0 0 0000000076B39408 00000000774C6FE8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父游标句柄地址 0000000076B396F0 0000000076B396F0 select * from dept where deptno=10 1 0 0 0000000076B39630 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//注意10.2.0.4版本也是一样,当前执行的sql语句KGLHDLMD=1.注意现在是cursor_space_for_time=FALSE的情况.
--//session 1:
SCOTT@test> select sysdate from dual;
SYSDATE
-------------------
2018-08-28 09:17:30
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 0000000076B394C8 0000000076B396F0 select * from dept where deptno=10 0 0 0 0000000076B39408 00000000774C6FE8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父游标句柄地址 0000000076B396F0 0000000076B396F0 select * from dept where deptno=10 0 0 0 0000000076B39630 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//KGLHDLMD=0.
--//补充测试:
--//session 2:
SYS@test> show parameter cursor_space_for_time
NAME TYPE VALUE
--------------------- ------- -------
cursor_space_for_time boolean FALSE
--//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;
select sysdate from dual;
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007672B290 000000007672B4B8 select * from dept where deptno=10 1 0 0 000000007672B1D0 00000000770A2190 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父游标句柄地址 000000007672B4B8 000000007672B4B8 select * from dept where deptno=10 1 0 0 000000007672B3F8 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//session 3:
SCOTT@test> @ ac.sql
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 576 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","sql area","ckydef : kkdlcky")
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 9
--//等,报ora-04031错误!!
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007672B290 000000007672B4B8 select * from dept where deptno=10 1 0 0 000000007672B1D0 00 3664 0 1803 5467 5467 911274289 4xamnunv51w9j 0
父游标句柄地址 000000007672B4B8 000000007672B4B8 select * from dept where deptno=10 1 0 0 000000007672B3F8 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//父子游标句柄地址的KGLHDLMD=1.
--//当会话缓存游标以后,在cursor_space_for_time=false的情况下.共享池内存不足时,并不能清除父子游标句柄地址,父游标堆0,子游标堆0.而子游标堆6的信息清除了.
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007672B290 000000007672B4B8 select * from dept where deptno=10 0 0 1 00 00 0 0 1803 1803 1803 911274289 4xamnunv51w9j 0
父游标句柄地址 000000007672B4B8 000000007672B4B8 select * from dept where deptno=10 1 0 1 000000007672B3F8 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//父子游标句柄地址,父游标堆0都没有清除.并且注意父游标句柄地址的KGLHDLMD=1.其他KGLHDIVC=1表示什么不是很清楚.
--//子游标堆0,子游标堆6清除.
--//感觉10g与11g下alter system flush shared_pool;存在不同,10g下,仅仅父游标句柄地址的KGLHDLMD=1.
--//而11g下,父子游标句柄地址的KGLHDLMD=1.
3.修改参数cursor_space_for_time看看:
SYS@test> alter system set cursor_space_for_time=true scope=spfile;
System altered.
--//修改参数重启数据库.略.
--//session 1:
SCOTT@test> show parameter cursor_space_for_time
NAME TYPE VALUE
--------------------- ------- -----
cursor_space_for_time boolean TRUE
SCOTT@test> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007773EC58 00000000777521F0 select * from dept where deptno=10 1 2 0 000000007773BE60 000000007773C1C8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父游标句柄地址 00000000777521F0 00000000777521F0 select * from dept where deptno=10 1 0 0 0000000077752130 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//对比前面的情况可以发现KGLHDLMD=1外,子游标句柄地址的KGLHDPMD=2.KGLHDPMD表示library cahce pin模式.
--//KGLHDLMD表示library cache lock模式. 1=null,2=共享模式 3=独占模式.
--//session 1:
SCOTT@test> select sysdate from dual;
SYSDATE
-------------------
2018-08-28 09:24:34
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007773EC58 00000000777521F0 select * from dept where deptno=10 0 0 0 000000007773BE60 000000007773C1C8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父游标句柄地址 00000000777521F0 00000000777521F0 select * from dept where deptno=10 0 0 0 0000000077752130 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//当前sql语句<>'4xamnunv51w9j'时,KGLHDLMD=0.
--//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;
select sysdate from dual;
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007773EC58 00000000777521F0 select * from dept where deptno=10 1 0 0 000000007773BE60 000000007773C1C8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父游标句柄地址 00000000777521F0 00000000777521F0 select * from dept where deptno=10 1 0 0 0000000077752130 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//KGLHDLMD=1.
4.执行测试脚本:
--//session 1:
SCOTT@test> alter system set open_cursors=50000 scope=memory;
System altered.
--//退出再进入,不然不会生效:
--//session 2:
SYS@test> alter system flush shared_pool;
System altered.
--//注:前面的测试我在session 1执行ac.sql前退出过1次(要使alter system set open_cursors=50000 scope=memory;生效),
--//导致sql_id='4xamnunv51w9j'游标已经退出回话缓存,虽然我又执行多次缓存该游标,但是不应该在该回话调用ac.sql.
--//这样测试有点问题.要缓存游标的这个回话不要退出.重来测试.
--//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;
select * from dept where deptno=10;
select sysdate from dual;
--//不退出.
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 0000000072C7CDD8 0000000071E6E388 select * from dept where deptno=10 1 0 0 0000000071E4A7C0 0000000071E65C80 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父游标句柄地址 0000000071E6E388 0000000071E6E388 select * from dept where deptno=10 1 0 0 0000000071E1C618 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//KGLHDLMD=0.
--//session 3:
SCOTT@test> @ ac.sql
--//等,报ora-04031错误!!
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 144 bytes of shared memory ("shared pool","select type#,blocks,extents,...","Typecheck","coldef: qcopCreateCol")
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 9
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 0000000072C7CDD8 0000000071E6E388 select * from dept where deptno=10 1 0 0 0000000071E4A7C0 00 3664 0 1803 5467 5467 911274289 4xamnunv51w9j 0
父游标句柄地址 0000000071E6E388 0000000071E6E388 select * from dept where deptno=10 1 0 0 0000000071E1C618 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//我感觉与会话缓存游标一样,父子游标的KGLHDLMD=1.
--//当会话缓存游标以后,在cursor_space_for_time=true的情况下.共享池内存不足时,并不能清除父子游标句柄地址,父游标堆0,子游标堆0.而子游标堆6的信息清除了.
--//刷新共享池看看.
--//session 2:
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 0000000072C7CDD8 0000000071E6E388 select * from dept where deptno=10 0 0 1 00 00 0 0 1803 1803 1803 911274289 4xamnunv51w9j 0
父游标句柄地址 0000000071E6E388 0000000071E6E388 select * from dept where deptno=10 1 0 1 0000000071E1C618 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//父子游标句柄地址,父游标堆0都没有清除.并且注意父游标句柄地址的KGLHDLMD=1.其他KGLHDIVC=1表示什么不是很清楚.
--//子游标堆0,子游标堆6清除.
--//感觉10g与11g下alter system flush shared_pool;存在不同,10g下,仅仅父游标句柄地址的KGLHDLMD=1.
--//而11g下,父子游标句柄地址的KGLHDLMD=1.
--//session 1退出后,session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 0000000072C7CDD8 0000000071E6E388 select * from dept where deptno=10 0 0 1 00 00 0 0 1803 1803 1803 911274289 4xamnunv51w9j 0
父游标句柄地址 0000000071E6E388 0000000071E6E388 select * from dept where deptno=10 0 0 1 0000000071E1C618 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
总结:
感觉设置cursor_space_for_time=true与session_cached_cursors差别不大,不做比较,总之无法彻底清除干净从共享池.
测试还是乱,
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2213257/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2213257/
扫一扫
141
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
