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

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

阅读量：6329 次

发布时间：2019-06-22

本文共 19941 字，大约阅读时间需要 66 分钟。

[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(*)

----------

--//确定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;

--//执行如上脚本,能产生许多子光标.主要是因为环境变量发生了变化.

--//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 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;

--//注:仅仅调用执行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;

转载于:https://www.cnblogs.com/lfree/p/9519903.html

你可能感兴趣的文章

进行短视频app开发工作时，可以加入它来保护青少年 ...

查看>>

25G DAC无源高速线缆和25G光模块之间的区别

查看>>

乐乐茶完成近2亿元Pre-A轮融资，祥峰投资领投

百度、长沙加码自动驾驶，湖南阿波罗智行科技公司成立 ...

查看>>

Java面试笔试题大汇总一（最全+详细答案）

查看>>

10 个 Linux 中方便的 Bash 别名

查看>>

[Server] 服务器配置SSH登录邮件通知

查看>>

全新 DOCKER PALS 计划上线，带给您不一样的参会体验！ ...

查看>>

Android开发之自定义View（二）

查看>>

python爬虫之微打赏（scrapy版）

查看>>

自制操作系统Antz day08——实现内核 (中) 扩展内核

查看>>

poj-1056-IMMEDIATE DECODABILITY（字典）

查看>>

阿里云容器Kubernetes监控(二) - 使用Grafana展现Pod监控数据

查看>>

区块链应用 | 不知道什么时候起，满世界都在谈区块链的事情

在非纯色背景上,叠加背景透明的BUTTON和STATIC_TEXT控件

查看>>