[20201111]CURSOR_SPACE_FOR_TIME.txt

来源：这里教程网时间：2026-03-03 16:16:45 作者：

[20201111]CURSOR_SPACE_FOR_TIME.txt --//参数CURSOR_SPACE_FOR_TIME从11g开始已经过时，引入它的初衷是为了缓解child cursor上的与库缓存相关的Latch争用，11G开始使 --//用MUTEX替换各种与库缓存相关的Latch。 oracle的cursor是有生命周期的，每个session cursor在使用的过程中都至少会经历一次open,parse,bind,execute,fetch和close中的一个或者多个阶段。当一个目标sql所对应的session cursor的状态是execute时(即该sql正在执行)，oracle会把该SQL语句所对于的child cursor 给pin在库缓存(11g应该使用mutex)。在CURSOR_SPACE_FOR_TIME=FALSE的情况下,一旦执行sql语句完毕，对应的session cursor的状态已经不是execute状态，此时sql所对应的child cursor就可以不再pin在库缓存中，意味着如果共享池内存紧张，该SQL语句解析树和执行计划可以背换出共享池。如果设置CURSOR_SPACE_FOR_TIME=TRUE。每次session cursor在execute完成后，对应的child cursor上的library cache pin不会释放。下次执行时就不会重复pin操作，减少latch的争用。当然副作用就是对应的child cursor不会释放，导致共享内存紧张。简单测试看看 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 SYS@book> alter system set CURSOR_SPACE_FOR_TIME=true scope=spfile; System altered. --//重启数据库: SYS@book> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ORACLE instance started. Total System Global Area 643084288 bytes Fixed Size 2255872 bytes Variable Size 205521920 bytes Database Buffers 427819008 bytes Redo Buffers 7487488 bytes Database mounted. Database opened. select p.name,p.value from v$parameter p, v$spparameter s where s.name=p.name and p.isdeprecated='TRUE' and s.isspecified='TRUE'; NAME VALUE ---------------------------------------- ------------------------------ cursor_space_for_time TRUE --//很明显11g已经废除了该参数。在10g下测试看看： 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> alter system set cursor_space_for_time=true scope=spfile; System altered. --//重启略： 2.测试： --//session 1: SCOTT@test> select count(*) from dept; COUNT(*) ---------- 4 SCOTT@test> @ &r/tpt/hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 761178024 1jrz3ucqpx9x8 0 2d5ea7a8 --//session 2: SYS@test> @ &r/sharepool/shp4 1jrz3ucqpx9x8 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ------------------------- -------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 0000000077575EF8 0000000077576E28 select count(*) from dept 1 0 0 0000000077575E38 0000000077576778 3664 8088 1794 13546 13546 761178024 1jrz3ucqpx9x8 0 父游标句柄地址 0000000077576E28 0000000077576E28 select count(*) from dept 1 0 0 0000000077576D68 00 2803 0 0 2803 2803 761178024 1jrz3ucqpx9x8 65535 SYS@test> alter system flush shared_pool; System altered. SYS@test> @ &r/sharepool/shp4 1jrz3ucqpx9x8 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ------------------------- -------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 0000000077575EF8 0000000077576E28 select count(*) from dept 0 0 1 00 00 0 0 1794 1794 1794 761178024 1jrz3ucqpx9x8 0 父游标句柄地址 0000000077576E28 0000000077576E28 select count(*) from dept 1 0 1 0000000077576D68 00 2803 0 0 2803 2803 761178024 1jrz3ucqpx9x8 65535 --//一样清除子光标的堆0与堆6.看看在执行状态如何： SCOTT@test> select count(*) from dept; COUNT(*) ---------- 4 SYS@test> @ &r/sharepool/shp4 1jrz3ucqpx9x8 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ------------------------- -------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 0000000077575EF8 0000000077576E28 select count(*) from dept 1 2 1 0000000077575E38 0000000077576778 3664 8088 1794 13546 13546 761178024 1jrz3ucqpx9x8 0 父游标句柄地址 0000000077576E28 0000000077576E28 select count(*) from dept 1 0 1 0000000077576D68 00 2803 0 0 2803 2803 761178024 1jrz3ucqpx9x8 65535 SYS@test> alter system flush shared_pool; System altered. SYS@test> @ &r/sharepool/shp4 1jrz3ucqpx9x8 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ------------------------- -------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 0000000077575EF8 0000000077576E28 select count(*) from dept 0 2 2 0000000077575E38 0000000077576778 3664 8088 1794 13546 13546 761178024 1jrz3ucqpx9x8 0 父游标句柄地址 0000000077576E28 0000000077576E28 select count(*) from dept 1 0 2 0000000077576D68 00 2803 0 0 2803 2803 761178024 1jrz3ucqpx9x8 65535 --//在这样状态下，无论如何刷新共享池，子光标的堆0与堆6不会清除。