[20180819]关于父子游标问题(11g).txt

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

[20180819]关于父子游标问题(11g).txt --//sql语句存在父子游标,子游标堆6在父游标堆0里面. --//如果存在许多子游标的情况下,父游标堆0是否大小是发生变化呢.测试看看. --//另外11g引入参数_cursor_obsolete_threshold限制子光标的数量,测试它的一些控制机制. 1.环境: --//session 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> SELECT count(*) FROM dept WHERE deptno=10; COUNT(*) ---------- 1 --//确定sql_id=2xw4k6w7wc5ka. --//session 2: SYS@book> @ &r/hide _cursor_obsolete_threshold NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE -------------------------- ---------------------------------------------- ------------- ------------- ------------ _cursor_obsolete_threshold Number of cursors per parent before obsoletion TRUE 1024 1024 --//退出session 1,刷新共享池,清除该语句在共享池.这样才能清除干净. SYS@book> alter system flush shared_pool; System altered. 2.建立测试脚本: $ cat aa.sql DECLARE l_count PLS_INTEGER; BEGIN FOR i IN 1..&&2 LOOP EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||i; FOR j IN 1..&&1 LOOP EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||j; EXECUTE IMMEDIATE 'SELECT count(*) FROM dept WHERE deptno=10' into l_count; END LOOP; END LOOP; END; / --//执行如上脚本,能产生许多子光标.主要是因为环境变量发生了变化. --//session 1: SCOTT@book> @ aa.sql 1 64 PL/SQL procedure successfully completed. 3.查看父子游标情况: --//session 2: SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 父游标句柄地址 000000007CBC2C60 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CBC2BA8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 --//查看父游标堆0的chunk: select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007CBC2BA8') ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F8F6AF85568 1634 1 1 1 KGLH0^fc6164a 000000007DB3C420 4096 freeabl 0 000000007CBC2BA8 00007F8F6AF5A1F8 2515 1 1 1 KGLH0^fc6164a 000000007D879970 4096 freeabl 0 000000007CBC2BA8 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 00007F8F6AF5EA40 2676 1 1 1 KGLH0^fc6164a 000000007D7C2F20 4096 freeabl 0 000000007CBC2BA8 00007F8F6AF55250 3713 1 1 1 KGLH0^fc6164a 000000007D342488 4096 freeabl 0 000000007CBC2BA8 00007F8F6AF450F0 4407 1 1 1 KGLH0^fc6164a 000000007CE8C5F8 4096 freeabl 0 000000007CBC2BA8 00007F8F6AF47AC0 4471 1 1 1 KGLH0^fc6164a 000000007CE6D850 4096 freeabl 0 000000007CBC2BA8 00007F8F6AF30440 4856 1 1 1 KGLH0^fc6164a 000000007CC973C8 4096 freeabl 0 000000007CBC2BA8 00007F8F6AF33E80 4965 1 1 1 KGLH0^fc6164a 000000007CBD41F0 4096 freeabl 0 000000007CBC2BA8 00007F8F6B0A1400 5593 1 1 1 KGLH0^fc6164a 000000007C7F4F60 4096 freeabl 0 000000007CBC2BA8 00007F8F6B08CD28 6025 1 1 1 KGLH0^fc6164a 000000007C55FC10 4096 freeabl 0 000000007CBC2BA8 00007F8F6B0907B0 6226 1 1 1 KGLH0^fc6164a 000000007C442F48 4096 freeabl 0 000000007CBC2BA8 00007F8F6B07A300 6652 1 1 1 KGLH0^fc6164a 000000007C137798 4096 freeabl 0 000000007CBC2BA8 00007F8F6B070CD8 7591 1 1 1 KGLH0^fc6164a 000000007BC7D898 4096 freeabl 0 000000007CBC2BA8 00007F8F6B072158 7717 1 1 1 KGLH0^fc6164a 000000007BB93BC0 4096 freeabl 0 000000007CBC2BA8 00007F8F6B074238 7807 1 1 1 KGLH0^fc6164a 000000007BB49798 4096 freeabl 0 000000007CBC2BA8 00007F8F6B0754B8 7846 1 1 1 KGLH0^fc6164a 000000007BB19348 4096 freeabl 0 000000007CBC2BA8 00007F8F6B058C38 8653 1 1 1 KGLH0^fc6164a 000000007B62C700 4096 freeabl 0 000000007CBC2BA8 00007F8F6B05AFB8 8778 1 1 1 KGLH0^fc6164a 000000007B5A06E8 4096 freeabl 0 000000007CBC2BA8 00007F8F6B05C6B0 8804 1 1 1 KGLH0^fc6164a 000000007B588C38 4096 freeabl 0 000000007CBC2BA8 00007F8F6B05DDA8 8830 1 1 1 KGLH0^fc6164a 000000007B55FD78 4096 recr 4095 000000007CBC2BA8 00007F8F6B02A960 8930 1 1 1 KGLH0^fc6164a 000000007B4D8640 4096 freeabl 0 000000007CBC2BA8 00007F8F6B02A490 8944 1 1 1 KGLH0^fc6164a 000000007B4AAFF0 4096 freeabl 0 000000007CBC2BA8 22 rows selected. --//可以发现如果产生子光标很多,父游标堆0的chunk也会很多,不像1个子光标的情况下仅仅1个chunk. --//查看父游标堆0的描述符chunk: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007CBC2BA8', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F8F6AF337F8 4997 1 1 1 KGLDA 000000007CBC2B40 240 freeabl 0 00 SYS@book> @ &r/sharepool/shp4 2xw4k6w7wc5ka 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000000007CBC27E0 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 0 0 0 000000007CBC2728 000000007B5604E8 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 0 子游标句柄地址 000000007C6C4A90 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 0 0 0 000000007C6C49D8 000000007B560AB0 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 1 子游标句柄地址 000000007DA59628 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 0 0 0 000000007DA59570 000000007B4D89C0 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 2 子游标句柄地址 000000007D66E770 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 0 0 0 000000007D66E6B8 000000007B4D8E80 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 3 ...//太长子游标句柄地址 000000007B693320 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 1 0 0 000000007B693268 000000007D87A1B0 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 63 父游标句柄地址 000000007CBC2C60 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 1 0 0 000000007CBC2BA8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 65 rows selected. --//随便看看一个子光标堆6的描述符chunk:(KGLOBHD6=000000007D87A1B0) SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007D87A1B0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F8F6AF6DD40 2621 1 1 1 KGLH0^fc6164a 000000007D879970 4096 freeabl 0 000000007CBC2BA8 --//可以发现堆6的描述符chunk与前面的父游标堆0的chunk相同,注意看前面下划线内容.也就是子游标堆6的描述符chunk在父游标堆0的chunk中. 4.继续测试_cursor_obsolete_threshold限制子光标的数量. --//退出session 1,刷新共享池. --//session 2: SYS@book> alter system flush shared_pool; System altered. SCOTT@book> alter session set "_cursor_obsolete_threshold"=64; Session altered. --//缺省参数1024,有点大,减少到64,这样好测试一些. --//session 1: SCOTT@book> @ aa.sql 1 65 PL/SQL procedure successfully completed. --//session 2 SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 父游标句柄地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B442B50 00 4736 0 0 4736 4736 264640074 2xw4k6w7wc5ka 65535 --//产生2个父游标,注意2条记录的KGLHDLMD=1.表示还没有释放游标. --//注:我开始以为会出现多父多子的情况.实际上并不是,查看v$sql视图就很容易明白. SYS@book> select address,child_number,IS_OBSOLETE from v$sql where sql_id='2xw4k6w7wc5ka' and IS_OBSOLETE='N'; ADDRESS CHILD_NUMBER I ---------------- ------------ - 000000007D0716A0 0 N SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE; ADDRESS I COUNT(*) ---------------- - ---------- 000000007D0716A0 N 1 000000007BBF0758 Y 64 --//可以发现仅仅1个子光标是IS_OBSOLETE='N'.其它IS_OBSOLETE='Y',共有64个子光标,而且地址也不同(这个地址对应父游标的地址).继续测试: --//父游标地址000000007BBF0758下的子光标都是IS_OBSOLETE='Y'. --//session 1: SCOTT@book> @ aa.sql 1 65 PL/SQL procedure successfully completed. --//session 2: SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 父游标句柄地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B442B50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007B59E0F8 000000007B59E0F8 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B59E040 00 4736 0 0 4736 4736 264640074 2xw4k6w7wc5ka 65535 SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE; ADDRESS I COUNT(*) ---------------- - ---------- 000000007D0716A0 Y 64 000000007B59E0F8 N 2 000000007BBF0758 Y 64 --//产生2个父游标,注意后2条记录的KGLHDLMD=1. 而地址000000007B59E0F8对应的IS_OBSOLETE='N',其它都是IS_OBSOLETE='Y'. --//表示父游标句柄地址=000000007B59E0F8,当前有效(IS_OBSOLETE='N').继续测试: --//session 1: SCOTT@book> @ aa.sql 1 65 PL/SQL procedure successfully completed. --//session 2: SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 父游标句柄地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B442B50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007B59E0F8 000000007B59E0F8 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B59E040 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007CB65CB0 000000007CB65CB0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CB65BF8 00 8808 0 0 8808 8808 264640074 2xw4k6w7wc5ka 65535 SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE; ADDRESS I COUNT(*) ---------------- - ---------- 000000007D0716A0 Y 64 000000007BBF0758 Y 64 000000007B59E0F8 Y 64 000000007CB65CB0 N 3 --//大家自己看,不再说明. --//可以发现1个规律.如果当前父游标下存在64个子光标的情况下,再有子光标产生,该父游标下的子游标无效(IS_OBSOLETE='Y'),建立新的父游标. --//我前面调用的脚本@ aa.sql 1 65,每次都有1个子光标无法容纳,产生1个新的父游标,这样3次,这样新建立的父游标下就存在3个子光标. --//如果执行如下,就不会建立新的父游标. --//session 1: SCOTT@book> @ aa.sql 1 61 PL/SQL procedure successfully completed. --//session 2: SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0 old 21: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2 new 21: WHERE kglobt03 = '2xw4k6w7wc5ka' or kglhdpar='2xw4k6w7wc5ka' or kglhdadr='2xw4k6w7wc5ka' or KGLNAHSH= 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 父游标句柄地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B442B50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007B59E0F8 000000007B59E0F8 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B59E040 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007CB65CB0 000000007CB65CB0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CB65BF8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE; ADDRESS I COUNT(*) ---------------- - ---------- 000000007D0716A0 Y 64 000000007BBF0758 Y 64 000000007B59E0F8 Y 64 000000007CB65CB0 N 64 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//注意看KGLHDLMD=1那行,父游标句柄地址=000000007CB65CB0.与下划线看到的地址一致(IS_OBSOLETE='N').如果我继续执行 --//session 1: SCOTT@book> @ aa.sql 1 64 PL/SQL procedure successfully completed. --//session 2: SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 父游标句柄地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B442B50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007B59E0F8 000000007B59E0F8 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B59E040 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007CB65CB0 000000007CB65CB0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CB65BF8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007CE9EFB8 000000007CE9EFB8 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CE9EF00 00 8808 0 0 8808 8808 264640074 2xw4k6w7wc5ka 65535 SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE; ADDRESS I COUNT(*) ---------------- - ---------- 000000007CE9EFB8 N 3 000000007CB65CB0 Y 64 000000007D0716A0 Y 64 000000007BBF0758 Y 64 000000007B59E0F8 Y 64 --//奇怪竟然又生产新的父游标,下面有3个子光标.why? --//中午自己认真看一遍,突然明白为什么存在3个子光标,IS_OBSOLETE='N'. 5.一步一步来解析: --//执行@ aa.sql 1 65, 一个父游标仅仅有64个子游标,这样最后1个语句,生成新父游标,对应的optimizer_index_caching=65. --//执行@ aa.sql 1 65, 执行到optimizer_index_caching=64时,该父游标无法再加入子游标,生成新父游标,对应的optimizer_index_caching=64,65.存在2个子光标. --//执行@ aa.sql 1 65, 执行到optimizer_index_caching=63时,该父游标无法再加入子游标,生成新父游标,对应的optimizer_index_caching=63,64,65. --//执行@ aa.sql 1 61, 对于父游标正好有64个子游标.不会生成新的父游标.而对应子游标的optimizer_index_caching=63,64,65,1,2,....,61 --//执行@ aa.sql 1 64, optimizer_index_caching从1,2,..,61都能找到对应的子光标.而当执行optimizer_index_caching=62时,全部子游标不合适. --//而且该父游标下已经存在64个子游标,这样该父游标下全部子游标变成IS_OBSOLETE='Y'.生成新的父游标.对应的optimizer_index_caching=62,63,64.存在3个子光标. --//可以通过一个简单的测试证明自己的判断: --//退出sessioin 1,刷新共享池. --//session 2: SYS@book> alter system flush shared_pool; System altered. --//session 1,顺序执行如下: SCOTT@book> alter session set "_cursor_obsolete_threshold"=64; Session altered. @ aa.sql 1 65 @ aa.sql 1 65 @ aa.sql 1 65 @ aa.sql 1 61 --//建立测试脚本ab.sql: $ cat ab.sql DECLARE l_count PLS_INTEGER; BEGIN FOR i IN 62..64 LOOP EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||i; FOR j IN 1..&&1 LOOP EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||j; EXECUTE IMMEDIATE 'SELECT count(*) FROM dept WHERE deptno=10' into l_count; END LOOP; END LOOP; END; / --//注:仅仅调用执行optimizer_index_caching=62,63,64的情况. --//session 1 SCOTT@book> @ ab.sql 1 PL/SQL procedure successfully completed. --//session 2: SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 父游标句柄地址 000000007D3E0D18 000000007D3E0D18 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007D3E0C60 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007CD26CF0 000000007CD26CF0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007CD26C38 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007B4808C0 000000007B4808C0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B480808 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007D14F128 000000007D14F128 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007D14F070 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535 父游标句柄地址 000000007B9760C8 000000007B9760C8 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B976010 00 8808 0 0 8808 8808 264640074 2xw4k6w7wc5ka 65535 SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE; ADDRESS I COUNT(*) ---------------- - ---------- 000000007D3E0D18 Y 64 000000007CD26CF0 Y 64 000000007B9760C8 N 3 000000007D14F128 Y 64 000000007B4808C0 Y 64 --//还有3个子游标,IS_OBSOLETE='N'. --//测试有点乱,不过还是能基本说明问题.oracle各个版本_cursor_obsolete_threshold参数一直的不断调整. --//看来家里的windows系统12.1.0.1: SYS@test> @ 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 SYS@test> @ hide _cursor_obsolete_threshold NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE -------------------------- ----------------------------------------------- ------------- ------------- ------------ _cursor_obsolete_threshold Number of cursors per parent before obsoletion. FALSE 64 64 --//据说12.2.0.1版本修改为8192.当然重点定位为什么子光标太多,定位问题很关键. --//我个人感觉1024还是比较合理. 6.附上测试脚本: --//shp4.sql column N0_6_16 format 99999999 SELECT DECODE (kglhdadr, kglhdpar, '父游标句柄地址', '子游标句柄地址') text, kglhdadr, kglhdpar, substr(kglnaobj,1,40) c40, KGLHDLMD, KGLHDPMD, -- kglhdivc, 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; --//shp4z.sql column N0_6_16 format 99999999 select * from ( SELECT DECODE (kglhdadr, kglhdpar, '父游标句柄地址', '子游标句柄地址') text, kglhdadr, kglhdpar, substr(kglnaobj,1,40) c40, KGLHDLMD, KGLHDPMD, -- kglhdivc, 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 ) where kglhdadr=kglhdpar;

编辑推荐：

返回列表

[20180819]关于父子游标问题(11g).txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推